How complete is the SQL Server 2008 deadlock graph executionStack?

Question

When I get the deadlock graph xml in SQL Server, it shows me an executionStack for each process, e.g. (I have removed most of the attributes):

<process>
<executionStack>
    <frame>INSERT INTO MYTABLE</frame>
    <frame>INSERT INTO MYTABLE</frame>
</executionStack>
<inputbuf>INSERT INTO MYTABLE</inputbuf>
</process>

I know the list of frames does not contain all the frames in the transaction, but does it contain all the frames that are implicated in the deadlock, so I do not need to look any further? Or do I need to also examine the preceding sql statements in the same transaction to get a complete picture as to the cause of the deadlock?

Edit: to clarify further, the following can deadlock itself if run in two separate spids:

select * from mytable where column = @arg delete from mytable where column = @arg

Will the deadlock graph show the select statement as well as the delete statement, or only the delete statement because that was the last statement in the transaction? Without seeing the select statement too, it is difficult to identify the correct fix (e.g., updlock hint on the select).

How complete? It should contain all the info necessary to diagnose the cause.... — Mitch Wheat
– Mitch Wheat, Commented May 26, 2011 at 8:25

Martin Smith · Accepted Answer · 2013-12-16 01:24:59Z

You need to examine the preceding sql statements in the same transaction.

The deadlock graph just shows you the call stack for the statements executing when the deadlock occurred. The statements that actually acquired the deadlocked resources may not be present. This is easily demonstrated as follows.

Connection 1

CREATE TABLE T1
  (
     id  INT PRIMARY KEY,
     foo CHAR(100),
     bar CHAR(100)
  )

INSERT INTO T1
            (id)
VALUES     (1),
            (2)

GO

BEGIN TRAN

UPDATE T1
SET    foo = '123'
WHERE  id = 1

WAITFOR DELAY '00:00:05'

GO

UPDATE T1
SET    bar = 'dlock'
WHERE  id = 2

ROLLBACK

DROP TABLE T1

Connection 2 (Execute quickly after Conn 1)

BEGIN TRAN

UPDATE T1
SET    foo = '123'
WHERE  id = 2

WAITFOR DELAY '00:00:05'

GO

UPDATE T1
SET    bar = 'dlock'
WHERE  id = 1

ROLLBACK

Deadlock Graph

<process-list>
    <process
        id="process520d1c8"
        taskpriority="0"
        logused="504"
        waitresource="KEY: 1:72057594051100672 (010086470766)"
        waittime="3666"
        ownerId="10399789"
        transactionname="user_transaction"
        lasttranstarted="2011-10-30T13:45:29.030"
        XDES="0x4eb5be8"
        lockMode="X"
        schedulerid="2"
        kpid="8656"
        status="suspended"
        spid="59"
        sbid="0"
        ecid="0"
        priority="0"
        trancount="2"
        lastbatchstarted="2011-10-30T13:45:34.047"
        lastbatchcompleted="2011-10-30T13:45:34.043"
        clientapp="Microsoft SQL Server Management Studio - Query"
        hostname="MyPC"
        hostpid="7544"
        loginname="Me"
        isolationlevel="read committed (2)"
        xactid="10399789"
        currentdb="1"
        lockTimeout="4294967295"
        clientoption1="671090784"
        clientoption2="390200">
        <executionStack>
            <frame
                procname="adhoc"
                line="2"
                stmtstart="58"
                sqlhandle="0x02000000b24eb7001f552b64e5c2bf2ccb1f2acfda154410">
            UPDATE [T1] set [bar] = @1  WHERE [id]=@2
          </frame>
            <frame
                procname="adhoc"
                line="2"
                stmtstart="4"
                stmtend="90"
                sqlhandle="0x020000002688730e96c94af4582dfb097fa79a39ea620d63">
            UPDATE T1 SET bar = 'dlock' where id=1
          </frame>
        </executionStack>
        <inputbuf>

          UPDATE T1 SET bar = 'dlock' where id=1


          rollback
        </inputbuf>
    </process>
    <process
        id="process370d8718"
        taskpriority="0"
        logused="504"
        waitresource="KEY: 1:72057594051100672 (020068e8b274)"
        waittime="5579"
        ownerId="10399738"
        transactionname="user_transaction"
        lasttranstarted="2011-10-30T13:45:27.040"
        XDES="0x1ef7ac10"
        lockMode="X"
        schedulerid="1"
        kpid="2060"
        status="suspended"
        spid="52"
        sbid="0"
        ecid="0"
        priority="0"
        trancount="2"
        lastbatchstarted="2011-10-30T13:45:32.083"
        lastbatchcompleted="2011-10-30T13:45:32.043"
        clientapp="Microsoft SQL Server Management Studio - Query"
        hostname="MyPC"
        hostpid="7544"
        loginname="Me"
        isolationlevel="read committed (2)"
        xactid="10399738"
        currentdb="1"
        lockTimeout="4294967295"
        clientoption1="671098976"
        clientoption2="390200">
        <executionStack>
            <frame
                procname="adhoc"
                line="3"
                stmtstart="58"
                sqlhandle="0x02000000b24eb7001f552b64e5c2bf2ccb1f2acfda154410">
            UPDATE [T1] set [bar] = @1  WHERE [id]=@2
          </frame>
            <frame
                procname="adhoc"
                line="3"
                stmtstart="8"
                stmtend="94"
                sqlhandle="0x020000004a869b267636c00306e481791dec78ade36b3f39">
            UPDATE T1 SET bar = 'dlock' where id=2
          </frame>
        </executionStack>
        <inputbuf>


          UPDATE T1 SET bar = 'dlock' where id=2


          ROLLBACK

          DROP TABLE T1
        </inputbuf>
    </process>
</process-list>

The assignments to column foo that acquired the locks in the first place aren't shown.

(NB: Without the GO statement to make the statements separate batches you would in this case see the offending statement but in general the lock acquisition might occur further down the call stack so the statement that actually acquired the locks still wouldn't be shown)

Collectives™ on Stack Overflow

How complete is the SQL Server 2008 deadlock graph executionStack?

1 Answer 1

Connection 1

Connection 2 (Execute quickly after Conn 1)

Deadlock Graph

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Connection 1

Connection 2 (Execute quickly after Conn 1)

Deadlock Graph

Comments

Your Answer

Sign up or log in

Post as a guest

Related