Find out deadlock reason

Question

We have NUnit project witn tests and many cases for each test. All of them run in parallel mode. I found 2 deadlock reasons. 1 - is table, but second one - no name or some id.

How to find this object?

just for reference We have gybrid mode in app ef context + dapper for difficult sql

Answer 1

Extract the XML of the deadlock graph and use the script I give to you to extract SQL text to undrerstand what's happening...

DECLARE @XML XML = N'??? my deadlock XML !!!';

WITH
TX AS
(
SELECT @XML AS TextData
),
TVM AS
(
SELECT v.value('(./inputbuf)[1]','nvarchar(max)') AS Query,
       i.value('(./deadlock/@victim)[1]','varchar(32)') AS ProcessVictim,
       v.value('(./@id)[1]','varchar(32)') AS ProcessID
FROM  TX
      CROSS APPLY TextData.nodes('/deadlock-list') AS X(i)
      CROSS APPLY TextData.nodes('/deadlock-list/deadlock/process-list/process') AS V(v)
),
TVV AS
(
SELECT DENSE_RANK() OVER (ORDER BY StartTime) AS ID,
       Query,
       CASE WHEN ProcessVictim = ProcessID THEN 'Victim!' ELSE 'Alive' END AS FinalState
FROM TVM
),
TQV AS
(
SELECT ID, Query
FROM   TVV
WHERE  FinalState = 'Victim!'
)
SELECT TVV.ID, TVV.Query,
       CASE WHEN TQV.ID IS NOT NULL THEN 'Victim!' ELSE 'Alive' END AS FinalState
FROM   TVV
       LEFT OUTER JOIN TQV
             ON TVV.ID = TQV.ID AND TVV.Query = TQV.Query
ORDER BY 1;

The result will be like this :