What is MVCC
Concurrency Control. MVCC, for Multi-version Concurrency Control.
Why MVCC is needed
First understand the database concurrency vendor, there are three common concurrency scenarios, respectively:
- Read-read: There are no problems and no concurrency control is required
- Read-write: Has thread safety issues, may cause transaction isolation issues, may encounter dirty reads, unreal reads, unrepeatable reads
- Write – write: There are thread safety issues and there may be update loss issues, such as type 1 update loss, type 2 update loss
In the MySQL InnoDB engine, the main purpose is to improve the database concurrency performance, with a better way to deal with read-write conflict, even if there is read and write conflict, can also do not lock, non-blocking concurrent read.
Let’s start with a few records. This is a static, pure piece of data from our database (the next three columns are hidden data from the database, as every record will have). The reason why it’s static and pure is because the last two columns are null, so what do I mean by the last two columns? DB_TRX_ID specifies the transaction ID. Null means no transaction is currently working on the data. The last column DB_ROLL_PTR specifies the address of the rollback pointer. It was initially inserted once and it hasn’t been modified since.
DB_TRX_ID indicates that it is being processed by 1 transaction. The DB_ROLL_PTR rollback pointer is not null, indicating that it has been modified before. 0x12446545 shows the address of the modified record.
Now, MVCC how does it help us resolve read/write conflicts?
Let’s start with a new member, undo logging
The question then arises what is undo logging and why is it needed. Here are two images to see for yourself.
The first is the undo log generated after making a change to the data:
The second is the undo log generated after two changes to the data:
From the above, we can see that the modification of the same record by different transactions or the same transaction will cause the undo log of the record to become a linear list of record versions. In other words, the linked list, the beginning of the undo log is the latest old record, and the end of the undo log is the earliest old record, which explains what undo log is.
So why do we need undo logs? What can undo logs do? That’s where MVCC comes in. In order to resolve read-write conflicts, MVCC now assumes that there are two transactions operating on the same row, one is a read transaction and the other is a write transaction. If Tom is 25 years old, then there is a concurrency conflict. If Tom is 25 years old, there is a concurrency conflict.
Transaction 1 read operation:
The MVCC came out and said, don’t move at all, let me take a picture (this picture is Read View) to record this moment, and then use this picture to analyze the complexity of this moment.
The Read View not only maintains the active transaction IDS of the system at the time the transaction is Read from the trx_list, but also has two properties up_limit_id (the lowest transaction ID in the trx_List). Low_limit_id (Record the maximum transaction ID in the trx_LIST list,
DB_TRX_ID = up_limit_id(1); low_limit_id(2) = low_limit_id(2); Finally, it determines whether 1 is in the active transaction in the trx_list. Finally, it finds that the transaction with transaction ID 1 is in the active transaction list. Then, it finds the transaction with transaction ID 1 in the undo log. (This place is also very simple, is to compare the version number, can only view the version of your younger, not your older version ID)