InnoDB Transaction ISOLATION LEVEL

The SQL standard defines a Class 4 isolation level, which includes specific rules to define which changes within and outside the transaction are visible and which are not . Low-level isolation levels generally support higher concurrency processing and have 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 real-world applications because it has no better performance than other levels. Reading uncommitted data is also known as Dirty reading (Dirty read).

Read Committed (read submit content)

This is the default isolation level for most database systems (but not MySQL default). It satisfies the simple definition of isolation: A transaction can only see changes that have been submitted to the firm . This isolation level also supports so-called non-repeatable reads (Nonrepeatable read), where data is inconsistent in two queries of a transaction, which may be the result of a transaction that was inserted in the middle of the two query process to update the original data, so the same select might return different results.

Repeatable Read (can be reread)

This is the default transaction isolation level for MySQL, which ensures that multiple instances of the same transaction will see the same rows of data while concurrently reading the data. In theory, however, this can lead to another tricky problem: Phantom Reading (Phantom read). To put it simply, Phantom reads when a user reads a range of data rows, another transaction inserts a new row within that range, and when the user reads the data row of that range, a new phantom row is found. The InnoDB and Falcon storage engines address this issue through a multi-version concurrency control (mvcc,multiversion Concurrency control Gap lock ) mechanism. Note: In fact, multi-version only solves the non-repeatable reading problem, and the gap lock (which is what it calls concurrency control) solves the Phantom reading problem.

Serializable (Serializable)

This is the highest isolation level, which solves the Phantom reading problem by forcing transactions to sort, making it impossible to conflict with one another. In short, it is a shared lock on every data row read. At this level, a large number of timeouts and lock competitions can result.

Dirty Reads (drity read): a transaction has updated one copy of the data, another transaction reads the same data at this time, for some reason, the previous rollback operation, the latter will read the data is not correct.

non-repeatable Read (Non-repeatableread): data inconsistency within two queries of a transaction, which may be a transaction that was inserted in the middle of a two-query process to update the original data.

Phantom Read (Phantom Read): a phantom read problem occurs when an INSERT or delete operation is performed on a row that belongs to the range of rows being read by a transaction. The row range for the first read of a transaction shows that one row has ceased to exist in the second or subsequent read because the row has been deleted by another transaction. Similarly, because of the insert operation of another transaction, the second or subsequent read of the transaction shows a row that does not exist in the original read. Magic reading should also be considered as a non-repeatable read phenomenon, but it is only relative to the insert and delete operations, and the above non-repeatable read phenomenon but focus on the update operation. The reason for this is that the new row for insert is not version information, and it is determined by a range.

Dirty Read Demo

// Set READ UNCOMMITTED (Read UNCOMMITTED content)set autocommit=0// turn off auto-commit

Client 1

Client 2

It is clear from the above two graphs that when tx_isolation=readuncommitted, when a transaction (transaction 1) modifies the data but does not commit it, the other transaction (transaction 2) is able to access the modified data. At this point, if the previous transaction cancels the previous modification (rollback), then the data obtained by transaction 2 is a "dirty" data.

Let's take a look at read Committed If this is the case.

Client 1

Client 2

As you can see in Figure 3, the Client 2 update (but not the commit), when client 1 reads the previous data, when client 2 is a real commit, client 1 can read the updated data. In addition, as shown in Figure 3, we can also see another phenomenon, non-repeatable read, that is, within a transaction (client 1) two reads inconsistent data.

Let's take a look at the repeatable read level for this behavior.

Client 1

Client 2

As you can see in Figure 5, even after client 2commit but client 1 has not yet been commit, the result of accessing the same record is the same at any time within that transaction. Therefore, repeatable read does not have a non-repeatable reading problem.

Phantom reading: In fact, it is almost the same as non-repeatable reading, but Phantom reading refers to the number of rows (a range), and non-repeatable reads are relative to a record. If the number of records for the first read of this range is 10 in transaction 1, but another transaction deletes a record in the range, this causes the re-read of transaction 2 to be inconsistent with the result of the first read. The InnoDB solves this problem by mcvv+ clearance lock. Therefore, there is no phantom reading for InnoDB on the repeatable read level.

Current Serializable serialization does not show the above problem

Client 1

Client 2

InnoDB Transaction ISOLATION LEVEL