Implementation of mysql Multi Version Concurrency Control MVCC

Transaction isolation level settings

set global transaction isolation level read committed; // Global 
set session transaction isolation level read committed; // Current session 

Modify the transaction commit method (whether to commit automatically, mysql commits automatically by default)

SET AUTOCOMMIT = 1; // Automatically submit for 0 Manual submission 

Different database engines have different MVCC schemas, typically optimistic and pessimistic concurrency control.

innodb

Description:

MVCC of InnoDB is achieved by saving two hidden columns after each row of records. Of these two columns, one holds the creation time of the row and one holds the expiration time (or deletion time) of the row. Of course, it is not the actual time value that is stored, but the system version number (system version number), which is automatically incremented every time a new transaction is started. The system version number at the start of the transaction will be used as the version number of the transaction, which is used to match the queried

SELECT

InnoDB checks each row against two criteria:

a. InnoDB looks only for rows of data whose version is earlier than the current transaction version (that is, the system version number of the row is less than or equal to the system version number of the transaction), which ensures that the rows read by the transaction either existed before the transaction started, or were inserted or modified by the transaction itself.

b. The deleted version of the row is either undefined or larger than the current transaction version number. This ensures that rows read by the transaction are not deleted before the transaction starts.

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

INSERT

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

DELETE

InnoDB saves the current system version number as the row deletion identification for each row deleted.

UPDATE

InnoDB is to insert a new line record, save the current system version number as the line version number, and save the current system version number to the original line as the line deletion identification.

Note:

MVCC works only at two isolation levels, REPEATABLE READ and READ COMMITED. The other two isolation levels are incompatible with MVCC, because READ UNCOMITTED always reads the latest data row, not the data row that conforms to the current transaction version. SERIALIZABLE locks all read rows.

View the status of the current table

show table status like 'task'\G;

Dirty reading, unrepeatable reading, illusion reading

Dirty read: The current transaction reads the uncommitted state of another transaction, and the transaction is not isolated.

Non-repeatable read: The isolation of transactions is realized, but when reading the same data twice, it is found that the data is not 1.

Phantom reading: Query the same batch of data twice, and find that new data is inserted, mainly because other transactions have inserted the data set in the middle. (Gap lock is added to solve this problem)