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 submission method (whether to submit automatically, MySQL automatically submits by default)

SET AUTOCOMMIT = 1; //Automatically commit, 0 manually commit

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

innodb

illustrate:

InnoDB's MVCC is implemented by saving two hidden columns after each row of records. These two columns, one stores the creation time of the row, and the other stores the expiration time (or deletion time) of the row. Of course, what is stored is not the actual time value, but the system version number. Every time a new transaction is started, the system version number will be automatically incremented. The system version number at the start of the transaction will be used as the transaction version number to compare with the queried

SELECT

InnoDB checks each row against the following two conditions:

a. InnoDB only searches for data rows whose versions are 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). This 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 greater than the current transaction version number. This ensures that rows read by a transaction were not deleted before the transaction began.

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 a row deletion identifier for each deleted row.

UPDATE

InnoDB saves the current system version number as the row version number when inserting a new row, and saves the current system version number to the original row as a row deletion marker.

Notice:

MVCC only works at REPEATABLE READ and READ COMMITED isolation levels. 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 will lock all read rows.

Check the status of the table

show table status like 'task'\G;

Dirty reads, non-repeatable reads, and phantom reads

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

Non-repeatable read: Transaction isolation is achieved, but data inconsistency is found when the same data is read twice.

Phantom read: When querying the same batch of data twice, new data is found to have been inserted. This is mainly because other transactions inserted data into the data set in the middle. (Adding a gap lock solves this problem)

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.