Shulou(Shulou.com)05/31 Report--

This article shows you the big data analysis of consistent non-locking reading and consistent locking reading. The content is concise and easy to understand, which will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Background

The innodb storage engine implements two standard row-level locks: the S lock and the X lock. The S lock is called a shared lock, which allows the transaction to read a row of data, and the X lock is called an exclusive lock, allowing the transaction to delete or update a row of data.

Consistent unlocked read means that if a record is locked by an X, other transactions can also read the record.

Consistent locking read means that a transaction can add an X lock or an X lock to a record through a SELECT statement.

A little chestnut.

Let's assume that there is a table and two transactions, the table name is mytest, and the transaction names are T1 and T2:

T1t2t3t4abbbbccc

The execution sequence of T1 and T2 is as follows:

Here, I would like to raise two questions:

At Mark An above, it is obvious that T1 has added an X lock to the record and modified the data within the transaction. what data does T2 see at this time?

What is the data seen by T2 when transaction T1 has been committed at Mark B above?

Row multi-version control

Row multi-version will be that innodb stores multiple versions for each row record, remember, here are multiple versions, not two versions, when I first came into contact with multiple versions, my question is how wasted storage space is for innodb to store multiple versions per row? However, to further understand, the so-called multi-version is only a clever lie told by innodb, and multiple versions are implemented through undo logs. It can be understood here that since the undo logs contain all the information used to recover historical version data, we only need to point the "different versions" pointer to the undo logs of different time nodes. When reading in this way, different versions of data can be obtained by restoring the undo logs of different time nodes. At the same time, the reading of undo logs does not need to be locked, so this greatly improves the concurrency of the database.

The above * questions are answered: what T2 should see at this time is the historical version of the data, that is, the data before T1 modification, as follows:

Mysql > select * from mytest where t2roombb' +-+ | T1 | T2 | T3 | T4 | +-+ | a | bb | bb | ccc | +-+ 1 row in set (0.00 sec) READ COMMITTED and REPEATABLE READ

Here's a review of the four isolation levels defined by the SQL standard:

READ UNCOMMITTED

READ COMMITTED

REPEATABLE READ

SERIALIZABLE

The default isolation level of innodb is REPEATABLE READ and uses next key locking technology to solve the problem of phantom reading. The value of READ COMMITTED is that a transaction can read data that has been committed by other transactions, while REPEATABLE READ requires a transaction to read a record repeatedly within the transaction. Therefore, the answer to the second question above is what T2 sees at this time is related to the isolation level of the database at this time. For example, the isolation level at this time is:

Mysql > select @ @ tx_isolation; +-+ | @ @ tx_isolation | +-+ | REPEATABLE-READ | +-+ 1 row in set (0.00 sec)

So what T2 sees in Mark B should be old data:

Mysql > select * from mytest where T2 row in set / bbsp; +-+ | T1 | T2 | T3 | T3 | +-+ | a | bb | bb | ccc | +-+ 1 row in set (0.00 sec) mysql >

If the transaction isolation level at this time is READ COMMITTED, T2 should see the new data at Mark B.

Consistent lock read

In the case of consistent non-locked read, even if the record is X-locked because of UPDATE, other transactions can still read the record without blocking. What if a transaction wants to lock the record when it is read and does not allow other transactions to modify it? That's SELECT... FOR UPDATE,SELECT... FOR UPDATE explicitly puts an X lock on a record, so other transactions cannot acquire any locks for that record. We can also use SELECT... LOCK IN SHARE MODE is used to explicitly add an S lock to a record, so that other transactions can acquire the S lock of the record but not the X lock of the record. Both statements have specific application scenarios.

To sum up, consistent non-lock reading is about a record with an X lock that other transactions can still read without blocking, which is achieved through row multiple versions of innodb, which do not actually store multiple version records but through undo. Consistency locking reads say that I can explicitly add an X lock to a record through the SELECT statement to ensure data consistency in a particular application scenario.

The above content is the big data analysis of consistent unlocked reading and consistent locked reading. Have you learned the knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.