Introduction to MySQL isolation level, lock and MVCC

This article aims to clarify the relationship between these concepts and their functions. Understand the locking operation and MVCC version control of each SQL statement when Mysql opens a transaction. To keep the discussion simple, this article ignores GAP locks (gap locks, range locks).

We often require high concurrency and high availability. It means evaluating from the perspective of quality and quantity. Anything can be analyzed from these two perspectives. In the MySQL database, transactions are used to ensure quality, and MVCC is used to ensure quantity.

Transactions

We use transactions to ensure that the results of each SQL statement are executed in accordance with our expectations. We say that transactions must have ACID properties. The three in ACID: atomicity, consistency and persistence actually describe the same thing, ensuring the reliability of SQL execution results. Isolation is more complicated. It describes the performance of the database in concurrent scenarios. However, the amount of concurrency is not fixed, and different businesses may have different requirements. In order to make the database adapt to different concurrent scenarios, great people have defined four isolation levels: Read Uncommited, Read Committed (RC), Repeatable Read (RR), and Serializable. As the database isolation level increases, the data concurrency capability decreases.

Isolation Level

How the database behaves under the standard isolation level can be found at https://www.jb51.net/article/116477.htm. Here we only discuss the concepts of shared locks and exclusive locks, read plus shared locks, write plus exclusive locks:

At the RC isolation level, an exclusive lock is added when data is modified. The lock is released when the transaction ends, and other transactions are not allowed to read the lock, thus solving the dirty read problem. (The shared lock is released on the spot)
At the RR isolation level, a shared lock is added to read data and released when the transaction ends. Other transactions are not allowed to modify the data, thus solving the problem of non-repeatable read. (Shared lock transaction ends and is released)

In fact, all operations are serialized. And Mysql has optimized it. When one transaction is reading, other transactions cannot write, and when one transaction is writing, other transactions cannot read? I can still solve the dirty read and non-repeatable read problems without doing this. MVCC appeared. (This also makes the problem more and more complicated, and the differences begin to appear under the RR isolation level. It happens that the default isolation level of MySQL is RR.)

MVCC

MVCC stands for multi-version concurrency control, which uses dual version numbers to solve the problem of data isolation. ("create" has a version number, "delete" has a version number, and the modification operation is divided into "delete" and "create") Each transaction will generate a version number when it starts to add, delete, modify and query each table. Each transaction can only query data with a "create" number less than this version number and a "delete" number greater than this version number. In this way, the operations of adding, deleting and checking can be performed concurrently, and only the modification operations must be queued. In this way, even without shared locks, the problem of non-repeatable reads is solved, because after other transactions modify the data, the version number of the data is larger than mine, and I will not read it.

MVCC concurrency at RR isolation level

After the introduction of MVCC, everything seems to be going well. However, have you ever thought about what two transactions read when they update a piece of data one after another and then read that data again? Haha, this is impossible to happen because the modification operation is serial, and another transaction must commit this transaction before it can modify it. OK, let's change the question. Two transactions successively perform a +1 operation on a piece of data. After the other transaction is committed, this transaction performs +1 again and then reads the data again. Will this transaction read the result of +1 or +2? If +2 is read, doesn't that destroy the isolation and read the data submitted by other transactions?

However, this is indeed the case. Other transactions have been submitted, and this transaction has also modified the data. Of course, it is necessary to read +2 afterwards. Although it was originally 0, and this transaction only added 1, it became 2 after reading, which is a bit uncomfortable. Indeed, under the standard RR isolation level, because all operations are serial, after this transaction reads a row of data, other transactions cannot modify this data. This data is always operated by this transaction only, so isolation is strictly met. However, MySQL's RR enhances the concurrency of reading and writing. Only when two transactions modify a piece of data at the same time, they need to be serialized. All other operations can be performed in parallel. So this result is caused, it seems that non-repeatable read has occurred. However, this non-repeatable read is actually in line with our intuitive feeling. After the data is modified in this transaction, of course the latest data must be read.

To analyze the process:

The data create version number is 0

Transaction 1 version number is 1, read data value=0

Transaction 2 version number is 2, the modified data value+1=1, the original data delete version number is 2, the new data create version number is updated to 2, commit

Transaction 1 modifies the data value+1=2. (Since the modification is the current read, the data with the largest version number is always read, so the value read is 1.) After the modification, the delete version number is 1.

The new data create version number is 1

This transaction reads data value=2

In-depth analysis:

In fact, the above description also has loopholes. What if there is a third transaction version number 3? Since the version number is 3, can we directly read the uncommitted data of transactions 1 and 2? In fact, in MVCC, each transaction also has a minimum visible version low_limit_id (records with transaction number >= low_limit_id are invisible to the current transaction), which filters out the transactions that are currently being executed but not committed. For example, transaction 3, although the version number is 3, low_limit_id = 1, so the changes made by transactions 1 and 2 are not visible to 3.

Summarize

In order to solve the isolation problem, no stupid method such as completely copying the data was used. Traditional databases use shared locks and exclusive locks to serialize read and write operations; MySQL uses MVCC and exclusive locks, which allow read and write operations to be performed in parallel. Mysql behaves the same as the traditional method at isolation levels below RR. It differs from the traditional method at isolation level RR. This is reflected in the fact that after a transaction updates a piece of data, it can read the modifications committed by other transactions to the piece of data.