MySQL MVCC (Multi-version concurrency control) and mysqlmvcc

MySQL MVCC (Multi-version concurrency control) and mysqlmvcc
Overview

To improve the concurrency of MySQL, a multi-version concurrency control is added. It stores the old record in the shared tablespace, and the corresponding row records are still subject to the lock before the transaction is committed, after the transaction is committed, the corresponding record row is modified in the cache and the record is also persistent, when the refresh thread refreshes according to certain rules, the row modification record is refreshed to the physical data page, and the old record pages in the shared tablespace are also cleared.

 

Body

Multi-version concurrency control only applies to the repeatable read and read committed isolation levels of innodb. The principle of multi-version concurrency control is to add two Marking columns after each record row to store the status of the row, respectively storing the new system version number and the deleted system version number of the row.

Version Number: The system version number increases with each additional transaction.

Reading the high-performance mysql book explains that MVCC is a variant of Row-level locks, but it avoids locking operations in many cases, but most of them implement non-blocking read operations, write operations only lock necessary rows.

 

TABLE

 

ID	NAME	Trx_id	De_Trx_id
1	A	1

There are four transactions: select (trx = 2), insert (trx = 3), delete (trx = 4), update (trx = 5)

Now these four transactions operate on the row at the same time to demonstrate the Repeatable read of the SELECT statement. If the four operations are not committed before the SLECT commit, the SELECT statement can check the records of the four operations.

SELECT:

SELET searches for records based on the following two conditions:

1. Only query record rows that are less than or equal to the current transaction version number.

2. Delete the record row whose version number is null or later than the current transaction version number.

BEGIN TRANSELECT NAME FROM TABLE WHERE ID=1

 

ID	NAME	Trx_id	De_Trx_id
1	A	1

 

WAIT 10 MINUTESELECT NAME FROM TABLE WHERE ID=1

COMMIT

 

ID	NAME	Trx_id	De_Trx_id
1	A	1	4

The transaction version number of the INSERT Record Based on the SELECT search condition is later than the current version number, so it is not found; the DELETE transaction version number of the DELETE transaction is later than the current version number, so this row is found; the transaction version number in the first row of UPDATE is smaller than the current version number, and the deleted version number is later than the current version number. This can be found. The second row is not satisfied because the transaction version number is greater than the current version number, the record of the final SELECT query is the first record of the UPDATE operation. The results of the SELECT two queries are consistent, meeting the Repeatable read isolation level. Of course, except the INSERT operation, other operations cannot be submitted before the SELECT statement is submitted.

 

 

INSERT:

INSERT INTO TABLE(ID,NAME) VALUES(2,'B');

 

ID	NAME	Trx_id	De_Trx_id
2	B	2

 

DELETE:

DELETE FROM TABLE WHERE ID=1;

 

ID	NAME	Trx_id	De_Trx_id
1	A	1	3

 

 

UPDATE:

BEGIN TRANUPDATE TABLESET NAME='B'WHERE ID=1COMMIT

 

ID	NAME	Trx_id	De_Trx_id
1	A	1	4

 

ID	NAME	Trx_id	De_Trx_id
1	B	4

 

 

Note: Multi-version concurrency control does not support the myisam storage engine.

Summary

There is a resource used to maintain locks in the cache. The maintenance of locks consumes mysql resources. Multi-version concurrency control reduces the consumption of lock resources to improve performance.