Explain MySQL isolation level in detail

ACID特性，也就是（Atomicity、Consistency、Isolation、Durability， 原子性 、 一致性 、 隔离性 、 持久性 ), this article mainly explains the Isolation , which is the transaction 隔离性 .

Overview

The four isolation levels are:

• 读未提交（read uncommitted）

  • When a transaction is not committed, the data it modifies can be seen by other things.

• 读已提交(read committed)

  • After a transaction commits, the data it modifies will be seen by other things.

• 可重复读(repeatable read)

  • The data seen during the execution of a transaction is always the same as the data seen when the transaction is started. Under the repeatable read isolation level, uncommitted transactions are also invisible to other transactions.

• 串行化(serializable)

  • 读 写 f5aedad7dc94c6af9b251be47a76fa4b---都会加锁 ， 读会加读锁 ， 写 写锁 will be added. When a read-write lock conflict is encountered, the later-accessed transaction must wait for the execution of the previous transaction to complete before continuing.

For the above four isolation levels, the isolation strength is increasing from top to bottom, but the execution efficiency will decrease accordingly. When setting the isolation level, you need to make a balance between 隔离级别 and 执行效率 .

For ease of understanding, here is an example:

在不同隔离级别下， 事务A cd03ba635f36519e1d2a8e4e88cfca37---会有哪些不同的返回结果，也就是图中的V1 、 V2 、 V3的What is the return value.

• If the isolation level is read uncommitted, 事务B modified data can be read by 事务A without committing the transaction, so V1 , V2 The values of V2 , V3 are all 2 .
• If the isolation level is read committed, 事务B needs to be committed after modification, the modified data can be read by 事务A , so the value of V1 is 1 ， 事务B提交， 事务A的数据,所以V2的值是2 , V3 The value of V3 is also 2 .
• If the isolation level is repeatable read, the transaction seen by the entire transaction is consistent with the data seen when the transaction is opened, and the data seen when the transaction is opened is 1 , so V1 , V2 The values of --ab447196435ef7dbf9eea8f0b8662da6 V2 are all 1 , after transaction A is submitted, the modified data is obtained, so the value of 2 V3 ---84f77acd092ec18
• If the isolation level is serialized, it will be locked. At this time, the thread corresponding to transaction B is in a blocked state. Transaction B will not continue until transaction A commits 将1改成2 . V1 、 V2的值是1 ， V3的值是2 。

MySQL The default isolation level is 可重复读 .

Isolation level problem

First understand a few basic concepts:

Dirty read : 事务A Modify data, 事务B After reading data 事务A Error rollback, the modified data is not submitted to the database, at this time 事务B Reading modified data is a dirty read , that is, a transaction reading uncommitted data from another transaction is a dirty read .

Non-repeatable read : 事务A read the same data multiple times on the same transaction, when 事务A has not ended, 事务B modified the data, because The modification of ---a5cd162df8977c60be9f4edd86fa1473 事务B resulted in inconsistent data read twice by 事务A , and the phenomenon that repeated reading was not possible.

Phantom reading : 事务A According to the conditional query, we get N of data, but at this time 事务B change or add 事务A M matches 事务A Data of the query condition. In this way, when 事务A is queried again, it is found that there will be N + M of data, resulting in phantom reading.

Several isolation levels may have problems with 脏读 , 不可重复读 or 幻读 , and the relationship between them is as follows:

isolation level	dirty read	non-repeatable read	hallucinations
读未提交	√	√	√
读提交	×	√	√
可重复读	×	×	√
串行化	×	×	×

• 读未提交 ： 脏读 、 不可重复读 、 幻读 ，读取未提交事务的数据，数据撤回了，就是一种脏读 . If other transactions modify the same data, the data read by the transaction is also different, so there is also 不可重复读 . At the same time, the data added by other transactions can also be read, so there is also 幻读 .
• 读已提交 : This isolation level can only read data submitted by other transactions, so 不存在脏读 . However, after reading the data for the first time, other transactions modify the data and submit the transaction. At this time, the data read by the transaction is inconsistent with the data read for the first time, which is 存在不可重复读 . At the same time, other transactions can add multiple pieces of data, also 存在幻读 .
• 可重复度读 : Indicates that the transaction seen by the entire transaction is consistent with the data seen by the opened transaction. Since the data is consistent, so 不存在不可重复读 . And will not read the data modified by other transactions, that is 不存在脏读 . For 同一个批 data, there may be additions, so there may be phantom reads.
• 窜行化 : When a read-write lock conflict occurs, the subsequent transaction will be executed after the previous transaction is completed, so it must be read or written first, and then read or write after execution, read Writes are done sequentially, so 不存在脏读 , 不存在不可重复读 , 也不存在幻读 .

