MySQL MVCC (multi-version concurrency control)

MySQL MVCC (multi-version concurrency control)

We know that MySQL's InnoDB uses row locks, and multiple versions of concurrency control are used to improve the performance of read operations.

What is multi-version concurrency control? In fact, add two hidden columns after each row record, record the creation version number and delete the version number,

Each transaction has a unique incremented version number when it is started.

1. When inserting an operation: the creation version number of the record is the transaction version number.

For example, if I insert a record and the transaction ID is 1, the record is as follows: that is, the build number is the transaction version number.

Id	Name	Create version	Delete version
1	Test	1

2, in the update operation, the use is to mark the old row record as deleted, and delete the version number is the transaction version number, and then insert a new row of records.

For example, for the above row record, the transaction ID is 2 to update the name field

Update table set name= ' New_value ' where id=1;

Id	Name	Create version	Delete version
1	Test	1	2
1	New_value	2

3, delete the operation, the transaction version number as the deletion version number. Like what

Delete from table where id=1;

Id	Name	Create version	Delete version
1	New_value	2	3

4. Query operation:

As you can see from the above description, the query must meet the following two criteria for a transaction to be queried:

1) The delete version number is greater than the current transaction version number, which means that the delete operation was made after the current transaction started.

2) Create a version number that is less than or equal to the current transaction version number, which means that the record is created in the transaction (equal to the case) or before the transaction starts.

This ensures that the transactions do not affect each other. From here, we can also realize a way to improve the system performance, that is:

Use the version number to reduce the contention for locks.

In addition, only two transaction isolation levels of read-committed and repeatable-read can be used MVCC

Read-uncommited because it is read UNCOMMITTED, there is no version issue

The serializable will lock all rows read.

MySQL MVCC (multi-version concurrency control)