MySQL MVCC (multiple version concurrency control)

Overview

In order to improve concurrent MySQL to join the multi-version concurrency control, it saves the old version record in the shared table space, the transaction is not committed before the corresponding row record or lock restrictions, when the transaction committed after the corresponding record row in the cache is modified records are also persisted, When the refresh thread refreshes on a regular basis, the row's modification record is flushed to the physical data page, and the old version record page in the shared tablespace is cleared.

Body

Multiple version concurrency control is only available for InnoDB Repeatable Read and Read Committed isolation levels. The principle of multi-version concurrency control is to add two labeled columns to store the state of the row after each record line, storing the new system version number of the line and deleting the system version number, respectively.

Version number: The system's version number is incremented with each additional transaction.

Read high-performance MySQL This book explains that MVCC is a variant of row-level locks, but it avoids lock-up operations in many cases, but mostly does non-blocking read operations, and write operations lock only the 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)

These four transactions now operate on the same line, demonstrating the repeatable read of SELECT, assuming that four operations were not committed before Slect was committed, and of course select was able to see records of four operations.

SELECT:

Selet will find the records according to the following two criteria:

1. Find only the record lines that are less than or equal to the current transaction version number.

2. Delete the record line with a version number that is empty or larger than the current transaction version number.

BEGIN TRAN SELECT  from TABLE WHERE ID=1

Id	NAME	trx_id	de_trx_id
1	A	1

Ten MINUTE SELECT  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 is greater than the current version number according to the search criteria for the Select, so it will not be detected; The transaction deletion version number of the delete is greater than the current version number, so the row is detected; the version number of the transaction in the first line of the update is less than the current version number and the version number is greater Can be found, the second line is not satisfied because the transaction version number is greater than the current version number, and the record of the final select query is the first record of the update operation, and the results of the Select two query are consistent to meet the repeatable read isolation level. Of course, in addition to the insert operation, none of the other operations can be committed before the select commit.

INSERT:

INSERT  into TABLE 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 TRAN UPDATE TABLE SET 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 in the cache to maintain the lock, the maintenance of the lock needs to consume the resources of MySQL, multi-version concurrency control it is to reduce the consumption to maintain the lock resource to improve performance.

MySQL MVCC (multiple version concurrency control)