Database transaction isolation level

Every programmer knows this for a long time, but I have not really figured it out. Many articles on the Internet are not clear. I personally think it is better to understand the following content:

 

Transaction isolation level (Transaction isolation
Levels
): The isolation level is the level of control over transaction concurrency. ANSI
/

ISO
SQL
Serializable and repeatable
Read), read committed, Read committed
Uncommited) four levels. To achieve the isolation level, the database usually uses a lock ). Generally, you only need to set the isolation level during programming. The database will set the specific lock used. First, we will introduce four levels, and then give an example to explain the concurrency problems that may occur in the next three levels (Repeatable read, read committed, read uncommitted.

Serializable: all transactions are executed serially one by one to avoid phantom
Reads ). For databases that implement Concurrency Control Based on locks, serialization requires that the range lock (range
Lock ). If it is not a database that implements Concurrency Control Based on locks, you need to roll back the transaction when checking that there is a transaction that violates the serial operation.

Repeatable
Read): All data obtained by the SELECT statement cannot be modified. This prevents data inconsistency before and after a transaction. However, there is no way to control Phantom reads, because at this time other transactions cannot change the selected data, but data can be added because the previous transaction has no range lock.

Read committed (read
Commited): The read data can be modified by other transactions. This may cause non-repeated reading. That is to say, the read lock is obtained when the transaction reads data, but is released immediately after reading the data (you do not need to wait until the transaction ends), and the write lock is released after the transaction is committed. After the read lock is released, data may be modified by other things. This level is also SQL
The default isolation level of the server.

Read uncommitted: This is the lowest isolation level, allowing other transactions to see uncommitted data. This level causes dirty read (dirty
Read ).

 

To sum up the above, you can wait for the following table:

 

Isolation level	Dirty read	Non-repeated read	Phantom read
Read not submitted	Yes	Yes	Yes
Read committed	No	Yes	Yes
Repeatable read	No	No	Yes
Serializing	No	No	No

 

Note: The transaction isolation level is defined based on whether the data is "correct" when a transaction is queried (select, may be one or multiple times. (Understanding)

Supplement:

1. Dirty reading: Transaction 1 reads data that transaction 2 has not submitted (If transaction 2 fails to roll back, the data read by transaction 1 is invalid, or garbage)

Generation level: read not submitted

Resolution requirement level: Read committed

 

2. Repeatable reading: when a transaction reads data twice or multiple times, it reads different data. The reason is that transaction 2 modifies the data in the middle.

Generation level: Read committed

Resolution requirement level: Repeatable read

3. Phantom read: When things modify part of the data, things 2 adds new data that meets things 1's conditions. It is called an illusion that new data has not been modified at the beginning of a transaction.

Generation level: Repeatable read

Solution level: serialize

 

Default isolation levels of major databases:

ORACLE: Read committed

SQL Server: Read committed

MySQL :( InnoDB) Repeatable read