InnoDB Transaction isolation level

Transaction isolation level

The SQL standard defines four isolation levels, including specific rules,Used to limit which changes inside and outside the transaction are visible and which are invisible. Low-level Isolation generally supports higher concurrent processing and lower system overhead.

Read uncommitted (read uncommitted content)

At this isolation level, all transactions can see the execution results of other uncommitted transactions. This isolation level is rarely used in practical applications, because its performance is no better than other levels. Read uncommitted data, also known as dirty read ).

Read committed (read submitted content)

This is the default isolation level for most database systems (but not for MySQL ). It satisfies the simple definition of isolation: a transaction can only see changes made by committed transactions. This isolation level also supports the so-called nonrepeatable read, because other instances of the same transaction may have a new commit during the processing of this instance, so the same select may return different results.

Repeatable read (repeable)

This is the default transaction isolation level of MySQL. It ensures that multiple instances of the same transaction will see the same data rows when reading data concurrently. However, theoretically, this will lead to another tricky problem: phantom read ). In short, phantom read refers to when a user reads data in a certain range, another transaction inserts a new row in this range. When the user reads data in this range, there will be a new Phantom line. InnoDB and Falcon storage engine use multiple versionsConcurrency Control(MVCC, multiversion concurrency
ControlGap lock. Note: In fact, multiple versions only solve the problem of non-repeatable read, while the gap lock (also known as Concurrency Control) solves the phantom read problem.

Serializable (serializable)

This is the highest isolation level. It forces transaction sorting to make it impossible to conflict with each other, thus solving the phantom read problem. In short, it adds a shared lock to each read data row. At this level, there may be a lot of timeout and lock competition.

Dirty read: a transaction has updated the data, and another transaction has read the same data at this time. For some reason, the previous rollback operation is performed, the data read by the other transaction is incorrect.

Non-repeatableread: the data in the two queries of a transaction is inconsistent. This may be because a transaction is inserted in the middle of the two queries to update the original data.

Phantom read: a phantom read occurs when a row is inserted or deleted and the row is within the range of the row being read by a transaction. The row range for the first read of the transaction shows that one row no longer exists in the second read or subsequent read because the row has been deleted by other transactions. Similarly, due to the insert operation of other transactions, the second or subsequent read of the transaction shows that a row does not exist in the original read. Phantom read is actually a non-repeated read phenomenon, but it is only relative to insert and delete operations, and the above non-repeated read phenomenon, but focuses on the update operation. The reason for this is that the new row of insert has no version information, and it must be determined through a range.

Note: The client here is also a transaction.

Set sessiontransaction isolation level read uncommitted;

Set autocommit = 0;

Dirty read Demo:

Client 1

Figure 1

Client 2

Figure 2

The above two figures clearly show that when tx_isolation = readuncommitted, when a transaction (transaction 1) modifies data but is not committed, another transaction (transaction 2) yes, you can access the modified data. At this time, if the previous transaction cancels the previous rollback, the data obtained by transaction 2 is a "dirty" data. Next, let's take a look at whether read committed has such a situation.

Client 1

Figure 3

Figure 4

Figure 3 shows that client 2 has been updated (but no commit is available). In this case, client 1 reads the previous data. After client 2 truly commit, client 1 can read the updated data. In addition, we can also see another phenomenon through figure 3, which means that the data read twice in a transaction (Client 1) is inconsistent. Next, let's see if this phenomenon exists at the Repeatable read level.

Repeatable read

Figure 5

Figure 6

Figure 5 shows that even if Client 2 commit is followed but client 1 does not commit, the results of accessing the same record are the same at any time in the transaction. Therefore, Repeatable read does not have the problem of repeated reading.

Phantom read:In fact, it is similar to non-repeated reading, but phantom reading refers to the number of Row Records (a range), while non-repeated reading is relative to a record. For example, in transaction 1, the number of records in this range is 10 for the first time, but another transaction deletes a record in this range, in this case, the re-reading of transaction 2 is inconsistent with the result of the first read. InnoDB solves this problem through mcvv + gap lock. Therefore, InnoDB will not experience phantom read at the Repeatable read level. This problem does not occur in current serialization, because

Serializing transactions

Figure 7

Figure 8

Note: The comment sequence above indicates the sequence in which operations or results appear. Transaction 1 is executed before transaction 2. Figure 7 and 8 show that after transaction 1 selects table tx_test, update of transaction 2 is blocked (because of a shared lock ), update is actually executed only after transaction 1 commit, and tx_test is occupied by transaction 2 (exclusive lock), so select of client 1 (transaction 3) is also blocked, it can be executed only after transaction 2 commit.

MVCC is introduced to reduce the number of locks to improve concurrency. MVCC only works at the Repeatable read and read commit isolation levels. Read uncommitted only reads the data row of the latest version each time, and serializable locks each read operation, so MVCC is not required.

The above are four isolation levels supported by InnoDB and their problems. At the same time, we have verified these problems through experiments.

References:

High-performance MySQL version 2