September 30, 2014 at 6:12 pm
Can someone please help me understand the deadlock graph below, it looks to me that X locks are being obtained by both sessions on the same object, which is a clustered index, and I cant understand how that is possible ?
<deadlock>
<victim-list>
<victimProcess id="process10c0681868" />
</victim-list>
<process-list>
<process id="process10c0681868" taskpriority="0" logused="3232" waitresource="KEY: 18:72057602246443008 (bed0c9e8f940)" waittime="1186" ownerId="7887029" transactionname="user_transaction" lasttranstarted="2014-09-30T15:49:34.253" XDES="0x127e7a6d28" lockMode="U" schedulerid="12" kpid="6388" status="suspended" spid="118" sbid="46" ecid="0" priority="0" trancount="1" lastbatchstarted="2014-09-30T15:49:34.363" lastbatchcompleted="2014-09-30T15:49:34.363" lastattention="2014-09-30T15:49:34.200" clientapp="Microsoft Dynamics NAV Service"
hostpid="3132" " isolationlevel="repeatable read (3)" xactid="7887029" currentdb="18" lockTimeout="10000" clientoption1="671156320" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="62" sqlhandle="0x020000006224e3394bfc5e7f855be94f65186f09914a9b380000000000000000000000000000000000000000">
SELECT TOP (@0) "timestamp","Table ID","Document Type","Document No_","Line No_","Additional Charge Code","Charge (LCY)","Vendor No_","Allocation Method","Currency Code","Currency Factor","Charge","Currency Hedge Factor","Hedge Charge (LCY)","Sorting Key" FROM "XXX"."dbo"."Document Additional Charge" WITH(UPDLOCK) WHERE ("Document Type"=@1 AND "Document No_"=@2) ORDER BY "Table ID" ASC,"Document Type" ASC,"Document No_" ASC,"Line No_" ASC,"Additional Charge Code" ASC OPTION(OPTIMIZE FOR UNKNOWN, FAST 50) </frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@0 int,@1 int,@2 nvarchar(20))SELECT TOP (@0) "timestamp","Table ID","Document Type","Document No_","Line No_","Additional Charge Code","Charge (LCY)","Vendor No_","Allocation Method","Currency Code","Currency Factor","Charge","Currency Hedge Factor","Hedge Charge (LCY)","Sorting Key" FROM "XXX"."dbo"."Document Additional Charge" WITH(UPDLOCK) WHERE ("Document Type"=@1 AND "Document No_"=@2) ORDER BY "Table ID" ASC,"Document Type" ASC,"Document No_" ASC,"Line No_" ASC,"Additional Charge Code" ASC OPTION(OPTIMIZE FOR UNKNOWN, FAST 50) </inputbuf>
</process>
<process id="process10db46ecf8" taskpriority="0" logused="11560" waitresource="KEY: 18:72057602246443008 (cf3b096a0cc4)" waittime="970" ownerId="7887033" transactionname="user_transaction" lasttranstarted="2014-09-30T15:49:34.317" XDES="0x122aef6d28" lockMode="U" schedulerid="23" kpid="8040" status="suspended" spid="119" sbid="90" ecid="0" priority="0" trancount="1" lastbatchstarted="2014-09-30T15:49:34.580" lastbatchcompleted="2014-09-30T15:49:34.580" lastattention="2014-09-30T15:49:19.870" clientapp="Microsoft Dynamics NAV Service" hostpid="3132" " isolationlevel="repeatable read (3)" xactid="7887033" currentdb="18" lockTimeout="10000" clientoption1="671156320" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="62" sqlhandle="0x020000006224e3394bfc5e7f855be94f65186f09914a9b380000000000000000000000000000000000000000">
SELECT TOP (@0) "timestamp","Table ID","Document Type","Document No_","Line No_","Additional Charge Code","Charge (LCY)","Vendor No_","Allocation Method","Currency Code","Currency Factor","Charge","Currency Hedge Factor","Hedge Charge (LCY)","Sorting Key" FROM "XXX"."dbo"."Document Additional Charge" WITH(UPDLOCK) WHERE ("Document Type"=@1 AND "Document No_"=@2) ORDER BY "Table ID" ASC,"Document Type" ASC,"Document No_" ASC,"Line No_" ASC,"Additional Charge Code" ASC OPTION(OPTIMIZE FOR UNKNOWN, FAST 50) </frame>
</executionStack>
<inputbuf>
(@0 int,@1 int,@2 nvarchar(20))SELECT TOP (@0) "timestamp","Table ID","Document Type","Document No_","Line No_","Additional Charge Code","Charge (LCY)","Vendor No_","Allocation Method","Currency Code","Currency Factor","Charge","Currency Hedge Factor","Hedge Charge (LCY)","Sorting Key" FROM "XXX"."dbo"."Document Additional Charge" WITH(UPDLOCK) WHERE ("Document Type"=@1 AND "Document No_"=@2) ORDER BY "Table ID" ASC,"Document Type" ASC,"Document No_" ASC,"Line No_" ASC,"Additional Charge Code" ASC OPTION(OPTIMIZE FOR UNKNOWN, FAST 50) </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057602246443008" dbid="18" objectname="XXX.dbo.Document Additional Charge" indexname="Document Additional Charge$0" id="lock119194bf80" mode="X" associatedObjectId="72057602246443008">
<owner-list>
<owner id="process10db46ecf8" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process10c0681868" mode="U" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="72057602246443008" dbid="18" objectname="XXX.dbo.Document Additional Charge" indexname="Document Additional Charge$0" id="lock1253c2e800" mode="X" associatedObjectId="72057602246443008">
<owner-list>
<owner id="process10c0681868" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process10db46ecf8" mode="U" requestType="wait" />
</waiter-list>
</keylock>
</resource-list>
</deadlock>
October 1, 2014 at 2:24 am
They're row locks. So two exclusive locks on different rows of the same index.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 1, 2014 at 6:37 am
Thank you , reading up on deciphering the wait resource helped me understand that.
So we know that there are two rows locked in the index based on the index key hash value highlighted in bold
"KEY: 18:72057602246443008 (bed0c9e8f940)"
"KEY: 18:72057602246443008 (cf3b096a0cc4)"
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply