Understanding SQL Server Transaction Isolation levels (Transaction isolation level)

About the SQL Server transaction isolation level, the Baidu Encyclopedia is described in this way

Isolation level: The degree to which a transaction must be isolated from a resource or data change made by another transaction. The isolation level is described from the perspective of the allowed concurrency side effects (for example, dirty reads or virtual reads).

There are 5 levels of isolation:

READ UNCOMMITTED | 0 UNCOMMITTED Read
Read Committed | 1 Read Committed
REPEATABLE Read | 2 REPEATABLE Read
Serializable | 3 serializable
Snapshot snapshot (added after version 2005)

In the following diagram, for example, the TABLE1 is read in transaction a according to the condition, and read two times, transaction B will update the same table TABLE1 the same conditions, and when the table data is updated, an exclusive lock is obtained until the transaction commits.

This can have different effects if you set different isolation levels in transaction a.

1, the isolation level is set to read UNCOMMITTED, allowing dirty reads, regardless of when transaction B commits, the SELECT statement 2 in transaction A can read the data (possibly dirty data, because transaction B may be rollback), and the data is different from statement 1.

2, the isolation level is set to read committed, does not allow dirty reads, but allows "non-repeatable read", that is, transaction a can be read more than once, regardless of whether a commit in transaction B, if the SELECT statement 3 executes when transaction B does not have a commit, the read result is the same as SELECT statement 1, The reverse is different.

3, the isolation level is set to repeatable read, does not allow dirty reads, and does not allow "non-repeatable read", but allows "phantom read", in the example, the execution of SELECT statement 3 in transaction a must wait until transaction B commits to execute successfully.

4, the isolation level is set to serializable, this is the highest level of isolation, serialization read, transaction can only one one execution, avoid dirty read, non-repeatable read, Phantom read.

5. When the isolation level is set to snapshot and the data is read, the line read by the read operation is guaranteed to be the last committed version available at the start of the transaction. This means that this isolation level guarantees that the data that has been submitted is read and repeatable read can be achieved, as well as to ensure that no phantom reading is possible.

Understanding SQL Server Transaction Isolation levels (Transaction isolation level)