Shulou(Shulou.com)05/31 Report--

This article will explain in detail how snapshots work in MVCC. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

MVCC

In MySQL (innodb storage engine), virtually every record records a rollback operation when it is updated. The latest value on the record, through the rollback operation, can get the value of the previous state.

If a value is changed from 1 to 2, 3, 4 in order, there will be a record similar to the following in the rollback log.

The current value is 4, but transactions started at different times will have different read-view when querying this record. As can be seen in the figure, in views A, B, and C, the values of this record are 1, 2, and 4 respectively. There can be multiple versions of the same record in the system, that is, multi-version concurrency control (MVCC) of the database. For read-view A, to get 1, you must perform all the rollback operations in the diagram to get the current value in turn.

How does InnoDB define a 'snapshot'

Each transaction in InnoDB has a unique transaction ID, called transaction id. It is applied to the transaction system of InnoDB at the beginning of the transaction and is strictly incremented in the order of application.

There are multiple versions of each row of data. Each time a transaction updates data, a new data version is generated and transaction id is assigned to the transaction ID of that data version, marked as row trx_id. At the same time, the old data version should be retained, and in the new data version, information can be obtained directly.

In other words, a row of records in a data table may actually have multiple versions (row), each with its own row trx_id.

According to the definition of repeatable readability, when a transaction starts, you can see the results of all committed transactions. But then, during the execution of this transaction, updates from other transactions are not visible to it.

Therefore, a transaction only needs to declare at startup time, "at the time of my startup, if a data version is generated before I start, I will recognize it; if it is generated after I start, I will not recognize it. I have to find the last version of it. Of course, if the previous version is not visible, you have to move on. Also, if the transaction updates its own data, it still has to recognize it.

In implementation, InnoDB constructs an array for each transaction to hold the transaction startup moment, and all transactions that are currently "active" ID. "active" means that it is started but has not yet been committed.

The minimum value of transaction ID in the array is marked as low water level, and the maximum value of transaction ID that has been created in the current system plus 1 is recorded as high water level.

This view array divides all row trx_id into several different cases.

In this way, there are several possibilities for a data version of row trx_id at the start of the current transaction:

If it falls in the green section, it indicates that the version is a committed transaction or is generated by the current transaction itself, and the data is visible

If it falls on the red part, it means that this version is generated by a transaction started in the future and is definitely invisible.

If it falls in the yellow part, there are two situations.

a. If row trx_id is in the array, it means that this version is generated by a transaction that has not been committed and is not visible.

b. If the row trx_id is not in the array, this version is generated by a committed transaction, visible.

For example:

Session A starts a transaction A. before transaction A starts, there are three active transactions in the system, and the ID is 909395.

So the ID of transaction An is 100.

At this point, the view array for transaction An is like this [90 93 95 100], where the low water level is 90 and the high water level is 100 million 101.

Now transaction A starts to read the data.

If you read that the ID is 104, which is greater than the high water level 101, it means that this version is generated by a transaction started in the future and is definitely invisible.

Read that the ID is 88, which is less than 90% of the low water level, indicating that this version is a committed transaction or generated by the current transaction itself. This data is visible.

Read that ID is 94, between low and high water levels, but not in the array [90 93 95 100], indicating that this version is generated by committed transactions, visible.

I read that ID is 93, and between low water level and high water level, this array [90 93 95 100] indicates that this version is generated by transactions that have not yet committed and is not visible.

This judgment rule is translated directly from the code logic, but as you can see, it is troublesome to use visibility in human flesh analysis.

So, let me translate it for you. For a data version, there are three situations for a transaction view, except that its own updates are always visible:

Version not submitted, not visible

The version was submitted, but it was submitted after the view was created and is not visible

The version has been submitted, and it was submitted before the view was created, visible.

This is the end of this article on "how snapshots work in MVCC". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

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.