Consistent Nonlocking read refers to the way InnoDB storage engine reads rows in the current database through multi-version control (MVVC). If the read row is performing a DELETE or UPDATE operation, the read operation does not therefore wait for the row lock to be released. Instead, InnoDB reads a snapshot of the row.

Consistent non-locked read schematic

The figure above visually illustrates InnoDB’s consistent unlocked read mechanism. This is called an unlocked read because there is no need to wait for another lock on the row to be released. Snapshot data refers to the data of the previous version of the row. Each row may have multiple versions. This technology is commonly called row multi-version technology.

The resulting Concurrency Control is called 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 overhead. In addition, snapshot data does not need to be locked because there are no transactions that need to modify historical data.

Consistent unlocked reads are InnoDB’s default read mode, i.e. reads do not occupy and wait for locks on rows. However, this is not the case at every transaction isolation level. In addition, even though consistent non-locked reads are used, the definition of snapshot data varies.

InnoDB uses consistent unlocked reads at READ COMMITTED and REPEATABLE READ transaction isolation levels. However, snapshot data is defined differently. At the READ COMMITTED transaction isolation level, consistent unlocked reads always READ the latest snapshot data of the locked row. With REPEATABLE READ transaction isolation level, the version of the row data at the start of the transaction is READ.

Let’s take an example to illustrate the above situation in detail.

# session Amysql> BEGIN; mysql> SELECT * FROM test WHERE id = 1;Copy the code

We first explicitly start A transaction in session A and then read data with ID 1 in the test table, but the transaction does not end. At the same time, the user starts another session B, which simulates concurrent operations, and then performs the following operations on session B:

# session Bmysql> BEGIN; mysql> UPDATE test SET id = 3 WHERE id = 1;Copy the code

SQL > alter table test (id=3); SQL > alter table test (id=3); Since InnoDB uses consistent non-locking reads at READ COMMITTED and REPEATABLE READ transaction isolation levels, if session A reads the record with ID 1 again, the same data can still be READ. At this point, there is no difference between READ COMMITTED and REPEATABLE READ transaction isolation levels.

Diagram of session A and session B

As shown in the figure above, when session B commits the transaction and session A runs the SQL statement SELECT * FROM test WHERE ID = 1 again, the results obtained at the two transaction isolation levels are different. For the READ COMMITTED transaction isolation level, it always reads the latest version of a row or, if a row is locked, the last snapshot of that row’s version. Because the transaction for session B has already committed, the result set of the above SQL statement is empty at this isolation level. For the transaction isolation level of REPEATABLE READ, the row data at the start of the transaction is always READ, so the above SQL statement still gets the same data at that isolation level.

MVVC

Let’s start by looking at the wiki definition of MVVC:

Multiversion concurrency control (MCC or MVCC), is a concurrency controlmethod commonly used by database management systems to provideconcurrent access to the database and in programming languages toimplement transactional memory.

By definition, MVVC is a concurrency control technology used to provide concurrent access control for databases. Database concurrency control mechanism has many, the most common is the lock mechanism. The locking mechanism usually locks the competing resources and blocks the read or write operations to solve the competition conditions between transactions and finally ensure the serializability of transactions.

MVVC introduces another kind of concurrency control, which makes the read and write operations do not block. Each write operation creates a new version of data, and the read operation directly returns the most suitable result from the finite number of versions of data, thus solving the transaction competition condition.

Consider a realistic scenario. The administrator queries the total deposits of all users. Assume that the total deposits of all users except users A and B are 0, and users A and B each have deposits of 1000. Therefore, the total deposits of all users are 2000. However, during the query, user A transfers money to user B. The sequence diagram of transfer operation and query total amount operation is shown below.

Time sequence diagrams for transfers and queries

If there is no concurrency control mechanism, query the total transaction reads the user A account deposit first, and then transfer transactions changed the user A and user B account deposit, the final total query transactions continue to read the transfer after the user B account deposits, lead to the final statistics, more than 100 yuan in deposits, an error occurs.

Using the locking mechanism can solve the above problems. The query total transaction locks the rows read and releases the locks on all rows after the operation is complete. Because user A’s deposit is locked, the transfer operation is blocked until the query total transaction commits and all locks are released.

Use locking mechanism
Use the MVVC mechanism

