Deadlock graph, is Management Studio GUI lying to me?

• jallmond

  Ten Centuries

  Points: 1181

  More actions

  November 21, 2014 at 3:17 pm

  #292725

  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?