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

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

```








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



Proc [Database Id = 6 Object Id = 279672044]





unknown


unknown

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.

StackExchange Database Administrators Q#251008, answer score: 5