SQL Server 2000 - Debugging Deadlocks

Question

I'm looking for suggestions on how to debug and chase down deadlock issues in an SQL Server 2000 database. I've had it recommended to me to use trace flags 1024 and 3605, which I have found give me the following:

1024 - this trace flag returns the type of locks participating in the deadlock and the current command affected.

3605 - this trace flag sends trace output to the error log.

The specific stored procedures, tables and indexes still need to be uncovered, so the goal is to use these trace flags to do so. And then from there I'll know better what indexes need tuning, locking hints for tuning queries, and potential sproc bugs to fix.

Any other suggestions or happy ending war stories on chasing down this all to common problem?

Answer 1

Once you know the sql statements involved, a code analysis will surely help. Some thumb rules are checking for the order in which the rows are accessed and checking for the isolation level used for the SQL statements. A profiler trace can help a lot.

Most of the time, it is because of a reader trying to get a shared lock on a resource locked exclusively by a writer.

Answer 2

Here is the bible on deadlock troubleshooting: http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx

Answer 3

Very useful script for analyzing deadlocks: http://www.sommarskog.se/sqlutil/aba_lockinfo.html