Concurrency and Deadlocking in SQL Server ~ Vinay's Blog

Nov 4, 2009

Concurrency and Deadlocking in SQL Server

Concurrency:

Concurrency is an ability that allows multiple users to access the same data at the same time.

4 Concurrency Problems:

Lost updates
Dirty reads
Non-repeatable reads
Phantom reads

Isolation Levels:

SQL Server 2005 uses locks to control how the data can be accessed and changed by multiple users at the same time without conflicting with each other.

5 Isolation Levels:

READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SNAPSHOT
SERIALIZABLE

Syntax for setting isolation level:

SET TRANSACTION ISOLATION LEVEL

{READ UNCOMMITTED|READ COMMITTED|

REPEATABLE READ|SNAPSHOT|SERAILIZABLE}

Which concurrency problem is prevented?

Isolation level	Dirty reads	Lost updates	Non-repeatable reads	Phantom reads
Read Uncommitted	No	No	No	No
Read Committed	Prevents	No	No	No
Repeatable Read	Prevents	Prevents	Prevents	No
Snapshot	Prevents	Prevents	Prevents	Prevents
Serializable	Prevents	Prevents	Prevents	Prevents

SERIALIZABLE isolation level:

• SERIALIZABLE locks every resource a process uses; other processes can’t work on the same data until the first transaction is completed.

• More server overhead to manage the locks.

• Deadlocks can happen.

SNAPSHOT isolation level:

First introduced in SQL Server 2005

Uses row versioning feature that stores the original version of a row in TempDB when a row is modified.
The process works with the version in TempDB, not with the original data.
Eliminates locking the data and significantly reduces the likelihood of deadlocks.
If a SNAPSHOT transaction tries to change the data that has been changed by another process since the SNAPSHOT transaction began, the process will raise an error and roll back the transaction.
Avoid it by using UPDLOCK for SELECT statements that its results will be used in subsequent update.

Deadlocking:

Happens at REPEATABLE READ and SERIALIZABLE
A deadlock occurs when two or more tasks are blocking each other from accessing the same resources.
Deadlocks are automatically caught by SQL Server Database Engine. Once detected, the Database Engine chooses one of the transactions as the deadlock victim and lets the other transaction complete its process.
Default search interval is 5 seconds.

Deadlock Error:

(1 row(s) affected)

Msg 1205, Level 13, State 51, Line 7

Transaction (Process ID 54) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Deadlock Tools:

1) Trace Flag 1204 and Trace Flag 1222

The flags can be set in the Startup Parameters for the instance of SQL Server in SQL Server Services

2) Deadlock Graph Event

SQL Server Profiler

Deadlock Graph:

Minimizing Deadlocks:

Because the transaction of the deadlock victim is terminated by SQL Server, applications should trap error message 1205 and resubmit the query again automatically.
Access and update data in the same order in each transaction.
Keep transactions short.
Use snapshot isolation.

References

SQL Server Books Online and Microsoft Technet.

Nov 4, 2009