November 21, 2014 at 3:17 pm
I suspect Management Studio is lying to me, but I am probably missing something.
<deadlock-list>
<deadlock victim="victim">
<process-list>
<process id="victim" taskpriority="0" logused="0" waitresource="KEY: 7:72057804259131392 (69d8115ef7b7)" waittime="4247" ownerId="826396819" transactionname="UPDATE" lasttranstarted="2014-11-21T14:52:36.883" XDES="0xb13f5d950" lockMode="U" schedulerid="10" kpid="78756" status="suspended" spid="494" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2014-11-21T14:52:36.880" lastbatchcompleted="2014-11-21T14:52:36.880" clientapp=".Net SqlClient Data Provider" hostname="3P-ATL-FE-03" hostpid="9332" loginname="WEBFE03" isolationlevel="read committed (2)" xactid="826396819" currentdb="7" lockTimeout="4294967295" clientoption1="538968096" clientoption2="128056">
<executionStack>
<frame procname="" line="90" stmtstart="6106" stmtend="7794" sqlhandle="0x030007002ec81f650eb83700fa9e00000100000000000000"/>
</executionStack>
<inputbuf>Proc [Database Id = 7 Object Id = 1696581678] </inputbuf>
</process>
<process id="process4671948" taskpriority="0" logused="0" waitresource="KEY: 7:72057804259131392 (69d8115ef7b7)" waittime="4248" ownerId="826423164" transactionname="UPDATE" lasttranstarted="2014-11-21T14:52:44.617" XDES="0x101e8583b0" lockMode="X" schedulerid="8" kpid="84044" status="suspended" spid="478" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2014-11-21T14:52:36.670" lastbatchcompleted="2014-11-21T14:52:36.667" clientapp="dispatchOffice" hostname="3P-ATL-CTX-01" hostpid="32572" loginname="3PDELIVERY\jespinoza" isolationlevel="read committed (2)" xactid="826423164" currentdb="7" lockTimeout="4294967295" clientoption1="538968096" clientoption2="128056">
<executionStack>
<frame procname="" line="124" stmtstart="8428" stmtend="9552" sqlhandle="0x030007002ec81f650eb83700fa9e00000100000000000000"/>
<frame procname="" line="283" stmtstart="23126" stmtend="23834" sqlhandle="0x03000700059237058cae3700fa9e00000100000000000000"/>
<frame procname="" line="361" stmtstart="34340" stmtend="34968" sqlhandle="0x030007002a106050947616006fa200000100000000000000"/>
<frame procname="" line="1" sqlhandle="0x010007003b968207c0079af81c0000000000000000000000"/>
</executionStack>
<inputbuf>exec pr_ratejob @p_cjobno = '123' </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057804259131392" dbid="7" objectname="" indexname="" id="lock14ca503400" mode="U" associatedObjectId="72057804259131392">
<owner-list>
<owner id="process4671948" mode="U"/>
</owner-list>
<waiter-list>
<waiter id="victim" mode="U" requestType="convert"/>
</waiter-list>
</keylock>
<keylock hobtid="72057804259131392" dbid="7" objectname="" indexname="" id="lock14ca503400" mode="U" associatedObjectId="72057804259131392">
<owner-list>
<owner id="victim" mode="S"/>
</owner-list>
<waiter-list>
<waiter id="process4671948" mode="X" requestType="convert"/>
</waiter-list>
</keylock>
</resource-list>
</deadlock>
</deadlock-list>
When I open the XML in a text editor, I see that the deadlock is on the same object in the resource list. I query sys.partitions for the hobtid in the resource list and see it is the clustered index on the same table.
The victim has a shared lock and has requested an exclusive lock. What confuses me is that when I open the file in the Management Studio 2014 GUI it shows as three update locks and an exclusive locks. I'm trying to understand if this is reader-writer blocking or writer-writer blocking.
I pulled the statement out of cache from the handle for the victim and blocker and see they are both written as UPDATE through a CTE
I'm a little confused about the blocker since there are multiple <frame> tags, but only the first one yields a statement, but maybe the cache was cleared by the time I grabbed the text. If multiple frames does only one show a statement, or is it the first or last? How to know which is the one causing the deadlock?
I didn't write the code, but since the update is through a CTE, and the XML shows an "S" lock, this feels like reader-writer blocking to me. Or should I trust the Managment Studio GUI that suggests this is writer-writer blocking?
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply