MySQL Multi-version concurrency control

MVCC (multiple version Concurrent control) Multiple version concurrency control does not have a unified implementation standard. MVCC can be thought of as a variant of a row-level lock, but in many cases it avoids lock-up operations and is less expensive. Although the implementation mechanism is different, most non-blocking read operations are implemented, and write operations only lock the necessary rows. MVCC is achieved by saving the data at a point in time snapshot. In other words, no matter how long it takes to execute, the data that each transaction sees is consistent. Depending on the time at which the transaction started, each transaction might see different data on the same table at the same time.

The InnoDB MVCC is implemented by saving two hidden columns after each row of records. These two columns, one saving the creation time of a row, the expiration time (or deletion time) of a saved row. However, it is not the actual time value that is stored, but the system version number. Each start of a new transaction, the system version number is automatically incremented. The system version number of the transaction start time as the version number of the transaction, compared to the version number of each row of records queried.

　　

The storage engine is InnoDB, under the default isolation level (Repeatable read), MVCC specific actions:

1 Select

Check each row of records according to the following two criteria:

A finds only data rows that are earlier than the current version of the transaction (the system version number of the row is less than or equal to the system version number of the transaction), ensuring that the transaction reads the row, either before the transaction begins, or the transaction itself is inserted or modified.

The deleted version of row B is either undefined or larger than the current transaction version number, ensuring that the rows read to by the transaction are not deleted before the transaction begins.

Only records that meet the above two criteria can be returned as query results.

2 Insert

Save the current system version number as the line version number for each row you insert.

3 Delete

Saves the current system version number for each row deleted as a row delete identity.

4 Update

Insert a new row of records, save the current system version number as the line version number, save the current system version number to the original line as the row to delete the identity.

Save these two additional system version numbers so that most read operations can be unlocked, but each line of records requires additional storage space, more row checking, and some additional maintenance work.

MVCC only works under two isolation levels: repeatable Read and Read Committed. The other two isolation levels are incompatible with MVCC. Uncommitted reads always read the most recent data row, not the data row of the current transaction version. Serializable will lock all rows that are read.

Resources

"High performance MySQL" p12-13

MySQL Multi-version concurrency control