Isolation Level Principle

The isolation level is mainly multi-version concurrency control MVCC , MVCC is achieved by saving a snapshot of the data at a certain point in time.

InnoDB MVCC implemented by saving two hidden columns behind each row of records, one is to save the creation time of the row, and the other is to save the expiration time of the row. Of course it is not the time that is stored, but 系统版本号 . Each time a new transaction is opened, the system version number is automatically incremented first, and the system version number will be used as the version number of the transaction to compare with the version number of each row of records queried. For example, under the 可重复读 isolation level, how does MVCC operate:

• SELECT

  • InnoDB will check each row of records against the following two conditions:

    • 1. InnoDB Only find data rows whose version number is earlier than the current transaction (the system version number is less than or equal to the system version number of the transaction), so as to ensure that the rows read by the transaction either exist before the transaction starts, Either the transaction itself inserted or updated.
    • 2. The deleted version of the row is either undefined or greater than the current transaction version number. This ensures that rows read by a transaction are not deleted before the transaction begins.
  • Only records that meet the above two conditions can be returned as query results.

• INSERT

  • InnoDB Save the current system version number as the row version number for each newly inserted row.

• DELETE

  • InnoDB Save the current system version number for each deleted row as the row deletion identifier.

• UPDATE

  • InnoDB In order to insert a new record, save the current system version number as the row version number, and save the current system version number to the original row as the row deletion flag.

Holds two additional system version numbers, most read operations will work 不用加锁 . This design is that the operation of reading data is simple, the performance is good, and it is also guaranteed that only rows that conform to the standard are read. The downside is that each row of records requires additional storage space, more row checking work, and some extra maintenance work.

MVCC only takes effect under the two isolation levels 读已提交 and 可重复读 . MVCC cannot take effect under the other two isolation levels, because 读未提交 always read the latest data row, and there is no need to record the current transaction version number. 串行化 b7e3cc08f76d739717d2c5959704fbb6---会对所有的读写都会进行加锁， ---abb00bc079f8ad605df82100b2635977 先读、先写的先执行， 后读 、 后写 。 There is also no need to record and record version numbers for careful comparison.

The row data of InnoDB has multiple versions, each data version has its own row trx_id , and each transaction or statement has its own consistency view. The query statement is a consistent read, and the consistent read will determine the visibility of the data version according to row trx_id and the consistency view.

• Repeatable reads, only query data that was committed before the transaction started.
• The read is committed, and only the data submitted by other transactions before the query statement is started.

Summarize

• Four isolation levels:

  • 读未提交 : The data will read data that other transactions have not updated to the data. There may be problems with 脏读 , 不可重复读 , 幻读 .
  • 读已提交(read committed) ：数据只能读取其他事务提交的数据，不存在脏读 7031f44717540d8fb5aed467dd4d08bb--- ，但是可能会存在不可重复读 、 幻读的问题.
  • 可重复读(repeatable read) : The data seen during transaction execution is always the same as the data seen when the transaction is started. Under the repeatable read isolation level, uncommitted transactions are also invisible to other transactions. There is no 脏读 , 不可重复读 , but there may be a 幻读 problem.
  • 串行化(serializable) : When there is a 读写锁 conflict, the later accessed transaction will wait for the previous transaction to complete before continuing.

• MySQL use MVVC (multi-version concurrency control) to solve 读已提交 , 可重复读

  • Executing a SQL statement will save two hidden columns. One is to save the creation time, the other is to save the expiration time, and the stored 系统版本号 .

  • Every time a transaction is opened, the system will increment by one 系统版本号 as the version number of the transaction.

    • select , query data older than the current transaction.
    • insert Add version number.
    • delete the version number as the deletion flag for deleted lines.
    • update , insert a piece of data first, and save the current system version number. Also save the original line as the line delete flag.

refer to

Are transactions isolated or not?

High-performance MySQL