SqlServer Dead Lock Graph - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

SqlServer Dead Lock Graph

Author

Topic

abugov

6 Posts

Posted - 2008-02-19 : 09:14:20

Hello.

Using profiler90 we got the following dead lock graph on our sql server 2005 sp2.

We can't understand it because the graph doesn't show the normal dead-lock "loop" (from transaction A to B and from B back to A)
Also, there is somthing suspicious with the "IX" lock (intent exclusive).

You can copy the following xml to ".xdl" file and open it using the profiler90:

<deadlock-list>
 <deadlock victim="process929888">
  <process-list>
   <process id="process9295b8" taskpriority="0" logused="0" waitresource="OBJECT: 11:1349579846:0 " waittime="8375" ownerId="1272309" transactionname="SELECT" lasttranstarted="2008-02-14T16:55:33.457" XDES="0x1ee49e30" lockMode="IS" schedulerid="2" kpid="6052" status="suspended" spid="138" sbid="0" ecid="0" priority="0" transcount="0" lastbatchstarted="2008-02-14T16:55:33.457" lastbatchcompleted="2008-02-14T16:55:33.457" clientapp=".Net SqlClient Data Provider" hostname="MORE-SRV" hostpid="3904" loginname="NT AUTHORITY\SYSTEM" isolationlevel="read committed (2)" xactid="1272309" currentdb="11" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="54" sqlhandle="0x020000007dd2d136d1d526b2b80a3ba8467217f5c03c4bb5">
SELECT Name FROM Station
                  WHERE Name LIKE @DisplayName     </frame>
     <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
(@DisplayName nvarchar(34))SELECT Name FROM Station
                  WHERE Name LIKE @DisplayName    </inputbuf>
   </process>
   <process id="process929888" taskpriority="0" logused="0" waitresource="RID: 11:1:634:77" waittime="5000" ownerId="1272453" transactionname="user_transaction" lasttranstarted="2008-02-14T16:55:34.397" XDES="0x1e97e250" lockMode="X" schedulerid="2" kpid="5720" status="suspended" spid="119" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2008-02-14T16:55:34.410" lastbatchcompleted="2008-02-14T16:55:34.397" clientapp=".Net SqlClient Data Provider" hostname="MORE-SRV" hostpid="3904" loginname="NT AUTHORITY\SYSTEM" isolationlevel="serializable (4)" xactid="1272453" currentdb="11" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="96" sqlhandle="0x0200000038a18e15bb6e49c5a76ac8aa5c576170baa034aa">
UPDATE StationKeepAlive SET LastKeepAliveTime = @LastKeepAliveTime
                          WHERE Name LIKE @Name     </frame>
     <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
(@LastKeepAliveTime datetime,@Name nvarchar(33))UPDATE StationKeepAlive SET LastKeepAliveTime = @LastKeepAliveTime
                          WHERE Name LIKE @Name    </inputbuf>
   </process>
   <process id="process92ed48" taskpriority="0" logused="0" waitresource="OBJECT: 11:626101271:0 " waittime="7234" ownerId="1272298" transactionname="user_transaction" lasttranstarted="2008-02-14T16:55:33.457" XDES="0x1e6db570" lockMode="S" schedulerid="3" kpid="4528" status="suspended" spid="92" sbid="0" ecid="0" priority="0" transcount="1" lastbatchstarted="2008-02-14T16:55:34.600" lastbatchcompleted="2008-02-14T16:55:34.600" clientapp=".Net SqlClient Data Provider" hostname="MORE-SRV" hostpid="3904" loginname="NT AUTHORITY\SYSTEM" isolationlevel="serializable (4)" xactid="1272298" currentdb="11" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="40" sqlhandle="0x02000000fdd60c2ec0fb00a86890ff9ca1f00f5f0e6e7f7e">
SELECT LastKeepAliveTime FROM StationKeepAlive WHERE Name LIKE @Name     </frame>
     <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
(@Name nvarchar(23))SELECT LastKeepAliveTime FROM StationKeepAlive WHERE Name LIKE @Name    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <ridlock fileid="1" pageid="634" dbid="11" objectname="QsController.dbo.StationKeepAlive" id="lock5a7c6c0" mode="U" associatedObjectId="72057594044743680">
    <owner-list>
     <owner id="process92ed48" mode="S"/>
    </owner-list>
    <waiter-list>
     <waiter id="process929888" mode="X" requestType="convert"/>
    </waiter-list>
   </ridlock>
   <objectlock lockPartition="0" objid="626101271" subresource="FULL" dbid="11" objectname="QsController.dbo.StationKeepAlive" id="lock62984c0" mode="IX" associatedObjectId="626101271">
    <owner-list>
     <owner id="process929888" mode="IX"/>
    </owner-list>
    <waiter-list>
     <waiter id="process92ed48" mode="S" requestType="convert"/>
    </waiter-list>
   </objectlock>
   <objectlock lockPartition="0" objid="1349579846" subresource="FULL" dbid="11" objectname="QsController.dbo.Station" id="lock19873580" mode="X" associatedObjectId="1349579846">
    <owner-list>
     <owner id="process92ed48" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process9295b8" mode="IS" requestType="wait"/>
    </waiter-list>
   </objectlock>
  </resource-list>
 </deadlock>
</deadlock-list>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources