Shulou(Shulou.com)06/02 Report--

In this issue, the editor will bring you about what the consistent unlocked reading of InnoDB in MySQL is like. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

  consistent unlocked read (consistent nonlocking read) refers to the way in which the InnoDB storage engine reads row data in the current database through multiple version control (MVVC). If the read row is performing a DELETE or UPDATE operation, the read operation does not wait for the row lock to be released. Instead, InnoDB reads a snapshot of the row.

Consistent non-locking reading diagram

The figure above   visually shows the mechanism of InnoDB consistent unlocked reads. It is called an unlocked read because there is no need to wait for the release of the exclusive lock on the row. Snapshot data refers to the data of previous versions of the row, and there may be multiple versions per row, which is generally referred to as row multi-version technology.

The resulting concurrency control is called multi-version concurrency control (Multi Version Concurrency Control, MVVC). InnoDB implements MVVC through undo log. Undo log itself is used to roll back data in a transaction, so the snapshot data itself has no additional overhead. In addition, reading snapshot data does not need to be locked, because there are no transactions that need to modify historical data.

  consistent unlocked reads are the default way for InnoDB to read, that is, reads do not occupy and wait for locks on rows. However, this approach is not adopted at every transaction isolation level. In addition, even if consistent unlocked reads are used, the definition of snapshot data varies.

  under transaction isolation levels READ COMMITTED and REPEATABLE READ, InnoDB uses consistent unlocked reads. However, the definition of snapshot data is different. At the READ COMMITTED transaction isolation level, consistent unlocked reads always read the latest snapshot data of the locked row. At the REPEATABLE READ transaction isolation level, the row data version at the beginning of the transaction is read.

 , let's give an example to illustrate the above situation in detail.

# session A

Mysql > BEGIN

Mysql > SELECT * FROM test WHERE id = 1

  We first open a transaction explicitly in session A, and then read the data in the test table with an id of 1, but the transaction does not end. At the same time, the user is opening another session B, which can simulate concurrent operations, and then do the following to session B:

# session B

Mysql > BEGIN

Mysql > UPDATE test SET id = 3 WHERE id = 1

In the transaction of session B,   changes the record with id 1 in the test table to id=3, but the transaction is also not committed, so that the row of id=1 is actually added an exclusive lock. Because InnoDB uses consistent unlocked reads at the READ COMMITTED and REPEATABLE READ transaction isolation levels, session A can still read the same data if session A reads the record with id 1 again. At this point, there is no difference between the READ COMMITTED and REPEATABLE READ transaction isolation levels.

Schematic diagram of conversation An and conversation B

  as shown in the figure above, when session A runs the SQL statement of SELECT * FROM test WHERE id = 1 again after session B commits the transaction, the results under the two transaction isolation levels are different.

For the transaction isolation level of READ COMMITTED,   always reads the latest version of the row and, if the row is locked, the latest snapshot of that version. Because the transaction for session B has been committed, the result set of the above SQL statement is empty at this isolation level.

For the transaction isolation level of REPEATABLE READ,   always reads the row data at the beginning of the transaction, so the above SQL statement still gets the same data at that isolation level.

MVVC

  Let's first take a look at the definition of MVVC on wiki:

Multiversion concurrency control (MCC or MVCC), is a concurrency control

Method commonly used by database management systems to provide

Concurrent access to the database and in programming languages to

Implement transactional memory.

According to the definition of  , MVVC is a concurrency control technology used for database to provide concurrency access control.

There are many concurrency control mechanisms in database, and the most common one is lock mechanism. The locking mechanism generally locks competitive resources, blocks read or write operations to solve the competition conditions between transactions, and ultimately ensures that transactions can be serialized.

On the other hand, MVVC introduces another kind of concurrency control, which makes the read and write operations do not block each other, each write operation creates a new version of the data, and the read operation selects the most appropriate result from a limited number of versions of the data and returns directly, thus solving the transaction competition condition.

  considers a real-world scenario. The manager wants to inquire about the total deposits of all users, assuming that with the exception of user An and user B, the total deposits of all users are 1000, so the total deposits of all users are 2000. However, during the query process, user A will transfer money to user B. The timing diagram of the transfer operation and the query total operation is shown in the following figure.

Time sequence diagram of transfer and query

If   does not have any concurrency control mechanism, the query total transaction first reads the account deposit of user A, then the transfer transaction changes the account deposit of user An and user B, and finally the query total transaction continues to read the account deposit of user B after the transfer, resulting in an error.

  can solve the above problems by using the locking mechanism. The query total transaction locks the read rows and waits until the end of the operation to release locks on all rows. Because user A's deposit is locked, the transfer operation is blocked until the query total transaction is committed and all locks are released.

