Mvcc principle and unreal reading of [MySQL] transactions

Time:2022-5-24

First of all, we should understand mvcc. Mvcc is called multi version concurrency control, which is actuallySave a snapshot of the data at a certain time node

We actually hide two columns per row,Create version number, expired (deleted) version number, every time you start a new transaction,The version number will be incremented automatically

 

Take the user table as an example. Suppose we insert two pieces of data. They should actually be as long as this. The creation version number is incremented.

id name create_version delete_version
1 Zhang San 1  
2 Li Si 2  

At this time, it is assumed that Xiaoming will execute the queryA transaction will be started by default. The current version is current_ version=3

select * from user where id<=3;

 

Meanwhile, Xiao Hong starts the transaction to modify the record with id = 1 at this time,The transaction version of Xiaohong is current_ version=4

Update user set name = ‘Zhang Sansan’ where id = 1;

This is the result of Xiaohong’s successful implementation

id name create_version delete_version
1 Zhang San 1  
2 Li Si 2  
1 Zhang Sansan 4  

 

Meanwhile, Xiaohei is deleting data with id = 2,Xiaohei’s version is current_ version=5, the result of Xiaohei’s execution is like this.

id name create_version delete_version
1 Zhang San 1  
2 Li Si 2 5
1 Zhang Sansan 4  

The principle of mvcc is to find that the created version is less than or equal to the current transaction version, and the deleted version is empty or greater than the current transaction version, Xiao Ming’s real query should be like this

select * from user where id<=3 and create_version<=3 and (delete_version>3 or delete_version is null);

Therefore, the last name Xiaoming found with id = 1 is’ Zhang San ‘, and the record with id = 2 can also be found. This isIn order to ensure that the data read by the transaction already exists before the start of the transaction, or it is inserted or modified by the transaction itself

 

Phantom reading is like this. For example, in the following example of adding users, assuming that the user name is the only index, duplication is not allowed

Xiao Ming wants to insert a piece of Wang Wu‘s data and start the transactioncurrent_version=6Query the record named ‘Wang Wu’ and find that it does not exist.

At the same time, Xiaohong starts the transactioncurrent_version=7Insert a piece of Wang Wu’s data, and the result is as follows:

id Name create_version delete_version
1 Zhang San 1  
2 Li Si 2  
3 Wang Wu 7  

Xiao Ming inserts the record with the name ‘Wang Wu’ and finds that the unique index conflicts and cannot be inserted. When querying, he clearly can’t see Wang Wu, but the insertion always says it’s repeated. This is unreal reading.