Interviewer: How does MySQL implement “thing isolation”?

preface

In MySQL, data is protected from other objects in RR isolation level. In MySQL, when other objects are committed, the data will be read to the data after the commit level. By what? That’s definitely the MVCC mechanic (Multi-version Concurrency Control). Most people are familiar with this, but I haven’t looked at how it works before, so I’m writing this post to document it!

Note: MySQL’s InnoDB engine supports high concurrency performance thanks to MySQL’s MVCC mechanism (thanks to undo log, read-view, etc.), but this article will not cover MVCC too much.

1. Isolation level of RC and RR

First, assume that there is an account table. Before transaction ABC is started, the balance in the account is 1, i.e

select balance from account =1; # the result is 1Copy the code

1.1 Query Results at the RR Transaction Isolation Level

When three transactions are enabled at the RR transaction isolation level, perform the following operations in different periods of time

• Transaction A (explicitly open transaction, manual COMMIT) : Query balance

• Transaction B (explicitly open transaction, manual COMMIT) : add 1 to the balance with id=1

• Transaction C (automatic commit without explicitly opening transaction) : add 1 to the balance with ID =1

We logically divide the results into three stages in time

• Phase 1: Transaction A immediately starts the transaction, and then transaction B immediately starts the transaction, and then transaction C first updates balance to 2 successfully, and the current balance=2;

• Phase 2: Transaction B updates the value of balance. At this point, it first reads that the latest value of balance is 2. Then set balance=balance+1 succeeds, and the current balance=3.

• Phase 3: Transaction A queries the value of balance, which is 1. Shouldn’t it be the current latest value of 3? That’s the focus of this blog post), followed by a commit to end the transaction, followed by transaction B

The last transaction reads A balance as A result of the 1, of course, RR is repeatable read, that is, A transaction see data during execution, always see starting with this transaction data is consistent, the current transaction regardless of submission, will not affect the data, I only need to read data based on snapshot, this is A snapshot. But what we’re going to talk about is how do we do it under MVCC?

Note: Begin /start transaction commands are not the starting point of a transaction, the transaction is actually started by the first statement that operates on the InnoDB table after they are executed. If you want to start a transaction immediately, use the start Transaction with consistent snapshot command.

I sorted out some information, and friends in need can click to get it directly.

JAVA Core Knowledge (PDF)

MySQL buckets

1.2 Query results at RC Transaction isolation level

Similarly, we turn on transaction ABC under RC isolation and observe the final balance result of transaction A.

In the end, transaction A reads balance with A result of 2, so RC is read committable, which literally means that as soon as other transactions commit, I can read the latest value of the current transaction, which is the current read. But what we’re going to talk about is how do we do it under MVCC?

In fact, this is because the consistent Read view used to implement MVCC, namely the Consistent Read View, is used to support RC (Read Committed) and RR (Repeatable Read) isolation levels.

Implementation of transaction isolation in MVCC

Before exploring how MVCC implements transaction isolation, we need to know the concepts of view arrays and consistent views to better understand how MVCC helps isolate transactions.

2.1 Multiple versions of data ROW

Each transaction in InnoDB has a unique transaction ID called transaction ID. It is applied to InnoDB’s transaction system at the beginning of a transaction and is applied in a strict ascending order.

There are multiple versions of each row. Each time a transaction updates the data, a new version of the data is generated and the transaction ID is assigned to the transaction ID of the data version, called Row trx_id. At the same time, the old data version is kept, and in the new data version, information can be retrieved directly from it (found in the undo_log file).

That is, a row in a data table may actually have multiple versions (rows), each with its own ROW TRx_id.

For the multi-version control process of a data ROW that has been updated three times at a certain time, draw the figure below to deepen our understanding.

From the figure, we can get:

• ROW has four versions v1-V4, that is, after three times of updating balance, the current version is V4, and the current balance has been updated to 4, which is the latest value

• InnoDB assigns row trx_id to the transaction ID generated by each update transaction.

• Undo_log can be used to rollback from V4 to V1, and find balance=1 of V1, that is, undo_log rollback version.

How InnoDB defines and creates snapshots can help you understand how InnoDB defines and creates snapshots!

2.2 View Array

The following parts are from the original sentences in the data, especially the parts in red may be difficult to understand, so you need to combine your own understanding and drawing

The way InnoDB defines a snapshot when a transaction is started, which transaction operations I can ignore, so I have to save in the snapshot. A transaction only needs to declare at startup, “At the time I started, if a version of the data was generated before I started, I recognize it; If IT was generated after I started it, I don’t recognize it, I have to find the previous version of it.

As an implementation, InnoDB constructs an array for each transaction that holds all transaction ids that are currently “active” at the moment the transaction is started. “Active” means it’s started but not yet submitted. The minimum value of transaction ids in the array is denoted as the low watermark, and the maximum value of transaction ids that have been created in the current system plus 1 is denoted as the high watermark. This array of views, together with the high water level, constitutes a read-view of the current transaction.

My understanding of low and high water levels:

Low water = Minimum ids of all currently started but uncommitted transaction sets = minimum ids of the last started but uncommitted transaction of the current transaction (minimum ids of all active transactions)

High watermark = TRANSACTION ID (Current ROW version/ROW trx_id) = Maximum number of transaction ids that have been created +1

