SqlProfiler "deadlock graph" diagram shows only 1 locked resource (2 resources shown in trace 1222 and .xdl) - by Jim McBrim

Status : 

  Not Reproducible<br /><br />
		The product team could not reproduce this item with the description and steps provided.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


2
0
Sign in
to vote
ID 656573 Comments
Status Closed Workarounds
Type Bug Repros 2
Opened 4/1/2011 3:49:09 AM
Access Restriction Public

Description

Hi, 
I recently came across a simple deadlock where two resources where being locked. Looking at the dealock graph diagram in profiler I could only see one of the resources.  This made the deadlock difficult to diagnose. On extracting the event data into an xdl file the second resource involved became obvious. 

Below is the xdl I extracted, the part of the xml that does not appear on the diagram  is the :
 '<objectlock lockPartition="0" objid="21575115" subresource="FULL" dbid="10" objectname="myDB.dbo.people" id="locka9b23300" mode="S" associatedObjectId="21575115">'

(I have obfuscated a few elements in the following xml)

<deadlock-list>
 <deadlock victim="processd3ed82c8">
  <process-list>
   <process id="processd3ed82c8" taskpriority="0" logused="0" waitresource="KEY: 10:72057594041794560 (1c00ccafd5bd)" waittime="3892" ownerId="64018898" transactionname="SELECT" lasttranstarted="2011-03-30T19:14:59.710" XDES="0x802ddb50" lockMode="S" schedulerid="1" kpid="3856" status="suspended" spid="72" sbid="2" ecid="0" priority="0" trancount="0" lastbatchstarted="2011-03-30T19:14:59.623" lastbatchcompleted="2011-03-30T19:14:59.623" clientapp=".Net SqlClient Data Provider" hostname="myServer" hostpid="4640" loginname="xx_user" isolationlevel="read committed (2)" xactid="64018898" currentdb="10" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="156" sqlhandle="0x02000000d6df0d0608023e1c0b2f0e65f9b592aadd2cde89">
SELECT TOP (30)    </frame>
     <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
(@p__linq__0 nvarchar(4000),@p__linq__1 datetime2(7),@p__linq__2 datetime2(7))SELECT TOP (30) 
 </inputbuf>
   </process>
   <process id="processcd2bfdc8" taskpriority="0" logused="5764" waitresource="OBJECT: 10:21575115:0 " waittime="3891" ownerId="64018854" transactionname="user_transaction" lasttranstarted="2011-03-30T19:14:59.537" XDES="0xc7723970" lockMode="IX" schedulerid="1" kpid="1800" status="suspended" spid="57" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2011-03-30T19:14:59.903" lastbatchcompleted="2011-03-30T19:14:59.830" clientapp=".Net SqlClient Data Provider" hostname="myServer" hostpid="2948" loginname="zz_user" isolationlevel="read committed (2)" xactid="64018854" currentdb="1" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="162" stmtend="484" sqlhandle="0x020000009b2b9e0de89472a6a281de89f2b2b289b5be7823">
insert [dbo].[people]() </frame>
     <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
(@0 char(64),@1 bigint,@2 int,@3 nvarchar(255),@4 nvarchar(255),@5 nvarchar(255))insert [dbo].[people](</inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock hobtid="72057594041794560" dbid="10" objectname="myDB.dbo.jobs" indexname="jobs_if3" id="lock97e44a00" mode="X" associatedObjectId="72057594041794560">
    <owner-list>
     <owner id="processcd2bfdc8" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="processd3ed82c8" mode="S" requestType="wait"/>
    </waiter-list>
   </keylock>
   <objectlock lockPartition="0" objid="21575115" subresource="FULL" dbid="10" objectname="myDB.dbo.people" id="locka9b23300" mode="S" associatedObjectId="21575115">
    <owner-list>
     <owner id="processd3ed82c8" mode="S"/>
    </owner-list>
    <waiter-list>
     <waiter id="processcd2bfdc8" mode="IX" requestType="wait"/>
    </waiter-list>
   </objectlock>
  </resource-list>
 </deadlock>
</deadlock-list>





Sign in to post a comment.
Posted by Mike Dimmick on 4/25/2013 at 8:07 AM
I have the following graph that also only shows one of the resources:

<resource-list>
<objectlock lockPartition="18" objid="1925581898" subresource="FULL" dbid="7" objectname="ePOD2_LIVE.dbo.U_JobData" id="lockddeac680" mode="S" associatedObjectId="1925581898">
    <owner-list>
     <owner id="process42b3198" mode="S"/>
    </owner-list>
    <waiter-list>
     <waiter id="process42bdac8" mode="IX" requestType="wait"/>
    </waiter-list>
</objectlock>
<pagelock fileid="1" pageid="83702" dbid="7" objectname="ePOD2_LIVE.dbo.C_JobData" id="lock2384dfa80" mode="IX" associatedObjectId="72057594086817792">
    <owner-list>
     <owner id="process42bdac8" mode="IX"/>
    </owner-list>
    <waiter-list>
     <waiter id="process42b3198" mode="S" requestType="wait"/>
    </waiter-list>
</pagelock>
</resource-list>

The pagelock shows up in the graph, but the object lock doesn't, on either SQL Server 2005 Profiler, Management Studio or SQL Server 2008 R2 Management Studio. The deadlock graph was captured from an instance of SQL Server 2005 SP3 (no subsequent updates, build 9.0.4035).
Posted by Evgeny [MSFT] on 5/9/2011 at 1:57 PM
Hi Jim,

Thank you very much for this report.

We have tried to reproduce the issue, but unfortunately it looks that we are unable to do this.

Could you share deadlocks graph and/or trace with us, same as more detailed queries which leads to an issue you are reporting?

Thanks again for providing feedback, it is very useful for us to improve quality of the product.

With best regards,
Evgeny Krivosheev