MVCC enables the database to read data without locking, ordinary SELECT requests without locking, improving the concurrent processing capacity of the database. With THE help of MVCC, the database can implement isolation levels such as READ COMMITTED, REPEATABLE READ. Users can view the previous or previous versions of the current data.

InnoDB MVVC implementation

Multi-version concurrency control is only a technical concept, and there is no unified implementation standard. Its core concept is data snapshot. Different transactions access different versions of data snapshot, so as to achieve different transaction isolation levels. Although this literally means having multiple versions of the data snapshot, it does not mean that the database must copy the data and hold multiple data files, which can waste a lot of storage space. InnoDB cleverly implements multiple versions of data snapshots through transaction undo logging. Sometimes database transactions need to be rolled back, so you need to undo the previous operation. Therefore, InnoDB generates undo logs when data is modified. When a transaction needs to be rolled back, InnoDB can use these undo logs to roll back data to where it was before it was modified. There are two types of undo logs: insert undo log and update undo log. Insert undo log is the undo log generated during 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 purge operations. Update undo log is an undo log generated during an update or delete operation. It will affect existing records. To provide MVCC mechanism, update undo log cannot be deleted when a transaction commits. Instead, the transaction commits to the history list and waits for the Purge thread to perform its final deletion. InnoDB uses rollback segments to maintain concurrent writes and persistence of undo logs in order to ensure that there is no conflict when writing undo logs in concurrent transactions. A rollback segment is actually an Undo file organization. The InnoDB row record has three hidden fields: roWID for the appropriate row, transaction number DB_trx_id, and rollback pointer DB_roll_ptr, where DB_trx_id represents the ID of the most recently modified transaction and DB_roll_ptr points to undo log in the rollback segment. As shown in the figure below.

The initial state

When transaction 2 modifies the row data using an UPDATE statement, it first locks the changed row with an exclusive lock, copies the current value of the row to the Undo log, then actually modifies the value of the current row, finally fills in the transaction ID, and uses the rollback pointer to point to the previous row in the Undo log. As shown in the figure below.

First Modification

When transaction 3 is modified, the process is similar to that of transaction 2, as shown below.

Second Modification

REPEATABLE READ When the MVVC mechanism is used to READ after the transaction starts at the isolation level, the active transaction ID will be recorded and recorded in the READ View. At the READ COMMITTED isolation level, a new READ View is created each time a READ is COMMITTED. Read View is a data structure used to judge the visibility of records in InnoDB. It records properties used to judge the visibility of records.

  • Low_limit_id: Specifies the value db_trx_id < for a row that is visible to the current Read View

  • Up_limit_id: Db_trx_id >= the value for a row that must not be visible to the current read View

  • Low_limit_no: Used to determine the purge operation

  • Rw_trx_ids: array of read and write transactions

After the Read View is created, the transaction compares the log’s db_trx_ID with low_limit_ID, up_limit_ID, and Read/write transaction arrays in the Read View to determine visibility.

If db_trx_id in this row is equal to the current transaction ID, the change occurred within the transaction. Otherwise, if the value db_trx_id is less than up_limit_id, the value was changed before the transaction started, and the record is visible to the current Read View.

If db_trx_id is greater than or equal to low_limit_id, the record must be invisible to this Read View. If db_trx_id is in the range [up_limit_id, low_limit_id), you need to check whether db_trx_id exists in the array of active Read and write transactions (rw_trx_ids). If so, the record is not visible to the current Read View. If the record is not visible to the Read View, the record’s DB_ROLL_PTR pointer is used to iterate through the undo log to construct the version data visible to the current Read View. In simple terms, the Read View records all active transactions at and after the start of a Read, and the changes made by those transactions are not visible to the Read View. In addition, all other records that are smaller than the transaction number that created the Read View are visible.

Afterword.

We will continue to learn about InnoDB locks, please continue to pay attention.

  • Mysql > select * from b-tree

  • Explore the internal storage structure of database

  • SQL statement execution process in detail

  • InnoDB memory structure and features

  • InnoDB disk files and drop disk mechanism

  • InnoDB lock type and state query

Refer to the article

  • http://mysql.taobao.org/monthly/2018/03/01/

  • https://liuzhengyang.github.io/2017/04/18/innodb-mvcc/

  • http://hedengcheng.com/?p=148

  • Tang Cheng -2016PG Conference – Inside of Multi-version Database Implementation.pdf