How the multi-version concurrency control for Mysql's INNODB transaction is implemented MVCC

Mvcc:multi-version Concurrency Control multiple version concurrency controls. I use my own words to make a summary of the MVCC: the data at a certain point in time snapshot of the version control, the purpose is to avoid the use of various locks affect concurrency performance

MySQL lock mechanism
When it comes to locks, there are several locking mechanisms available in MySQL
Read/write Lock
Table lock (MyISAM using a table lock)
Row-level locks (InnoDB use row-level locks)

1th:

MVCC is not unique to MySQL, oracle,postgresql and so on are used.

MVCC does not simply use row locks, but instead uses row-level locks (row-level locking).

The basic principles of MVCC are:

The implementation of MVCC is achieved by saving the snapshot of the data at a certain point in time. This means that a transaction can see a consistent view of the data in the same transaction regardless of how long it is running. Depending on the time at which the transaction started, it also means that the data in the same table as seen by different transactions at the same time may be different.

Basic features of MVCC:

• Each row of data has a version that is updated every time the data is updated.
• Copy out the current version at random changes, no interference between the transactions.
• Compare version number on save, overwrite original record if successful (commit), failure to discard copy (rollback)

InnoDB Storage Engine MVCC implementation strategy:

Save two additional hidden columns in each row of data: The version number at the time the current row was created and the version number when it was deleted (possibly empty). The version number here is not the actual time value, but the system version number. The system version number is automatically incremented for each new transaction that starts. The system version number at the start of a transaction is used as the version number of the transaction to compare with the version number of the query for each row of records.

Each transaction has its own version number, so that the data versioning is achieved by comparing version numbers when performing CRUD operations within a transaction. See below for specific practices.

　　

MVCC specific operation is as follows:

SELECT: InnoDB checks each row of records according to the following two criteria:

1) InnoDB only finds data rows that are earlier than the current version of the transaction (that is, the system version number of the line is less than or equal to the system version number of the transaction), which ensures that the transaction reads only the rows that existed before the start of the transaction, either by the transaction itself or by a modification.

2) The deleted version of the row is either undefined or larger than the current transaction version number. This ensures that the transaction is read to a row that has not been deleted before the transaction begins.

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 for each row deleted as a row delete identity.

UPDATE: InnoDB to insert a new row of records, save the current system version number as the line version number, while saving the system's version number as the original row as the delete identity.

Save these two additional system version numbers so that most operations can be unlocked. This design makes the data manipulation simple, performs well, and ensures that only rows that conform to the standard are read. The disadvantage is that each row of records requires additional storage space, more row checking is required, and some additional maintenance work.

MVCC only works under the two isolation levels of repeatable read and read commited, and the other two isolation levels and MVCC are incompatible.

)

Take a timely summary as a habit

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 that are read.

Reference: MySQL MVCC (multiple version concurrency control): http://www.cnblogs.com/dongqingswt/p/3460440.html

Multi-version concurrency control: http://blog.csdn.net/xifeijian/article/details/45230053

How the multi-version concurrency control for Mysql's INNODB transaction is implemented MVCC