Transaction isolation level, lock mechanism in SQL Server

Transaction

Function: Used to perform a series of actions, and to ensure that all actions are either executed or not executed.

Properties: Atomic row, consistency, isolation, persistence

Lock

Role: SQL Server uses locks to enforce transaction isolation properties.

Blocking

Definition: If one transaction holds a lock on a data resource and another transaction requests an incompatible lock of the same resource, the request for the new lock is blocked.

By default, blocked requests wait until the original transaction releases the associated lock.

Isolation level of a transaction

There are 6 possible settings (higher and lower isolation levels):

1. READ UNCOMMITTED

2. Read Committed

3. Repeatable READ

4, Serializable

5, Snapshot

6. Read Committed Snapshot

of which the first four levels are most commonly used

1, read UNCOMMITTED dirty read

For example, a is executing the following statement:

begin TranInsertTbunreadSelect 3,'Zhang San'UnionSelect 4,'John Doe'---delay seconds to simulate real-world trading scenarios for processing business logicwaitforDelay'00:00:05'rollback Tran

At this point, within 5 seconds of the wait, B has made a change to the resource:

Set Transaction Isolation  Level Read Uncommitted -- Querying Data Select *  from where  like ' Zhang% '

Originally a to insert two data, but the last transaction rollback, at this time the database should not exist in this two data, but B is read, this is dirty read.

To solve this problem, you need to elevate the transaction isolation level to read Committed

2, Read committed non-repeatable reading (avoid dirty read the lowest level)

If a is performing the following actions:

Set Transaction Isolation  Level Read committedbegin TranSelect *  fromTbunreadwhereId=2 ---delay seconds to simulate real-world trading scenarios for processing business logicwaitforDelay'00:00:05'Select *  fromTbunreadwhereId=2

At this point, within 5 seconds of the wait, B has made a change to the resource:

Update Tbunread Set name='jack_upd'where ID=2

The result is that within the same transaction, a query with the same two-time condition has different results.

To solve this problem, you need to elevate the transaction isolation level to repeatable READ

3, REPEATABLE read Phantom reading

If a is performing the following actions:

Set Transaction Isolation  Level Repeatable READbegin TranSelect *  fromTbunreadwhereId=3 ---delay seconds to simulate real-world trading scenarios for processing business logicwaitforDelay'00:00:05'Select *  fromTbunreadwhereId=3 Commit Tran

At this point, within 5 seconds of the wait, B has made a change to the resource:

Tbunread

Select 3,' Phantom reading '

At this point, a has set the isolation level to "repeatable read", although the data that has been read, whether it is a shared or exclusive lock to the end of the transaction, but can not prevent others to run the new operation, resulting in the first query without data, the second query has data, this phenomenon is called Phantom read.

To solve this problem, you need to elevate the transaction isolation level to Serializable

Transaction isolation level, lock mechanism in SQL Server