Use the locking mechanism

 , however, may introduce a new problem at this time, which can lead to a deadlock when the transfer operation is from user B to user A. The transfer transaction will first lock user B's data and wait for the lock on user A's data, but the transaction of querying the total amount will first lock user A's data and wait for the lock on user B's data. This problem can also be solved by using the MVVC mechanism. The query total transaction first reads the account deposit of user A, and then the transfer transaction modifies the account deposit of user An and user B. when the query total transaction reads the deposit of user B, it does not read the modified data of the transfer transaction, but reads the copy of the data at the beginning of the transaction (under the REPEATABLE READ isolation level).

Using the MVVC mechanism

  MVCC makes the database read without locking the data and ordinary SELECT requests without locking, which improves the concurrent processing ability of the database. With MVCC, the database can achieve isolation levels such as READ COMMITTED,REPEATABLE READ, and users can view the previous or previous historical versions of the current data, ensuring the I feature (isolation) in ACID.

MVVC implementation of InnoDB

  multi-version concurrency control is only a technical concept, and there is no unified implementation standard, its core concept is data snapshots, different transactions access different versions of data snapshots, so as to achieve different transaction isolation levels. Although it literally means that there are multiple versions of data snapshots, this does not mean that the database must copy the data and save multiple data files, which will waste a lot of storage space. InnoDB cleverly implements multiple versions of data snapshots through the undo logs of transactions.

Transactions in the   database sometimes require a rollback operation, which requires undo of the previous operation. Therefore, InnoDB generates undo log when making changes to the data. When a transaction needs to be rolled back, InnoDB can use these undo log to roll back data to what it was before modification.

  is divided into two types of undo log: insert undo log and update undo log according to different behaviors.

The   insert undo log is the undo log generated in the insert operation. Because the record of the insert operation is visible only to the transaction itself and not to other transactions, the insert undo log can be deleted directly after the transaction commits without the need for a purge operation.

  update undo log is a undo log generated in a update or delete operation, because it affects existing records. In order to provide a MVCC mechanism, update undo log cannot delete a transaction when it commits, but puts it on the history list when the transaction commits, waiting for the purge thread to delete the transaction.

In order to ensure that there is no conflict when writing their respective undo log during the concurrent operation of transactions,   uses a rollback segment to maintain the concurrent writing and persistence of undo log. The rollback segment is actually a way of organizing Undo files.

The   InnoDB row record has three hidden fields: the rowid for the row, the transaction number db_trx_id, and the rollback pointer db_roll_ptr, where db_trx_id indicates that the id,db_roll_ptr of the recently modified transaction points to the undo log in the rollback segment. This is shown in the following figure.

Initial state

  when transaction 2 uses an update statement to modify the row data, it will first use an exclusive lock to change the row, copy the current value of the row to undo log, then actually modify the value of the current row, finally fill in the transaction ID, and use the rollback pointer to point to the row in undo log before modification. This is shown in the following figure.

First revision

  the modification of transaction 3 is similar to the process of transaction 2, as shown in the following figure.

The second revision

When the MVVC mechanism is used for reading after the transaction starts under the   REPEATABLE READ isolation level, the active transaction id is recorded and recorded in Read View. Under the READ COMMITTED isolation level, a new Read View is created each time it is read.

  Read View is a data structure used to judge the visibility of records in InnoDB, and some attributes are recorded to determine the visibility.

Low_limit_id: the db_trx_id of a row record

= this value, the row must not be visible to the current read view

Low_limit_no: judgment for purge operations

Rw_trx_ids: read-write transaction array

After the   Read View is created, the visibility is determined by comparing the low_limit_id,up_limit_id and read-write transaction arrays in the recorded db_trx_id and Read View when the transaction reads again.

  if the db_trx_id in the row is equal to the current transaction id, it is a change within the transaction and the data in the row is returned directly. Otherwise, if db_trx_id is less than up_limit_id, indicating a modification before the start of the transaction, the record is visible to the current Read View and returns the row data directly.

  if db_trx_id is greater than or equal to low_limit_id, the record must not be visible to that Read View. If the db_trx_id is in the [up_limit_id, low_limit_id) range, you need to find out whether the db_trx_id exists in the active read-write transaction array (rw_trx_ids), and if so, the record is not visible to the current Read View.

  if the record is not visible to the Read View, you need to traverse the undo log through the recorded DB_ROLL_PTR pointer to construct the data visible to the current Read View version.

  simply says that Read View records all active transactions at and after the start of the read, and the changes made by these transactions are not visible to Read View. In addition, all other records that are smaller than the transaction number that created the Read View are visible.

This is what the consistent unlocked reading of InnoDB in MySQL is like. If you happen to have similar doubts, please refer to the above analysis to understand. If you want to know more about it, 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.