Reputation: 984
Can someone tell me why the following SQL Server queries is deadlocking and what is the solution to fixing it?
<deadlock-list>
<deadlock victim="process88b5b8">
<process-list>
<process id="process88b5b8" taskpriority="0" logused="76132" waitresource="RID: 32:1:151867:174" waittime="5093" ownerId="65554098" transactionguid="0xedf3314c05f1124cbe8d480cd092e03e" transactionname="DTCXact" lasttranstarted="2011-09-02T19:00:29.690" XDES="0x1029e040" lockMode="S" schedulerid="1" kpid="5108" status="suspended" spid="118" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2011-09-02T19:00:31.317" lastbatchcompleted="2011-09-02T19:00:31.300" hostname="MELWFPL382S" hostpid="0" loginname="MM4" isolationlevel="repeatable read (3)" xactid="65554098" currentdb="32" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="78" sqlhandle="0x020000004b4b0a0d63e1040095143cbaa0174ffc3e076067"> delete from PARTIES where PARTYEXTERNALREF=@P0 and ISCOUNTERPARTY='N' and PARTYID in (select PARTYID from NAB_PARTY_EXTEND (nolock) where PARTYTYPE=@P1) </frame>
<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">unknown </frame>
</executionStack>
<inputbuf>(@P0 nvarchar(4000),@P1 nvarchar(4000))delete from PARTIES where PARTYEXTERNALREF=@P0 and ISCOUNTERPARTY='N' and PARTYID in (select PARTYID from NAB_PARTY_EXTEND (nolock) where PARTYTYPE=@P1) </inputbuf>
</process>
<process id="process9196a8" taskpriority="0" logused="132612" waitresource="RID: 32:1:140302:31" waittime="5046" ownerId="65554657" transactionguid="0x7313c78fecc8914dac3ed821cd7c21fe" transactionname="DTCXact" lasttranstarted="2011-09-02T19:00:34.100" XDES="0x12835778" lockMode="S" schedulerid="2" kpid="3692" status="suspended" spid="94" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2011-09-02T19:00:35.690" lastbatchcompleted="2011-09-02T19:00:35.687" hostname="MELWFPL382S" hostpid="0" loginname="MM4" isolationlevel="repeatable read (3)" xactid="65554657" currentdb="32" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="78" sqlhandle="0x020000004b4b0a0d63e1040095143cbaa0174ffc3e076067">delete from PARTIES where PARTYEXTERNALREF=@P0 and ISCOUNTERPARTY='N' and PARTYID in (select PARTYID from NAB_PARTY_EXTEND (nolock) where PARTYTYPE=@P1) </frame>
<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">unknown </frame>
</executionStack>
<inputbuf>(@P0 nvarchar(4000),@P1 nvarchar(4000))delete from PARTIES where PARTYEXTERNALREF=@P0 and ISCOUNTERPARTY='N' and PARTYID in (select PARTYID from NAB_PARTY_EXTEND (nolock) where PARTYTYPE=@P1) </inputbuf>
</process>
</process-list>
<resource-list>
<ridlock fileid="1" pageid="140302" dbid="32" objectname="mm4_melwfpl382s.dbo.COUNTERPARTYSSI" id="lock170fa500" mode="X" associatedObjectId="72057595803336704">
<owner-list>
<owner id="process88b5b8" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process9196a8" mode="S" requestType="wait" />
</waiter-list>
</ridlock>
<ridlock fileid="1" pageid="151867" dbid="32" objectname="mm4_melwfpl382s.dbo.COUNTERPARTYSSI" id="lock20e65d80" mode="X" associatedObjectId="72057595803336704">
<owner-list>
<owner id="process9196a8" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process88b5b8" mode="S" requestType="wait" />
</waiter-list>
</ridlock>
</resource-list>
</deadlock>
</deadlock-list>
What I don't understand is how two process can have an exclusive lock on the same object.
There is an index (IDX_NC_PARTIES_PARTYEXTERNALREF_ISCOUNTERPARTY_PARTYID) on the PARTIES table and the database is set to read committed snapshot.
Thanks,
Wayne.
Upvotes: 1
Views: 2536
Reputation: 294487
isolationlevel="repeatable read (3)"
So the deadlock occur on the base heap of the table (RID locks instead of key locks implies a heap not a Btree). The high isolation level (likely caused by DTC, judging from the xact name) makes the RCSI setting irrelevant.
What type are the columns PARTYEXTERNALREF and PARTYTYPE? The parameters passed in are NVARCHAR (ie. Unicode) and if the columns are VARCHAR (ie. Ascii) then due to the rules of data type precedence the NC index would not be used. Because of the table scan involved, together with of the high isolation level in use, a deadlock is almost unavoidable.
The solution would be to use VARCHAR type parameters for @P0 and @P1 so the NC index would be leveraged to avoid the table scan.
If the parameters are already of type VARCHAR and you can confirm from the execution plan that a seek on the NC is used, the my first question would be what else is the transaction doing, other than the delete statements?
BTW, you only give the name of the NC index but I assume is on (PARTYEXTERNALREF, ISCOUNTERPARTY, PARTYID)
.
Update
Since your comment say that the columns are NVARCHAR then the tables scan hypothesis is probably wrong. There are three more possibilities to cause a deadlock that need investigation:
For the first two hypotheses only you can do anything right now (investigate if they are correct). For the last one I can tell you how to verify it, but is not trivial. It is unlikely to happen and a bit difficult to prove, but it is possible. Since you know of as deadlock case (the attached XML), use it as investigation base:
DBCC TRACEON(3604,-1)
DBCC PAGE (<restored db id>, 1, 151867, 3)
inspect the values in slot 174SELECT %%lockres%% FROM PARTIES WHERE PARTYEXTERNALREF = ... AND ISCOUNTERPARTY='N' and PARTYID=...
and pass in the values read aboveUpvotes: 9