Hunting Deadlocks - SQL Server 2000

One of a series of posts from andraszek.net posted originally between 2006 and 2010. 

A typical scenario for a deadlock is described in Books Online: two procedures which try to use two tables in a different order, block each other after aquiring a lock to the first table.

I will describe here a different scenario: one procedure using one table. How can a deadlock occurr in such a case? Let's see.

It all starts with SQL Error 1205: Your transaction (process ID #99) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun your transaction.

First, let's enable SQL Server Trace 1204 flag. You must be in sysadmin server role to execute the following statement:

DBCC TRACEON (1204)

The trace, by default is written to this file:
C:\Program Files\Microsoft SQL Server\MSSQL\LOG\ERRORLOG

Here we find details of our deadlock:

Deadlock encountered .... Printing deadlock information
2004-10-15 19:14:04.64 spid4
2004-10-15 19:14:04.64 spid4 Wait-for graph
2004-10-15 19:14:04.64 spid4
2004-10-15 19:14:04.64 spid4 Node:1
2004-10-15 19:14:04.64 spid4 KEY: 10:1266375687:3 (dd0025eb9a53) CleanCnt:1 
Mode: S Flags: 0x0
2004-10-15 19:14:04.64 spid4 Grant List 1::
2004-10-15 19:14:04.64 spid4 Owner:0x655ac660 Mode: S Flg:0x0 Ref:1 
Life:00000000 SPID:75 ECID:0
2004-10-15 19:14:04.64 spid4 SPID: 75 ECID: 0 Statement Type: SELECT Line #: 29
2004-10-15 19:14:04.64 spid4 Input Buf: RPC Event: InvoiceUpdate;1
2004-10-15 19:14:04.64 spid4 Requested By:
2004-10-15 19:14:04.64 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:64 
ECID:0 Ec:(0x63B3F580) Value:0x57648ee0 Cost:(0/3C)
2004-10-15 19:14:04.64 spid4
2004-10-15 19:14:04.64 spid4 Node:2
2004-10-15 19:14:04.64 spid4 KEY: 16:1153035489:1 (d1003ffe1113) CleanCnt:1 
Mode: X Flags: 0x0
2004-10-15 19:14:04.64 spid4 Grant List 0::
2004-10-15 19:14:04.64 spid4 Owner:0x42bc2140 Mode: X Flg:0x0 Ref:0 
Life:02000000 SPID:64 ECID:0
2004-10-15 19:14:04.64 spid4 SPID: 64 ECID: 0 Statement Type: UPDATE Line #: 
679
2004-10-15 19:14:04.64 spid4 Input Buf: RPC Event: InvoiceUpdate;1
2004-10-15 19:14:04.64 spid4 Requested By:
2004-10-15 19:14:04.64 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:75 
ECID:0 Ec:(0x61D09580) Value:0x655ad200 Cost:(0/0)
2004-10-15 19:14:04.64 spid4 Victim Resource Owner:
2004-10-15 19:14:04.64 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:75 
ECID:0 Ec:(0x61D09580) Value:0x655ad200 Cost:(0/0)
			

You can notice that both nodes executed procedure InvoiceUpdate. From the KEY you can decipher the object they fought for:
10 is database id,
1266375687 is table id,
and 1 or 3 are index ids.

Find name of the database:

SELECT [name] FROM master.dbo.sysdatabases WHERE dbid = 10

Find name of the table:

SELECT OBJECT_NAME('1266375687')

This may be a temporary object, for example a cursor, which will not exist when we check later. In this case find name of the table from the stored procedure: just look for line # provided by trace.

There is a little trap here: if your procedure calls another procedure, then the deadlock may actually occurr in the subprocedure and the line number applies to the subprocedure.

Find ids and names of the indexes:

SELECT indid, [name] FROM sysindexes WHERE id = OBJECT_ID('Invoice')

Find keys of the indexes:

sp_helpindex 'Invoice'

Both instances aquire a shared lock to a range of rows, and then one of them tries to escalate that lock to exclusive to update a row, but cannot, because the other keeps it and also tries to escalate to exclusive to update another row.

The problem is that the initial shared lock was too wide. The index used was not optimal for the SELECT statement and too many rows were locked.

The solution is to create another index which matches exactly the WHERE clause used in the SELECT. This of course does not solve the problem if there are two or more processes that try to execute this procedure with the same parameters. The application has to be designed in a way that processes operate on different sets of data.