Transaction isolation level

Time:2021-4-19

Transaction isolation level

What is a business?

A transaction is a logical set of operations, either executed or not executed.

The most classic example of transaction is transfer. If Xiaoming wants to transfer 1000 yuan to Xiaohong, the transfer will involve two key operations: reducing the balance of Xiaoming by 1000 yuan and increasing the balance of Xiaohong by 1000 yuan. In case of a sudden error between the two operations, such as the collapse of the banking system, the balance of Xiaoming will decrease while that of Xiaohong will not increase, it will be wrong. Transaction is to ensure that these two key operations either succeed or fail.

Characteristics of transactions (acid)

Transaction isolation level

  1. Atomicity:Transaction is the smallest execution unit, and segmentation is not allowed. The atomicity of transactions ensures that the actions are either complete or not functional at all;
  2. uniformity:Before and after the transaction is executed, the data should be consistent. For example, in the transfer business, the total amount of the transferor and the payee should remain unchanged regardless of whether the transaction is successful or not;
  3. Isolation:When accessing the database concurrently, a user’s transaction is not interfered by other transactions, and the database is independent among the concurrent transactions;
  4. persistence:After a transaction is committed. The change of the data in the database is persistent, even if the database fails, it should not have any impact on it.

Problems caused by concurrent transactions

In a typical application, multiple transactions run concurrently, often operating the same data to complete their own tasks (multiple users operate on the unified data). Concurrency is necessary, but it may lead to the following problems.

  • Dirty read:When a transaction is accessing data and modifying the data, but the modification has not been committed to the database, another transaction also accesses the data and uses the data. Because this data is uncommitted data, the data read by another transaction is “dirty data”. The operation based on “dirty data” may be incorrect.
  • Lost to modify:When a transaction reads a data, another transaction also accesses the data. After modifying the data in the first transaction, the second transaction also modifies the data. In this way, the modification result in the first transaction is lost, so it is called lost modification. For example: transaction 1 reads data in a table, a = 20, transaction 2 also reads a = 20, transaction 1 modifies a = A-1, transaction 2 also modifies a = A-1, the final result is a = 19, and the modification of transaction 1 is lost.
  • Unrepeatable read:Read the same data multiple times in a transaction. Before the end of this transaction, another transaction also accesses the data. Then, between the two reads of the first transaction, the data read by the first transaction may be different due to the modification of the second transaction. This occurs when the data read twice in a transaction is not the same, so it is called non repeatable read.
  • Phantom read:Unreal reading is similar to nonrepeatable reading. It occurs when one transaction (T1) reads several rows of data, and then another concurrent transaction (T2) inserts some data. In the subsequent query, the first transaction (T1) will find some records that do not exist, just like an illusion, so it is called phantom reading.

The difference between nonrepeatability and unreal reading:

The key point of non repeatable reading is to modify, and the key point of magic reading is to add or delete.

Example 1 (under the same conditions, you read the data and find that the value is different again): before Mr. A in transaction 1 reads his own salary as 1000, Mr. B in transaction 2 modifies a’s salary as 2000, so that when a reads his own salary again, his salary becomes 2000. This is not repeatable.

Example 2 (under the same conditions, the number of records read out for the first time and the second time is different): assuming that there are four people whose wages are greater than 3000 in a certain payroll table, transaction 1 reads all the people whose wages are greater than 3000 and finds a total of four records. At this time, transaction 2 inserts another record whose wages are greater than 3000. When transaction 1 reads again, the number of records found becomes five, which leads to unreal reading.

Transaction isolation level

The SQL standard defines four isolation levels:

  • Read-uncommitted:The lowest level of isolation allows reading uncommitted data changes,It may lead to dirty reading, unreal reading or non repeatable reading
  • Read committed:Allows reading of committed data of concurrent transactions,Dirty reading can be prevented, but phantom reading or non repeatable reading can still occur
  • Repeatable-read:The results of multiple reads of the same field are consistent, unless the data is modified by the transaction itself,It can prevent dirty reading and non repeatable reading, but phantom reading can still occur
  • Serializable:The highest isolation level, fully subject to the isolation level of acid. All transactions are executed one by one, so that there is no interference between transactions,This level can prevent dirty read, non repeatable read and unreal read
    • *
Isolation level Dirty reading Not repeatable Phantom reading
READ-UNCOMMITTED
READ-COMMITTED ×
REPEATABLE-READ × ×
SERIALIZABLE × × ×

The default isolation level supported by MySQL InnoDB storage engine isRepeatable-read. We can go through itSELECT @@tx_isolation;Command to view MySQL 8.0, which is changed toSELECT @@transaction_isolation;

mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

It should be noted that the difference from SQL standard is that InnoDB storage engineRepeatable-readUnder the transaction isolation level, the application is allowed to use the next key lock algorithm to avoid the generation of unreal reading. This is different from other database systems (such as SQL Server). So although the default isolation level supported by InnoDB storage engine isRepeatable-read, but can be read by applying a lock (e.gselect * from table for updateStatement) to ensure that magic reading will not occur, and the mechanism used for this locking degree is the next key lock algorithm. So as to achieve the SQL standardSerializable (serializable)Isolation level.

Because the lower the isolation level, the less locks the transaction requests, so the isolation level of most database systems is the sameRead committed:But you need to know that InnoDB storage engine is used by defaultRepeatable-readThere is no performance loss.

InnoDB storage engine inDistributed transactionIt’s usually used in the case ofSerializable (serializable)Isolation level.

Demonstration of actual situation

Next, I will use two command lines Mysql to simulate the dirty reading of the same data by multithreading (multi transaction).

In the default configuration of MySQL command line, transactions are automatically committed, that is, commit operation will be executed immediately after executing SQL statement. If you want to open a transaction explicitly, you need to use the following command:START TARNSACTION

We can set the isolation level with the following command.

SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE]

Let’s take a look at some of the concurrency control statements we use in the following operations:

  • START TARNSACTION |BEGIN: open a transaction explicitly.
  • COMMIT: commit transactions to make all changes to the database permanent.
  • ROLLBACK: rollback ends the user’s transaction and undoes all uncommitted changes in progress.

Dirty read (read uncommitted)

Transaction isolation level

Avoid dirty reading (read committed)

Transaction isolation level

Not repeatable

Just now, the read submitted graph avoids the problem of uncommitted read. However, the problem of non repeatable read occurs before the end of a transaction.
Transaction isolation level

Repeatable reading

Transaction isolation level

Prevent unreal reading (repeatable)

Transaction isolation level

A transaction operates on the database. The scope of this operation is all the rows of the database. Then the second transaction also operates on the database. This operation can be to insert a row of records or delete a row of records. Then the first transaction will feel hallucination. How can there be any records that have not been processed? Or how can one more row of records be processed?

There are some similarities between unreadable reading and non repeatable reading, but the focus of non repeatable reading is modification, and the focus of unreadable reading is addition or deletion.