Hi,
I have a java application which is doing multiple concurrent CRUD operations on a database. I'm adding support for SQLServer but am having problems with deadlocking during concurrent deletes. After some investigation it appeared that the problem may be due to lock escalation on a particular table.
In an attempt to fix it, I decided to make all reads on the table in question be done "for update" using the UPDLOCK hint so that the deadlock could be avoided. However, I'm still seeing the problem. I've enabled tracing in SQLServer and have found the following deadlock trace in the SQLServer logs:
Deadlock encountered .... Printing deadlock information Wait-for graph
Node:1 KEY: 5:72057594042384384 (54048e7b3828) CleanCnt:3 Mode:X Flags: 0x0 Grant List 1: Owner:0x03D08C40 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:62 ECID:0 XactLockInfo: 0x04834274 SPID: 62 ECID: 0 Statement Type: DELETE Line #: 1 Input Buf: Language Event: (@P0 nvarchar(4000))delete from part_data where part_id = @P0 Requested By: ResType:LockOwner Stype:'OR'Xdes:0x04B511C8 Mode: U SPID:60 BatchID:0 ECID:0 TaskProxy:(0x058BE378) Value:0x3d08500 Cost:(0/1296)
Node:2
KEY: 5:72057594042384384 (f903d6d6e0ac) CleanCnt:2 Mode:X Flags: 0x0 Grant List 0: Owner:0x03D088A0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:60 ECID:0 XactLockInfo: 0x04B511EC SPID: 60 ECID: 0 Statement Type: DELETE Line #: 1 Input Buf: Language Event: (@P0 nvarchar(4000))delete from part_data where part_id = @P0 Requested By: ResType:LockOwner Stype:'OR'Xdes:0x04834250 Mode: U SPID:62 BatchID:0 ECID:0 TaskProxy:(0x047BA378) Value:0x3d089e0 Cost:(0/4588)
Victim Resource Owner: ResType:LockOwner Stype:'OR'Xdes:0x04B511C8 Mode: U SPID:60 BatchID:0 ECID:0 TaskProxy:(0x058BE378) Value:0x3d08500 Cost:(0/1296)
SQLServer profiler shows this as two clients holding update (U) locks and attempting to escalate to exclusive (X) locks. The SQLServer docs I have read say that only one client can have a (U) lock on a table at a given time, so I'm wondering why I'm seeing the situation shown in the trace.
The database object refered to in that trace is an index on a foreign key. If anyone with experience of fixing this sort of problem could offer advice it would be a big help.
Thanks, Brad.
EDIT added deadlock graph xml as requested:
<deadlock-list>
<deadlock victim="process989018">
<process-list>
<process id="process6aa7a8" taskpriority="0" logused="4844" waitresource="KEY: 5:72057594042384384 (5504bdfb7529)" waittime="9859" ownerId="613553" transactionname="implicit_transaction" lasttranstarted="2009-05-08T11:52:39.137" XDES="0x5fcbc30" lockMode="U" schedulerid="1" kpid="3516" status="suspended" spid="59" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2009-05-08T11:52:39.183" lastbatchcompleted="2009-05-08T11:52:39.183" clientapp="jTDS" hostname="LOIRE" hostpid="123" loginname="sa" isolationlevel="read committed (2)" xactid="613553" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="40" sqlhandle="0x0200000007c76c39efdd8317c6fa7b611b4fd958f05cfcf4">
delete from part_data where part_id = @P0 </frame>
</executionStack>
<inputbuf>(@P0 nvarchar(4000))delete from part_data where part_id = @P0</inputbuf>
</process>
<process id="process989018" taskpriority="0" logused="1528" waitresource="KEY: 5:72057594042384384 (5e0405cb0377)" waittime="1250" ownerId="613558" transactionname="implicit_transaction" lasttranstarted="2009-05-08T11:52:39.183" XDES="0x48318f0" lockMode="U" schedulerid="2" kpid="2692" status="suspended" spid="60" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2009-05-08T11:52:39.183" lastbatchcompleted="2009-05-08T11:52:39.183" clientapp="jTDS" hostname="LOIRE" hostpid="123" loginname="sa" isolationlevel="read committed (2)" xactid="613558" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="40" sqlhandle="0x0200000007c76c39efdd8317c6fa7b611b4fd958f05cfcf4">
delete from part_data where part_id = @P0 </frame>
</executionStack>
<inputbuf>(@P0 nvarchar(4000))delete from part_data where part_id = @P0</inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057594042384384" dbid="5" objectname="MESSAGESTOREDB61.dbo.part_data" indexname="idx_part_data_part_id" id="lock3cab740" mode="X" associatedObjectId="72057594042384384">
<owner-list>
<owner id="process6aa7a8" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="process989018" mode="U" requestType="wait"/>
</waiter-list>
</keylock>
<keylock hobtid="72057594042384384" dbid="5" objectname="MESSAGESTOREDB61.dbo.part_data" indexname="idx_part_data_part_id" id="lock3cad340" mode="X" associatedObjectId="72057594042384384">
<owner-list>
<owner id="process989018" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="process6aa7a8" mode="U" requestType="wait"/>
</waiter-list>
</keylock>
</resource-list>
</deadlock>
</deadlock-list>