SQL Server Deadlocks

                                                                   SQL Server Locks - Part I, Part II, Part III, Part IV

Deadlocks
In previous post we discussed locking mechanism in SQL Server and what are different types of locks and how you can control default locking mechanism by using various hints with your queries.

Today, we will discuss deadlocks. Locking is a necessity for any DBMS to work in a multi-user environment and to maintain data consistency. Deadlocks occur when two transactions are waiting on each other to release the lock. Eventually SQL Server chooses one of the transaction as victim and kills it.

Let's explain this using an example -

Suppose you have a Customers table and you run the following queries in two separate transactions.

Transaction A
--statement 1
SELECT * FROM Customers WHERE Customer ID = 1
--statement 2
UPDATE Customers SET EmailAddress='123@mail.com' WHERE CustomerID = 2

Transaction B
--statement 1
SELECT * FROM Customers WHERE Customer ID = 2
--statement 2
UPDATE Customers SET EmailAddress='123@mail.com' WHERE CustomerID = 1

Let's assume one user calls a routine that executes transaction A and another use calls a routine that executes transaction B concurrently.

Transaction A will issue a shared lock while executing statement 1 on row 1 and transaction 2 will issue a shared lock while executing statement 1 on row 2.

Now  transaction  A will request an exclusive lock on row 2, while transaction B will request an exclusive lock on row 1.

Transaction A cannot complete because it is waiting on transaction B while transaction B cannot complete because it waiting on transaction A.

This condition is also called cyclic dependency. SQL Server engine deadlock monitor periodically checks for cyclic dependency and when it detects a dependency, it chooses one of the transaction as a victim and terminates it. The transaction chosen as a deadlock victim will terminate but the other transaction will complete successfully.

Remember, deadlocking is different than blocking. Blocking occurs when a transaction requests a lock on a resource which is exclusively locked by another transaction, in that case the requesting transaction will wait for the lock to be released.

Minimizing Deadlocks
Deadlocks are a fact of life, but you can minimize them by following some basic steps...

• Objects should be accessed in the same order
• All concurrent transactions should access the objects in the same order. For example, If there are two tables - Orders and Order Details, all concurrent transactions should request locks in the same order - For example first on Orders table and then on Order Details table. If all lock requests flow in the same direction, the subsequent requests will be blocked until a lock is released from the previous request, but deadlock should not occur.
• Avoid prompting for User input when a transaction is running
• A transaction should be completed as fast as possible so that the locks are acquired and released as quickly as possible. Requiring user input delays the transaction, hence resources will be locked for longer period of time.
• Keep Transactions Short and in one batch
• Keeping transactions short and in one batch will minimize resources, resulting in faster transaction completion.
• Use a lower Isolation Level
• We will discuss isolation levels in more detail in subsequent post but lower the isolation level, less locking. For example if you implement read committed isolation level, it allows a transaction to read data previously read by another transaction without waiting for that transaction to complete.
• Set READ_COMMITTED_SNAPSHOT to ON
• When this database option is ON, a transaction that is running under read committed isolation level uses row versioning instead of shared lock during read operations. Row versioning does not acquire shared lock for read operations. You must also set ALLOW_SNAPSHOT_ISOLATION to ON in addition to read committed snapshot for it to work.
• Use bound connections
• If your application opens two or more connections, you can bound them together such that one is a primary connection while other is a secondary connection. Under this scenario, any locks that are acquired by the primary connection are treated as they are also acquired by the secondary connection and vice versa.

This concludes our topic for today. There are some other aspects of locking such as providing locking hints with your queries or setting up different isolation levels. We will discuss them in subsequent posts.

Thank you.