Deadlock Graph - Surviving Statement inputbuf does not show the object that was locked

Question

I have captured a deadlock on my system and the (anonymised) XML output is as follows:

<deadlock>
    <victim-list>
        <victimProcess id="processf4d9233468" />
    </victim-list>
    <process-list>
        <process id="processf4d9233468" taskpriority="0" logused="0" waitresource="KEY: 6:72057594039631872 (d117f90e375f)" waittime="481" ownerId="840005340" transactionname="SELECT" lasttranstarted="2019-10-14T10:16:07.550" XDES="0xeec803db90" lockMode="S" schedulerid="16" kpid="7220" status="suspended" spid="145" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2019-10-14T10:16:07.547" lastbatchcompleted="2019-10-14T10:16:07.550" lastattention="1900-01-01T00:00:00.550" clientapp=".Net SqlClient Data Provider" hostname="MYWEBSERVER" hostpid="4512" loginname="MyOtherLogin" isolationlevel="read committed (2)" xactid="840005340" currentdb="6" currentdbname="MyDB" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
            <executionStack>
             <frame procname="MyDB.MySchema.MyProc" line="13" stmtstart="670" stmtend="9106" sqlhandle="MYSQLHANDLE">
                    SELECT p.[Col25]
                                , p.Col1
                                , pId.Col2
                                , p.Col3
                                , p.Col4
                                , p.Col5
                                , CONVERT(VARCHAR(10),p.Col6,103)
                                , a.[Col7]
                                , a.[Col8]
                                , a.[Col9]
                                , a.[Col10]
                                , a.[Col11]
                                , a.[Col12]
                                , a.[Col13]
                                , a.[Col14]
                                , a.[Col15]
                                , a.[Col16]
                                , a.[Col17]
                                , a.[Col18]
                                , a.[Col19]
                            FROM
                            (
                                SELECT COALESCE(p.[Col34], p.[Col25]) AS Col25
                                        , lpm.[Col20]
                                        , lpm.[Col21]
                                        , ISNULL(c.[Col22] + ' ','') + ISNULL(c.[Col23] + ' ','') + ISNULL(c.[Col3],'')
                                        , Table1.[Col10] AS [Col10]
                                        , CONVERT(VARCHAR(10), ae.[Col23], 103) + ' ' + CONVERT(VARCHAR(5),ae.[Col23],108) AS Col11
                                        , ISNULL(Table2.[Col24], ae.[Col25]) AS Col12
                                        , ISNULL
                </frame>
            </executionStack>
           <inputbuf>
                Proc [Database Id = 6 Object Id = 279672044]
           </inputbuf>
        </process>
        <process id="process103fec6f468" taskpriority="0" logused="292" waitresource="OBJECT: 6:2105058535:30 " waittime="1449" ownerId="840433263" transactionname="user_transaction" lasttranstarted="2019-10-14T10:16:42.233" XDES="0x103f9fb0e80" lockMode="IX" schedulerid="31" kpid="9972" status="suspended" spid="130" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-10-14T10:16:42.240" lastbatchcompleted="2019-10-14T10:16:42.237" lastattention="1900-01-01T00:00:00.237" clientapp=".Net SqlClient Data Provider" hostname="MYWEBSERVER2" hostpid="3948" loginname="MyLogin" isolationlevel="read committed (2)" xactid="840433263" currentdb="6" currentdbname="MyDB" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
            <executionStack>
                <frame procname="adhoc" line="1" stmtstart="478" stmtend="1170" sqlhandle="0x020000006b742230b8276e1a3e7508dd257586243f5833450000000000000000000000000000000000000000">
                    unknown    
                </frame>
                <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
                    unknown
                </frame>
            </executionStack>
           <inputbuf>
                (@Col25 nvarchar(11),@Col26 nvarchar(11),@Col27 nvarchar(23),@Col28 nvarchar(12),@Col29 nvarchar(2),@Col30 nvarchar(4000),@Col23 nvarchar(23),@Col25 nvarchar(2),@Col31 nvarchar(11))
                 UPDATE [dbo].[TableInInputBuff]
                 SET    [Col25] = @Col25,
                        [Col26] = @Col26,
                        [Col27] = @Col27,
                        [Col28] = @Col28,
                        [Col29] = @Col29,
                        [Col30] = @Col30,
                        [Col23] = @Col23,
                        [Col25] = @Col25
                 OUTPUT Inserted.Col31
                 WHERE [Col31]=@Col31
            </inputbuf>
        </process>
    </process-list>
    <resource-list>
        <keylock hobtid="72057594039631872" dbid="6" objectname="MyDB.dbo.TableNotInInputBuff" indexname="PK__Encounte__4278DD362D27B809" id="lockf6be6aa900" mode="X" associatedObjectId="72057594039631872">
            <owner-list>
                <owner id="process103fec6f468" mode="X" />
            </owner-list>
            <waiter-list>
                <waiter id="processf4d9233468" mode="S" requestType="wait" />
            </waiter-list>
        </keylock>
        <objectlock lockPartition="30" objid="2105058535" subresource="FULL" dbid="6" objectname="MyDB.dbo.TableInInputBuff" id="lock10332f42500" mode="S" associatedObjectId="2105058535">
            <owner-list>
                <owner id="processf4d9233468" mode="S" />
            </owner-list>
            <waiter-list>
                <waiter id="process103fec6f468" mode="IX" requestType="wait" />
            </waiter-list>
        </objectlock>
    </resource-list>
</deadlock>

I can see that the surviving process (spid130) had an exclusive lock on a table MyDB.dbo.TableNotInInputBuff which is not used in the query that is shown in the <inputbuf> section for the process process103fec6f468

I assume what has happened here is that the input buffer only shows the last statement from a larger transaction and that an earlier statement in that transaction has done an update or similar on MyDB.dbo.TableNotInInputBuff and therefore the overall transaction is still holding the lock?

Two questions:

• is my assumption above correct
• if so, how can I track down the full transaction text to see what code has caused the lock?

Answer 1

There are circumstances in which a lock can be taken on a table that isn't the immediate target of the UPDATE (e.g. foreign keys or update triggers) but I assume this is not the case here?

Certainly there was a preceding batch in the same transaction as shown by the timestamps

• lasttranstarted="2019-10-14T10:16:42.233"
• lastbatchcompleted="2019-10-14T10:16:42.237"
• lastbatchstarted="2019-10-14T10:16:42.240"

So the lock could have been taken by another batch between 16:42.233 and 16:42.237.

There is nothing built in to help you with this. SQL Server does not retain this history of previously executed SQL text just on the off chance the session may be involved in a deadlock in a later batch.

The best way would be to audit the application code to see what other statements it submits earlier in the same transaction. If this isn't possible you would need to look at extended events to capture all statements from the application whilst it runs that code path.