Briefly analyze the snapshots in MVCC and see how snapshots work?

This article will take you to understand snapshots in MVCC and see how snapshots work in MVCC? Hope it can help everyone!

MVCC

In MySQL (innodb storage engine), in fact, each record will record a rollback operation at the same time when it is updated. The latest value on the record can be obtained by rolling back the value of the previous state.

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

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

How InnoDB defines a ‘snapshot’

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

And each row of data also has multiple versions. Every time a transaction updates data, a new data version will be generated, andtransaction id will be assigned to the transaction ID of this data version, recorded as row trx_id.At the same time, the old data version should be retained, and in the new data version, there may be information that can be obtained directly.

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

According to the definition of repeatable read,When a transaction is started, all submitted transaction results can be seen. But then, while this transaction is executing, updates from other transactions are not visible to it.

Therefore, a transaction only needs to declare when starting, "Based on the moment I start, if a data version is generated before I start, it will be recognized; if I start If it is generated later, I will not recognize it. I have to find its previous version." Of course, if the "previous version" is not visible either, you have to keep looking forward. Also, if the data is updated by the transaction itself, it still has to recognize it.

In terms of implementation, InnoDB constructs an array for each transaction to save all transaction IDs that are currently "active" at the moment the transaction is started. "Active" means that it has been started but has not yet been submitted.

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

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

In this way, for the startup moment of the current transaction, the row trx_id of a data version has the following possibilities:

• If it falls in the green part, it means that this version is a submitted transaction or generated by the current transaction itself, and this data is visible;

• If it falls in the red part, it means that this version It is generated by transactions started in the future and is definitely invisible;

• If it falls in the yellow part, it includes two situations

a. If row trx_id is in the array, it means that this version is generated by a transaction that has not yet been submitted and is invisible;
b. If row trx_id is not in the array, it means that this version is a transaction that has been submitted. Generated, visible.

For example:

session A starts a transaction A. Before transaction A starts, there are three active transactions in the system, with IDs 90 93 95.
Then the ID of transaction A is 100
At this time, the view array for transaction A is like this [90 93 95 100], where the low water level is 90 and the high water level is 100 1=101;
Now the transaction A starts reading data

• If you read that the ID is 104, which is greater than the high water level of 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 the low water level of 90, means that this version is a submitted transaction or generated by the current transaction itself, and this data is visible;
• read that the ID is 94, which is between the low water level and the high water level. time, but is not in the array [90 93 95 100], it means that this version is generated by a transaction that has been submitted and is visible.
• I read that the ID is 93, which is between the low water level and the high water level. This [90 93 95 100] array indicates that this version is generated by a transaction that has not yet been submitted and is invisible;

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

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

• The version is uncommitted and invisible;

• The version has been submitted, but it was submitted after the view was created, so it is invisible; the

• version has been submitted, and it was submitted before the view was created, so it is visible.

[Related recommendations:mysql video tutorial]

The above is the detailed content of Briefly analyze the snapshots in MVCC and see how snapshots work?. For more information, please follow other related articles on the PHP Chinese website!