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