Introduction to Mysql isolation level, lock and MVCC, mysql isolation level mvcc

Introduction to Mysql isolation level, lock and MVCC, mysql isolation level mvcc

This article aims to clarify the relationships between these concepts and their functions. Understand the lock operation and MVCC version control when each SQL statement is executed when Mysql starts a transaction. To make the discussion simple, this article ignores GAP locks (GAP locks and range locks ).

We often have high concurrency and high availability. It is evaluated by quality and quantity. Everything can be analyzed from these two perspectives. In Mysql databases, transactions are used to ensure the quality, while MVCC is used to ensure the quantity.

Transactions

We use transactions to ensure that the execution of the results of each SQL statement meets our expectations. We say that transactions must have ACID properties. ACID: atomicity, consistency, and durability are similar, ensuring the reliability of SQL Execution results. Isolation is complicated. Isolation describes the performance of the database in the concurrent scenario, but the concurrency is not fixed, and different services may have different requirements, in order to make the database adapt to different concurrent scenarios, the great people have defined four isolation levels: Read Uncommited, Read Committed (RC), Repeatable Read (RR), and Serializable. As the database isolation level increases, data concurrency also decreases.

Isolation level

For more information about the performance of databases at the standard isolation level, see explain:

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

In fact, all operations are serialized. Mysql optimizes it. When a transaction is read, other transactions cannot be written. When a transaction is written, other transactions cannot be read? I can solve the problem of dirty reads and non-repeated reads without doing so. MVCC appears. (This also makes the problem more and more complex, and different places also begin to appear at the RR isolation level, it happens that the default Mysql isolation level is RR)

MVCC

MVCC is a multi-version concurrency control. The dual version number is used to solve the data Isolation Problem. ("Create" is a version number, "delete" is a version number, and the modification operation is split into "delete" and "create ") each transaction generates a version number when it starts adding, deleting, modifying, and querying each table. Each transaction can only find data with "create" less than the version number and "delete" greater than the version number. In this way, the addition, deletion, and query operations can be performed concurrently. Only the modification operations must be queued. In this way, even if there is no shared lock, the problem of non-repeated read is solved, because after other transactions are modified, the data version is later than me, and I will not read it.

Concurrency of MVCC at the RR isolation level

After MVCC is introduced, it looks beautiful. However, have you ever wondered whether two transactions have successively updated one piece of data, and then the two transactions have read that piece of data separately? Haha, this is impossible at all, because the modification operation is serialized, and another transaction must commit this transaction before it can be modified. Okay. For another problem, the two transactions successively perform the plus 1 operation on one data record. After the other transaction is committed, the transaction will read the data more than 1, does this transaction read the results of + 1 or + 2? If read to + 2, it does not undermine isolation. Have you read the data committed by other transactions?

However, this is indeed the case. Other transactions have been committed, and the data of this transaction has been modified. Then, of course, you must read more than 2. Although it was originally 0, this transaction obviously only added 1, but after reading it, it became 2, a little uncomfortable. Indeed, at the standard RR isolation level, because the operations are serial, after this transaction reads a row of data, other transactions cannot modify this data, this data is always only operated by this transaction, so it strictly meets the isolation requirements. However, the RR of Mysql enhances the concurrency of reading and writing. Only when two transactions modify one piece of data at the same time must be serialized, and all other operations can be performed in parallel. Therefore, this kind of result appears as if it is not readable repeatedly. But this non-repeated read is actually in line with our intuitive feelings. After the data is modified in this transaction, of course, we need to read the latest data.

To analyze the process:

The data create version is 0.

Transaction 1 version is 1, read data value = 0

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

Transaction 1 changes the data value + 1 = 2 (since the modification is the current read, the data with the largest version number is always read, so the read value is 1) after the modification, the delete version number is 1,

Create version 1 for new data

The value of the data read in this transaction is 2.

In-depth analysis:

In fact, the above description is also vulnerable. What if the third transaction version is 3? Can I directly read uncommitted data from transactions 1 and 2 because the version number is 3? In fact, in MVCC, each transaction has a log with the lowest visible version low_limit_id (transaction number> = low_limit_id, which is invisible to the current transaction ), filter out the transactions that are currently being executed but have not been committed. For example, transaction 3, although the version number is 3, but low_limit_id = 1, so the changes to transaction 1 and transaction 2 are invisible to 3.

Summary

In order to solve the isolation problem, the full copy data method is not used. Traditional databases use shared locks and exclusive locks to serialize read/write operations. Mysql uses MVCC and exclusive locks to allow parallel read/write operations. Mysql is at or below the RR isolation level, which is consistent with the traditional method. At the RR isolation level, it is different from the traditional method, because after a certain data entry is updated in this transaction, can read the changes committed by other transactions to the data.