A Brief Analysis of MySQL - MVCC

Version Chain

In InnoDB engine tables, there are two hidden columns in their clustered index records:

• trx_id: used to store the transaction id when modifying data
• roll_pointer: Every time a clustered index record is modified, the old version will be written to the undo log. This roll_pointer stores a pointer that points to the location of the previous version of this clustered index record, through which the record information of the previous version can be obtained.

For example, there is a transaction with trx_id 60 that is executing the following statement: update table set name = 'Xiaoming1' where id = 1

At this point, there is a version chain in the undo log

The version chain can be similar to git, which can perform version control on a row of data and can be rolled back through undo_log

ReadView

The difference between Read Committed and Repeatable Read is that they have different strategies for generating ReadView.

ReadView mainly has a list to store the currently active read and write transactions in our system (begin tx that has not been committed). This list is used to determine whether a version of the record is visible to the current transaction. Assume that the transaction id in the current list is [80,100].

id <= 80 (minimum transaction id)
id >= 80 && id <= 100
id >= 100

These records are searched in the version chain. The most recent record is searched first. If the transaction ID of the most recent record does not meet the conditions and is not visible, the previous version is searched and the ID of the current transaction is compared with the transaction ID of this version to see if it can be accessed. This process is repeated until a visible version is returned or the process ends.

For example, in the Read Committed isolation level:

For example, at this time there is a transaction with transaction id 100, which modifies the name so that the name is equal to Xiaoming 2, but the transaction has not been committed yet. The version chain at this time is

At this time, another transaction initiates a select statement to query the record with id 1, and the generated ReadView list is only [100]. Then go to the version chain to find it. First, you must find the most recent one. You will find that trx_id is 100, which is the record with name Xiaoming 2. It is found in the list, so it cannot be accessed.

At this time, we continue to look for the next record named Xiaoming 1 through the pointer, and find that trx_id is 60, which is less than the minimum id in the list, so it can be accessed. The direct access result is Xiaoming 1.

At this time, we commit the transaction with transaction id 100, and create a new transaction with id 110 to modify the record with id 1, and do not commit the transaction.

--trx_id = 110
BEGIN;
update table set name = 'Xiaoming3' where id = 1

At this time, the version chain is

At this time, the previous select transaction executed another query to query the record with id 1.

Different isolation levels result in different results here

If you are in the committed read isolation level, you will create a new ReadView at this time, and the value in your active transaction list will change to [110].

According to the above statement, you go to the version chain and compare trx_id to find the appropriate result, which is Xiao Ming 2.

If you are in the repeatable read isolation level, your ReadView is still the ReadView generated during the first select, that is, the value of the list is still [100]. So the result of select is Xiao Ming 1. So the result of the second select is the same as the first, so it is called repeatable read!

This is MySQL's MVCC, which implements multiple versions through version chains, and can perform concurrent read-write and write-read operations. Different isolation levels are achieved through different ReadView generation strategies.

The above is a brief analysis of the details of MySQL - MVCC. For more information about MySQL mvcc, please pay attention to other related articles on 123WORDPRESS.COM!