SQL SERVER – How To Handle Deadlock: Nice Example

SQL SERVER – How To Handle Deadlock

By Nitin Chilka | 22 Sep 2009

SQL2005SQLWindowsSQL-ServerDBADevIntermediate

An article on how to handle deadlock

Introduction

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

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s