Concurrency Control for MVCC(Mutil-version Concurrency Control). MVCC is a method of concurrency control, which is generally used in database management system to achieve concurrent access to the database.

In Mysql’s InnoDB engine, transactions at READ COMMITTD and REPEATABLE READ isolation levels access records in the version chain for SELECT operations.

This allows other transactions to modify this record, and each change will be recorded in the version chain anyway. SELECT can go to the version chain to get records, which implements read – write, write – read concurrent execution, improve system performance.

Let’s see how this works.

Version of the chain

Let’s first understand the concept of version chain. In the InnoDB engine table, there are two necessary hidden columns in its clustered index records:

  • trx_id

This ID is used to store the transaction ID each time a clustered index record is modified.

  • roll_pointer

    Every time a cluster index record is modified, the old version is written to the Undo log. This roll_pointer stores a pointer to the previous version of the clustered index record and retrials the previous version of the record. (Note that the undo log for the insert operation does not have this property because it does not have an older version)

Let’s say we now have a transaction id of 60 that executes this record modification statement

In this case, the version chain exists in the undo log

ReadView

Having said version chain, let’s look at ReadView. The difference between committed reads and repeatable reads is their strategy for generating readViews.

The main thing in ReadView is to have a list of all the active read/write transactions in our system that have not yet been committed by begin. 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].

  • If the transaction ID of the record version you are accessing is 50, which is smaller than id80, the smallest in the current list, then the transaction was previously committed and therefore is accessible to the currently active transaction.

  • If the transaction id of the version of the record you want to access is 70, and the transaction is between the maximum and minimum of the list ID, then check if it is in the list. If it is, the transaction has not committed yet, so the version cannot be accessed. If not, the transaction has been committed, so the version can be accessed.

  • If the transaction ID of the version of the record you are accessing is 110, which is larger than the transaction list maximum ID100, then the version occurred after ReadView was generated and therefore cannot be accessed.

If the transaction ID of the latest record does not meet the conditions and is not visible, then go to the previous version and compare the current transaction ID with the transaction ID of this version to see whether the version can be accessed, and so on until the visible version is returned or the end.

For example, at committed read isolation level:

For example, there is a transaction whose ID is 100, and the name is changed so that the name is equal to xiaoming 2, but the transaction has not been committed. The version chain is

When another transaction initiates a select statement to query the record with id 1, the generated ReadView list is only [100]. Trx_id = 100, trx_id = 100, trx_id = 100, trx_id = 100, trx_id = 100, trx_id = 100, trx_id = 100, trx_id = 100

At this time, I continued to look for the next record whose name was Xiaoming 1 through the pointer, and found that TRx_id was 60, which was smaller than the minimum ID in the list, so I could access it. The direct access result was Xiaoming 1.

In this case, we commit the transaction with transaction ID 100, create a new record with transaction ID 110 and modify the record with ID 1, and do not commit the transaction

This is when the version chain is

The select transaction now performs another query for the record with ID 1.

This is where the key comes in

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

According to the above statement, you go to the version chain by trx_id comparison to find the appropriate result is xiaoming 2.

If you are at the repeatable read isolation level, your ReadView will be the same as the ReadView generated on the first select, with the value of the list being [100]. So the result of select is Xiaoming 1. So the second select result is the same as the first, so it is called repeatable!

That is, committed read isolation generates a separate ReadView at the start of each query, whereas repeatable read isolation generates a ReadView at the first read, and subsequent reads reuse the previous ReadView.

This is the MVCC of Mysql, through the version chain, to achieve multiple versions, concurrent read-write, write-read. Different isolation levels are implemented through ReadView generation policies.

If there are mistakes welcome to correct!

Personal public account: Yes training level guide

There are related interview advanced materials (distributed, performance tuning, classic books PDF) waiting to be collected