SQLServer deadlock

Question

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>

Answer 1

Welcome to awful.

The last time I ran into a situation like this it was because an update or a delete was unable to find a good index to help it isolate the rows it was affecting. This caused an erratic lock escalation as it was using a non-covering index to modify locate the records.

So if you can isolate some of the queries, check the sql for them and see if you cannot experiment with providing some covering indexes.

A covering index is an index that includes all the fields in the specific where clause.

Answer 2

deadlocks on SQLServer almost always originate from the fact that a single thread tries to write and read using two connections and therefore two transactions. If you want to make this work, do all operations in a single thread using ONE connection and make absolutely sure you're indeed re-using the connection. It might be due to the layering in your application you're accidently using a different connection for reading during a transaction which makes that read wait for the other code (using another connection) to complete which never happens as the read never finishes.

Example (pseudo steps)

start trans

• write data (e.g. INSERT, UPDATE) to table Foo
• read some data (which triggers table scan) from Foo using different connection DEADLOCK, as read never finishes, so transactio is never committed

Answer 3

First do not use hints, usually SQL Server is better left on his own.

Secondo verify that you do not have a REAL deadlock (but it happens much less often of the deadlock appearance can hint).

Third, as someone suggested, check if you've some slow query and tune it.

In my experience every single time a customer has reported a deadlock situation this happened because there was a slow running query that escalates and never a real deadlock and tuning the query or adding a specific index always solved the problem.

I do not see which version of SQL Server you're talking about but each following version has a better deadlock management than the previous one and SQL Server 2000 was particularly nasty in this topic.

Regards
Massimo