Deadlock on table

Question

Post reply

Deadlock on table

khushbu

SSCarpal Tunnel

Points: 4591
More actions
December 9, 2008 at 10:19 pm

#203897

Hi
I have a script which executes when user logs off from UI. The script deletes few rows from tables.
Many a time I have observed a deadlock situation in the error log file.
The table from which rows are deleted at the same time other rows may get added to that same table. But the row id would be different.
Isolation level is read committed.
Please help me so as to avoid deadlock situation.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

madhu-686862 SSChampion Points: 10707 More actions · Answer 1

madhu-686862

SSChampion

Points: 10707

December 9, 2008 at 11:30 pm

#910083

plz post your dead lock graph from the error log

John H Marsh SSChampion Points: 11671 More actions · Answer 2

Hello,

Please see the following Article on how to troubleshoot Deadlocks:

http://blogs.msdn.com/bartd/archive/2006/09/09/747119.aspx

Regards,

John Marsh

www.sql.lu
SQL Server Luxembourg User Group

sturner SSC-Insane Points: 22411 More actions · Answer 3

I would need to look at a lot more details of the queries, the business logic and the database design, but generally I can pass along some general information.

Basically, you want to look at how efficiently the table is designed and/or indexed for the type of queries most often run against it. If the indexing is so poor that queries (including updates) end up escalating to extent or even table locks you will end up with more blocking and even deadlock potential.

Generally avoid such wide tables, break tables up if you need to and link them with a key as needed. Efficient queries, proper indexing and well designed business logic is the key to high availability and minimizing deadlocks.

The probability of survival is inversely proportional to the angle of arrival.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 4

First as Madhu posted, get your deadlock graph and post that here. To do so, on SQL 2000 enable trace flag 1204:

DBCC TRACEON(1204,-1)

on SQL 2005 use 1222 instead:

DBCC TRACEON(1222,-1)

This will log the deadlock graph to your SQL errorlog. Then tell us some more about the table. Is it a heap or does it have a clustered index on it? I blogged a few times recently about understanding deadlocks in SQL:

http://jmkehayias.blogspot.com/2008/07/anatomy-of-deadlock.html

http://jmkehayias.blogspot.com/2008/11/anatomy-of-deadlock-part-deux.html

and I have another one coming soon once I finish the graphics for it. The first link above is 90% of the time, the issue I find people having with deadlocking where a clustered index exists. The second one is for heaps. Both are easy to solve by fixing your index strategies.

Other things to watch out for is accessing tables in different order in code, but if these are deadlocks on one table, look to have one of the above problems.