DEADLOCK_GRAPH - exchangeEvent, what is it?

Question

I'm trying to diagnose a deadlock we're getting. I've got the DEADLOCK_GRAPH, and worked my way through what's going on.
All six processes are trying to DELETE from the same table (possible different ranges, not sure).
There are three resources listed. Two are as I'd expect - pagelocks on the table in question.
However the third is one that I've not seen before, and cannot find anything about. I've looked on google, BoL and searched these forums

 

Code Snippet

<exchangeEvent id="portffffffff800eea00" nodeId="2">

<owner-list><owner event="e_waitNone" type="producer" id="processC" /></owner-list>

<waiter-list><waiter event="e_waitPipeGetRow" type="consumer" id="processE" /></waiter-list>

</exchangeEvent>

 

 

(I've renamed the processes to help with my diagnostics, hence C and E

 

What is an "exchangeEvent" resource, and what can I do to alleviate the deadlock that it appears to be at the centre of?

TIA, moff.

Answer 1

Can you post the entire deadlock graph?  I can't answer specifically to the exchangeEvent node but I am pretty good at reading an entire graph and offering suggestions/queries to run to identify where the specific problem is.

 

Answer 2

wow, quick reply, thanks!

here it is (i've replaced object names etc with generic labels, but kept it consistent)

 

Code Snippet

<EVENT_INSTANCE>
  <EventType>DEADLOCK_GRAPH</EventType>
  <PostTime>2008-09-22T18:03:16.757</PostTime>
  <SPID>17</SPID>
  <TextData>
    <deadlock-list>
      <deadlock victim="processA">
        <process-list>
          <process id="processE" taskpriority="0" logused="20002" waittime="1921" schedulerid="1" kpid="3732" status="suspended" spid="77" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2008-09-22T18:03:03.097" lastbatchcompleted="2008-09-22T18:03:03.097" clientapp="Website" hostname="ServerA" hostpid="636" loginname="userID" isolationlevel="read committed (2)" xactid="412482713" currentdb="8" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
            <executionStack>
              <frame procname="DB1.dbo.uspMySproc" line="152" stmtstart="9936" stmtend="10194" sqlhandle="0x03000800baa63f40991bfb006a9a00000100000000000000">
delete from Table1
 where PageType = @PageType
 and PageId = @PageId

</frame>
              <frame procname="DB1.dbo.uspMyParentSproc" line="89" stmtstart="8792" stmtend="9172" sqlhandle="0x03000800da2e0a749a43d000199b00000100000000000000">
EXEC uspMySproc
     @PageType ,
     @PageId ,
     @WebsiteId

      </frame>
            </executionStack>
            <inputbuf>
Proc [Database Id = 8 Object Id = 1946824410]    </inputbuf>
          </process>
          <process id="processA" taskpriority="0" logused="0" waitresource="PAGE: 8:1:1239" waittime="3328" ownerId="412482673" transactionname="DELETE" lasttranstarted="2008-09-22T18:03:04.720" XDES="0x2616fe08" lockMode="U" schedulerid="1" kpid="2348" status="suspended" spid="104" sbid="0" ecid="2" priority="0" transcount="0" lastbatchstarted="2008-09-22T18:03:03.160" lastbatchcompleted="2008-09-22T18:03:03.160" clientapp="Website" hostname="ServerA" hostpid="636" isolationlevel="read committed (2)" xactid="412482673" currentdb="8" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
            <executionStack>
              <frame procname="DB1.dbo.uspMySproc" line="152" stmtstart="9936" stmtend="10194" sqlhandle="0x03000800baa63f40991bfb006a9a00000100000000000000">
delete from Table1
 where PageType = @PageType
 and PageId = @PageId

</frame>
              <frame procname="DB1.dbo.uspMyParentSproc" line="89" stmtstart="8792" stmtend="9172" sqlhandle="0x03000800da2e0a749a43d000199b00000100000000000000">
EXEC uspMySproc
     @PageType ,
     @PageId ,
     @WebsiteId

      </frame>
            </executionStack>
            <inputbuf />
          </process>
          <process id="processC" taskpriority="0" logused="7204" waitresource="PAGE: 8:1:261" waittime="11953" ownerId="412482713" transactionname="DELETE" lasttranstarted="2008-09-22T18:03:04.737" XDES="0xffffffff85d3dcb8" lockMode="U" schedulerid="1" kpid="872" status="suspended" spid="77" sbid="0" ecid="2" priority="0" transcount="0" lastbatchstarted="2008-09-22T18:03:03.097" lastbatchcompleted="2008-09-22T18:03:03.097" clientapp="Website" hostname="ServerA" hostpid="636" isolationlevel="read committed (2)" xactid="412482713" currentdb="8" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
            <executionStack>
              <frame procname="DB1.dbo.uspMySproc" line="152" stmtstart="9936" stmtend="10194" sqlhandle="0x03000800baa63f40991bfb006a9a00000100000000000000">
delete from Table1
 where PageType = @PageType
 and PageId = @PageId

</frame>
              <frame procname="DB1.dbo.uspMyParentSproc" line="89" stmtstart="8792" stmtend="9172" sqlhandle="0x03000800da2e0a749a43d000199b00000100000000000000">
EXEC uspMySproc
     @PageType ,
     @PageId ,
     @WebsiteId

      </frame>
            </executionStack>
            <inputbuf />
          </process>
          <process id="processD" taskpriority="0" logused="0" waitresource="PAGE: 8:1:261" waittime="9453" ownerId="412495478" transactionname="DELETE" lasttranstarted="2008-09-22T18:03:07.220" XDES="0xffffffffb45a6c78" lockMode="U" schedulerid="1" kpid="3976" status="suspended" spid="87" sbid="0" ecid="2" priority="0" transcount="0" lastbatchstarted="2008-09-22T18:02:49.880" lastbatchcompleted="2008-09-22T18:02:49.880" clientapp="Website" hostname="ServerA" hostpid="636" isolationlevel="read committed (2)" xactid="412495478" currentdb="8" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
            <executionStack>
              <frame procname="DB1.dbo.uspMySproc" line="152" stmtstart="9936" stmtend="10194" sqlhandle="0x03000800baa63f40991bfb006a9a00000100000000000000">
delete from Table1
 where PageType = @PageType
 and PageId = @PageId

</frame>
              <frame procname="DB1.dbo.uspMyParentSproc" line="89" stmtstart="8792" stmtend="9172" sqlhandle="0x03000800da2e0a749a43d000199b00000100000000000000">
EXEC uspMySproc
     @PageType ,
     @PageId ,
     @WebsiteId

      </frame>
            </executionStack>
            <inputbuf />
          </process>
          <process id="processB" taskpriority="0" logused="0" waitresource="PAGE: 8:1:1816" waittime="11953" ownerId="412482673" transactionname="DELETE" lasttranstarted="2008-09-22T18:03:04.720" XDES="0x48e73a40" lockMode="U" schedulerid="2" kpid="3588" status="suspended" spid="104" sbid="0" ecid="1" priority="0" transcount="0" lastbatchstarted="2008-09-22T18:03:03.160" lastbatchcompleted="2008-09-22T18:03:03.160" clientapp="Website" hostname="ServerA" hostpid="636" isolationlevel="read committed (2)" xactid="412482673" currentdb="8" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
            <executionStack>
              <frame procname="DB1.dbo.uspMySproc" line="152" stmtstart="9936" stmtend="10194" sqlhandle="0x03000800baa63f40991bfb006a9a00000100000000000000">
delete from Table1
 where PageType = @PageType
 and PageId = @PageId

</frame>
              <frame procname="DB1.dbo.uspMyParentSproc" line="89" stmtstart="8792" stmtend="9172" sqlhandle="0x03000800da2e0a749a43d000199b00000100000000000000">
EXEC uspMySproc
     @PageType ,
     @PageId ,
     @WebsiteId

      </frame>
            </executionStack>
            <inputbuf />
          </process>
          <process id="processF" taskpriority="0" logused="0" waitresource="PAGE: 8:1:1816" waittime="9453" ownerId="412495478" transactionname="DELETE" lasttranstarted="2008-09-22T18:03:07.220" XDES="0x28396868" lockMode="U" schedulerid="2" kpid="720" status="suspended" spid="87" sbid="0" ecid="1" priority="0" transcount="0" lastbatchstarted="2008-09-22T18:02:49.880" lastbatchcompleted="2008-09-22T18:02:49.880" clientapp="Website" hostname="ServerA" hostpid="636" isolationlevel="read committed (2)" xactid="412495478" currentdb="8" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
            <executionStack>
              <frame procname="DB1.dbo.uspMySproc" line="152" stmtstart="9936" stmtend="10194" sqlhandle="0x03000800baa63f40991bfb006a9a00000100000000000000">
delete from Table1
 where PageType = @PageType
 and PageId = @PageId

</frame>
              <frame procname="DB1.dbo.uspMyParentSproc" line="89" stmtstart="8792" stmtend="9172" sqlhandle="0x03000800da2e0a749a43d000199b00000100000000000000">
EXEC uspMySproc
     @PageType ,
     @PageId ,
     @WebsiteId

      </frame>
            </executionStack>
            <inputbuf />
          </process>
        </process-list>
        <resource-list>
          <pagelock fileid="1" pageid="261" dbid="8" objectname="DB1.dbo.Table1" id="lockffffffff99728840" mode="U" associatedObjectId="96653824229376">
            <owner-list>
              <owner id="processA" mode="U" />
            </owner-list>
            <waiter-list>
              <waiter id="processC" mode="U" requestType="wait" />
              <waiter id="processD" mode="U" requestType="wait" />
            </waiter-list>
          </pagelock>
          <exchangeEvent id="portffffffff800eea00" nodeId="2">
            <owner-list>
              <owner event="e_waitNone" type="producer" id="processC" />
            </owner-list>
            <waiter-list>
              <waiter event="e_waitPipeGetRow" type="consumer" id="processE" />
            </waiter-list>
          </exchangeEvent>
          <pagelock fileid="1" pageid="1816" dbid="8" objectname="DB1.dbo.Table1" id="lockffffffffb2f9d000" mode="U" associatedObjectId="96653824229376">
            <owner-list>
              <owner id="processE" mode="U" />
            </owner-list>
            <waiter-list>
              <waiter id="processB" mode="U" requestType="wait" />
              <waiter id="processF" mode="U" requestType="wait" />
            </waiter-list>
          </pagelock>
          <pagelock fileid="1" pageid="1239" dbid="8" objectname="DB1.dbo.Table1" id="lockffffffffb27ca680" mode="U" associatedObjectId="96653824229376">
            <owner-list>
              <owner id="processE" mode="U" />
            </owner-list>
            <waiter-list>
              <waiter id="processA" mode="U" requestType="wait" />
            </waiter-list>
          </pagelock>
        </resource-list>
      </deadlock>
    </deadlock-list>
  </TextData>
  <TransactionID />
  <LoginName>sa</LoginName>
  <StartTime>2008-09-22T18:03:16.753</StartTime>
  <ServerName>serverB</ServerName>
  <LoginSid>AQ==</LoginSid>
  <EventSequence>4549864</EventSequence>
  <IsSystem>1</IsSystem>
  <SessionLoginName />
</EVENT_INSTANCE>

 

 

Answer 3

Is this deleting in a loop?  Take this query:

 

delete from Table1
 where PageType = @PageType
 and PageId = @PageId

 

 

and create two local variables in a query window with the correct datatype and run it like this:

 

 

Code Snippet

set showplan_text on

go

declare @pagetype int

declare @pageid int

 

delete from Table1
 where PageType = @PageType
 and PageId = @PageId

go

set showplan_text off

 

 

 

and provide the showplan text back in this post.

Answer 4

Code Snippet

  |--Table Delete(OBJECT:([DB1].[dbo].[Table1]))
       |--Top(ROWCOUNT est 0)
            |--Parallelism(Gather Streams)
                 |--Table Scan(OBJECT:([DB1].[dbo].[Table1]), WHERE:([DB1].[dbo].[Table1].[PageId]=[@PageId] AND [DB1].[dbo].[Table1].[PageType]=[@PageType]) ORDERED)                

Looking at this has pointed me to what I think is the problem - lack of indexes.

This table exists on several servers and the indexes just haven't made it across onto the one where we saw the deadlock.

I'm going to create them and then rerun the app that triggered the deadlocks and see if it's fixed it.

Is it a fair assumption that exchangeEvent is something to do with the parallelism?

cheers, moff.

Answer 5

I don't know for certain that it is, and I haven't gotten an answer back from an email I sent to find out.  That plan would definately be my first point of resolution to the deadlocking problem.  You could test your theory about parallelism being the issue by adding option(maxdop 1) to the statement which removes parallelism from the equation.  I would say that an appropriate index on the PageID and PageType will resolve the current problem.

Answer 6

Moff,

 

Good news, I got a response back, and Bart Duncan was kind enough to not just explain it, but to make a full blog posting in his blog about it;

 

http://blogs.msdn.com/bartd/archive/2008/09/24/today-s-annoyingly-unwieldy-term-intra-query-parallel-thread-deadlocks.aspx

 

In fact your post now has the esteem of being a reference for this exact problem in his blog posting.  Take a look at the information he provided in the blog post, and it should become clear why you have the deadlocks.

 

Answer 7

Jonathan (and Bart),
Many thanks for your help.

Cheers, moff.