Mysql-mvcc in detail

Let’s first explain what MVCC is:

The basic idea of multi-version concurrency control is that each transaction generates a new version of data, and different versions are selected to read the data. It can not only realize the integrity of the transaction results, but also improve the concurrent read and write performance.



Let’s review the isolation levels in MySql transactions;

Read uncommitted, read committed, repeatable read, serialized

MVCC is mainly aimed atReading has been submitted Repeatable readThe two isolation levels;



MySql has two hidden columns per row by default: transaction ID, rollback pointer (pointing to previous version)



When querying data, there is a read-view to determine which version of the undo.log file to read.

Here is a direct example to start four transactions :(the following operations are based on this figure)



Transaction 1; change name to ‘A’;



Transaction 2; change name to ‘B’;



At this point, the rollback pointer points to the previous record;

Transaction 3, change the name value to ‘C’.



At this point, the rollback pointer points to the previous record;

Transaction 1; change name to ‘D’;



At this point, the rollback pointer points to the previous record;

As you can see, one has been formedVersion of the chain;



First say read submitted:

Transaction 4, the query operation, generates a read-view containing all of the itemsHave not been submittedArray of transactions, and currentThe biggestThe transaction. When select, the read-view values from theVersion of the chainIn theThe latest dataCheck if trx_id is in read-view if trx_id is in read-view



If trx_id is smaller than 1, it is readable. If trx_id is larger than 3, it is newly generated and unreadable. If trx_id is in the range [1,3], it is yesHave not been submittedTransaction, unreadable; So, the first query result in transaction 4 is name =B; Select * from ‘name’ where ‘name’ = ‘name’C;



Repeatable read and read committed difference:

Read committed: a new read-view is generated each time a select is performed.

Repeatable read: the first read-view is reused for each select.



To sum up:

Readable if the current record: transaction ID < the minimum ID of an uncommitted transaction;

If the current record: minimum ID <= transaction ID <= maximum ID, determine whether the transaction ID is in the array of uncommitted transaction ids, if not, it can be read;

If the current record: transaction ID > maximum ID, it is unreadable;