SQL SERVER – How To Handle Deadlock: Nice Example

SQL SERVER – How To Handle Deadlock

By Nitin Chilka | 22 Sep 2009


An article on how to handle deadlock


A deadlock is a situation wherein two transactions wait for each other to give up their respective locks.

When this happens, the SQL Server ends the deadlock by automatically choosing one and aborting the process, allowing the other process to continue. The aborted transaction is rolled back and an error message is sent to the user of the aborted process. Generally, the transaction that requires the least amount of overhead to rollback is the transaction that is aborted.

This article will explain how to handle deadlocks in a user-friendly way.

The Deadlock

Transaction A attempts to update table 1 and subsequently read/update data from table 2, whereas transaction B attempts to update table 2 and subsequently read/update data from table 1. In such situations, transaction A holds locks that transaction B needs to complete its task and vice versa; neither transaction can complete until the other transaction releases locks.

The Deadlock Situation

The below example shows the deadlock situation between the two transactions.

Transaction A

Collapse | Copy Code

 UPDATE Customer SET LastName = 'John' WHERE CustomerId=111
 WAITFOR DELAY '00:00:05'