Shulou(Shulou.com)06/02 Report--

Today, I would like to talk to you about what is the understanding of Mysql's MVCC. Many people may not know much about it. In order to make you understand better, the editor has summarized the following for you. I hope you can get something according to this article.

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

In Mysql's InnoDB engine, transactions at committed read (READ COMMITTD) and repeatable read (REPEATABLE READ) isolation levels access records in the version chain for SELECT operations.

This allows other transactions to modify the record, and each change is recorded in the version chain anyway. SELECT can get records from the version chain, which realizes the concurrent execution of read-write, write-read, and improves the performance of the system.

Let's take a look at how it is realized.

Edition 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 record:

Trx_id

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

Roll_pointer

Each time a change is made to which clustered index is recorded, the old version is written to the undo log. This roll_pointer stores a pointer to the location of the previous version of the clustered index record, which is used to obtain the record information of the previous version. (note that the undo log for the insert operation does not have this property because it does not have the older version.)

For example, there is a modification statement for this record executed by a transaction whose id is 60.

At this point, a version chain exists in the undo log

ReadView

After talking about the version chain, let's take a look at ReadView. The difference between committed reads and repeatable reads is that they have different strategies for generating ReadView.

The main thing in ReadView is that there is a list of read and write transactions currently active in our system, that is, begin transactions that have not yet been committed. Use this list to determine whether a version of the record is visible to the current transaction. Suppose the transaction id in the current list is [80100].

If the transaction id of the record version you want to access is 50, which is smaller than the smallest id80 in the current list, the transaction has been committed before, so it is accessible to the currently active transaction.

If the record version you want to access has a transaction id of 70 and finds that the transaction is between the maximum and minimum values of the list id, then determine whether it is in the list. If it is, it means that the transaction has not been committed, so the version cannot be accessed. If not there, the transaction has been committed, so the version can be accessed.

If the transaction id of the record version you want to access is 110, which is larger than the maximum id100 of the transaction list, this version occurs after the ReadView is generated, so it cannot be accessed.

These records are all found in the version chain. First, look for the most recent record. If the recent record transaction id is not eligible and is not visible, then go to the previous version and compare the current transaction id with this version transaction id to see if the version can be accessed, and so on until the visible version is returned or finished.

For example, under the committed read isolation level:

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

At this point, another transaction initiates a select statement to query the record with id 1, and the ReadView list generated at this time is only [100]. Then go to the version chain to find it. First of all, be sure to find the nearest one. It is found that trx_id is 100. that is, the record whose name is Xiaoming 2. It is found in the list, so it cannot be accessed.

At this point, we will continue to find the next record with a name of Xiaoming 1 through the pointer. It is found that the trx_id is 60, which is less than the minimum id in the list, so it can be accessed. The result of direct access is Xiaoming 1.

At that time, we committed the transaction with transaction id 100, and created a new record with transaction id 110 and modified id 1, and did not commit the transaction

It's time for the version chain to be

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

At this time, the key point is coming.

If you have committed the 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, you go to the version chain to find the appropriate result through trx_id comparison is Xiaoming 2.

If you are at the readable isolation level, your ReadView is still the ReadView generated when it was the first select, that is, the value of the list is still [100]. So the result of select is Xiaoming 1. So the second select result is the same as the first time, so it is called repeatable!

That is, transactions under the committed read isolation level generate a separate ReadView at the beginning of each query, while the repeatable read isolation level generates a ReadView on the first read, and subsequent reads reuse the previous ReadView.

After reading the above, do you have any further understanding of Mysql's MVCC? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.