Talking about MySQL MVCC

The following is personal understanding, if there are errors, but also to correct!!

Most of MySQL's transactional storage engine is not a simple row-level lock, based on the promotion of concurrency performance considerations, they generally implement multi-version concurrency control at the same time, can be considered as a variant of row-level lock, but it MVCC in many cases to avoid lock operation, so the cost is lower, Although the implementation mechanism is different, most non-blocking read operations are implemented, and write operations only lock the necessary rows.

The implementation of MVCC is achieved by saving a snapshot of the data at a point in time, that is, regardless of how long it takes to execute, as long as the transaction start time is the same, each transaction sees the same data, the transaction begins at a different time, each transaction is on the same table, The data that you see at the same time may be different (because different points of time may have produced a distinct snapshot version, and each transaction can only see a snapshot of the data at the beginning of the transaction at the default RR isolation level). The MVCC implementations of different storage engines are different, typically with optimistic concurrency control and pessimistic concurrency control, and here's a simple explanation of how MVCC works:

The MVCC of InnoDB is achieved by saving two hidden columns after each row of records, with two columns, one saving the creation time of the row, one saving the row's expiration time (or deletion time), and, of course, storing the actual time value, but the system version number Number), each time a new transaction starts, the system version numbers are automatically incremented, and the system version number at the start of the transaction is used as the version number of the transaction, compared to the version number of each row of records queried, below the repeatable read isolation level, the MVCC is specifically how to operate:

Select

InnoDB will check each row's records according to the following two conditions

A:innodb only finds rows of data that are earlier than the current version of the transaction (that is, the system version number of the line is less than or equal to the system version number of the transaction), which ensures that the transaction reads the row, either before the transaction begins, or data that the transaction itself inserts or modifies.

B: The deleted version of the row is either undefined or larger than the current transaction version number, which ensures that the transaction is read to the row that was not deleted before the transaction begins (that is, the purpose is to do so so that the transaction does not read to the row that was actually deleted. Delete the representation that the version number is less than the current transaction version number the transaction that deleted the record has been committed--the data has been deleted, the deletion version number is greater than the current transaction version number indicates that the transaction started after the current transaction-the records are still present at the beginning of the current transaction, based on the isolation of the transaction, the consistency requirement, After the start of the transaction operation of the record and commit, the current transaction is not visible, so the current transaction is also required to query these records-only can query, not be able to modify and delete.

Only the above two conditions can be returned as the result of a query.

Insert

InnoDB Save the current system version number as the row version number for each newly inserted row

Delete

InnoDB saves the current system version number for each row deleted as a row delete identity

Update

InnoDB to insert a new row of records, save the current system version number as the line version number, and save the current system version number to the original row to delete the identity as a row

Saving these two additional system version numbers allows most read operations to be unlocked, so that the design makes the read data operation Simple, performs well, and ensures that only the standard rows are read, with the disadvantage of requiring additional storage space per row of records, more row checking, and some additional maintenance work.

The MVCC only works under the Repeatable-read and read-committed two isolation levels, and the other two isolation levels are incompatible with MVCC because READ uncommitted always reads the most recent data rows, not the data rows that match the current transaction version. The serializeble will lock all rows that are read.

Also note that the difference between the RR and RC isolation levels is that, under RR isolation, a transaction can only read a snapshot of the data at the point at which the transaction began, that is, the data that was modified and committed by other transactions is generally not read until it is MVCC (except for the Select FOR UPDATE statement). Because this statement must read the most recent data snapshot to the data plus the X lock, the transaction always reads the most recent snapshot of the data row at the RC isolation level, resulting in a non-repeatable read problem.

Talking about MySQL MVCC