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.
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.
Collapse | Copy Code
UPDATE Customer SET LastName = 'John' WHERE CustomerId=111
WAITFOR DELAY '00:00:05'