For example, three ABC transactions at the RR isolation level are used as an example

• Before transaction A starts, there is only one active transaction ID 99 in the system.

• The version numbers of transactions A, B, and C are 100, 101, and 102 respectively, and there are only four transactions in the system.

• Before three transactions start, the row trx_id of (id,balance)=(1,1) is 90.

Thus, the view array for transaction A is [99], the view array for transaction B is [99,100], and the view array for transaction C is [99,100,101]. That is, the general formula of view array is: [{set of instantly active transaction ids when the current transaction is on}].

The visibility rules for data versions are based on row trx_id and consistency views, so we need to learn more about consistency views

2.3 Consistency View

The consistency view is made easier by understanding the array of views, that is, this array of views, together with the high water level, constitutes the read-view of the current transaction.

Again, take the three ABC transactions at the above RR isolation level as an example

• Before transaction A starts, there is only one active transaction ID 99 in the system, so the set of active things when transaction A starts is [99].

• The version numbers of transactions A, B, and C are 100, 101, and 102 respectively, and there are only four transactions in the current system. Therefore, the high water levels of transactions A, B, and C are 100, 101, and 102 respectively.

• Before three transactions start, the row trx_id of (id,balance)=(1,1) is 90.

Thus, the consistency view for transaction A is [99,100], for transaction B is [99,100,101], and for transaction C is [99,100,101,102]. That is, the general formula of consistency view is: [{set of transaction ids that are instantly active when the current transaction is started}, current row trx_id].

Analyze the results of the above flow chart:

The first valid update is transaction C, which updates balance=2, and the latest version of row trx_id=102, whereas the latest version of row trx_id is 99, which is active before transaction ABC, so 99 has become historical version 1.

The second valid update is for object B, update balance=3, where the latest version row trx_id=101, where row trx_id=102 becomes historical version 1, and row trx_id=99 becomes historical version 2;

When object A is queried, object B is not committed, but the generated (id, balance)=(1, 3) has become the current latest version. When object A reads data, the consistency view is [99, 100], and the read data is cut from the current version. Then compare row trx_id, so the following flow will occur:

• Row trx_id=101; row trx_id=101;

• Row trx_id=102; row trx_id=102; row trx_id=102;

• (1,1) row trx_id=90, which is lower than the low water level and is visible in green.

Finally, whenever object A is queried, the data is snapshot data (1, 1) generated by the consistency view [99, 100], that is, the data when Row trx_id=90. This is called consistent reading.

Conclusion:

There are three cases for a transactional view, in addition to its own updates being always visible:

• Version not submitted, not visible;

• The version is committed, but not visible, after the view is created;

• The version is committed and is visible before the view is created.

Now, we use this rule to determine the result of the query in the diagram where the view array of the query statements of transaction A was started

When:

• (1,3) has not been submitted, which belongs to case 1 and is not visible;

• (1,2) although committed, but after the view array was created, belongs to case 2, invisible;

• (1,1) is submitted before the view array is created, visible.

2.4 Current Read and Snapshot Read

(1) Current read and snapshot read rules

Of course, according to this consistent read logic, object B is valid after object C updates balance=2, but object B’s view array is generated at object C, so theoretically it shouldn’t be object B that sees (ID, balance)=(1, 1) (snapshot/history version)? The current version (1, 2) data cannot be seen. Why is the immediate data of object B (1, 3) after updating balance?

If transaction B select data once before update, the value is indeed balance=1. However, update cannot be performed on the historical version, otherwise the update of transaction C will be lost, so update operations will read the current version first, and then update.

Data is read first and then updated. The current read is the latest value, which is called “current read”. The current snapshot is read only when the data is queried. So before transaction B updates balance, it looks up the latest version (1, 2) and then updates to (1, 3). The snapshot data queried by transaction A is (1, 1), not the latest version (1, 3).

(2) Current read and snapshot read description

Current read: like select lock in share mode, select for update; Update, INSERT,delete(exclusive lock) operations are current reads. It reads the latest version of the record and locks the read record to ensure that other concurrent transactions cannot modify the current record.

Snapshot read: For example, the unlocked SELECT operation is snapshot read, that is, unlocked non-blocking read. The prerequisite for snapshot reads is that the isolation level is not serial. Snapshot reads at the serial level are degraded to current reads. Is based on multi-version control, so the snapshot read may not necessarily be the latest version of the data, but may be the previous historical version (snapshot data).

(3) RC read view rules under commit

Read commit logic is similar to repeatable read logic, with the main differences being:

• At the repeatable read isolation level, you only need to create a consistency view at the beginning of a transaction, which is shared by all other queries in the transaction.

• Under read commit isolation, a new view is recalculated before each statement is executed. In this case, Start Transaction with consistent Snapshot is equivalent to normal Start Transaction/BEGIN

Therefore, at the RC isolation level, the data queried by object A and object B are as follows:

• Transaction C immediately updates balance=2, then commits automatically to generate the latest version (1, 2), at which point the view data (1, 2) is recalculated;

• Check that the latest version of object B is (1, 2), and then update it to version (1, 3), which is the latest version of object B. Query the balance=3 of object B at this time. (After object B updates balance=3, a new view is calculated immediately. Instead of 1.

• At this time, object B has not been submitted, so it is invisible to object A, so the latest version (1, 2) submitted by object C is read by object A at this time.