Interpreting a Deadlock Log / XDL file

Question

I've generated the following XML + XDL:

<deadlock>
  <victim-list>
    <victimProcess id="processc1eaf13468" />
  </victim-list>
  <process-list>
    <process id="processc1eaf13468" taskpriority="0" logused="892" waitresource="KEY: 11:72057594044547072 (c9fb1da9313f)" waittime="5244" ownerId="118489" transactionname="user_transaction" lasttranstarted="2017-06-27T15:17:20.250" XDES="0xc1e878c4c0" lockMode="S" schedulerid="1" kpid="144900" status="suspended" spid="119" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2017-06-27T15:20:22.437" lastbatchcompleted="2017-06-27T15:17:21.003" lastattention="1900-01-01T00:00:00.003" clientapp=".Net SqlClient Data Provider" hostname="MIKBEN-W530" hostpid="30520" loginname="HuddleFm" isolationlevel="read committed (2)" xactid="118489" currentdb="11" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="unknown" queryhash="0x05ff9bdd8b808318" queryplanhash="0x5cc8b281eb7808cd" line="1" stmtstart="256" stmtend="970" sqlhandle="0x020000005f1e5a01ca72346b429f4c909878692fbda9bbd20000000000000000000000000000000000000000">
unknown    </frame>
        <frame procname="unknown" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
(@0 nvarchar(512),@1 int,@2 int,@3 bigint,@4 int,@5 int,@6 int,@7 datetime2(7),@8 int,@9 int,@10 bit,@11 bit,@12 nvarchar(max) )INSERT [dbo].[AppEvent]([MediaPath], [StorageProvider], [MediaType], [MediaSizeInBytes], [OldSessionState], [NewSessionState], [AppEventType], [EventDate], [DeviceSessionId], [TargetSessionId], [UserId], [ShouldBeDeleted], [HasBeenDeleted], [Payload], [LocalBrowserSessionGuid])
VALUES (@0, @1, @2, @3, @4, @5, @6, @7, @8, NULL, @9, @10, @11, NULL, @12)
SELECT [AppEventId]
FROM [dbo].[AppEvent]
WHERE @@ROWCOUNT &gt; 0 AND [AppEventId] = scope_identity()   </inputbuf>
    </process>
    <process id="processc1e7c26ca8" taskpriority="0" logused="1064" waitresource="KEY: 11:72057594043170816 (40fd182c0dd9)" waittime="5396" ownerId="118496" transactionname="user_transaction" lasttranstarted="2017-06-27T15:17:20.890" XDES="0xc1e88b44c0" lockMode="S" schedulerid="1" kpid="194344" status="suspended" spid="123" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2017-06-27T15:20:22.283" lastbatchcompleted="2017-06-27T15:17:21.060" lastattention="1900-01-01T00:00:00.060" clientapp=".Net SqlClient Data Provider" hostname="MIKBEN-W530" hostpid="30520" loginname="HuddleFm" isolationlevel="read committed (2)" xactid="118496" currentdb="11" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="unknown" queryhash="0xebf55cdceee65c9c" queryplanhash="0x5cc8b281eb7808cd" line="1" stmtstart="220" stmtend="936" sqlhandle="0x0200000066a736157a1b98ec891323511cd809b8ea3bf4a30000000000000000000000000000000000000000">
unknown    </frame>
        <frame procname="unknown" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
(@0 int,@1 int,@2 bigint,@3 int,@4 int,@5 int,@6 datetime2(7),@7 int,@8 int,@9 bit,@10 bit,@11 nvarchar(max) )INSERT [dbo].[AppEvent]([MediaPath], [StorageProvider], [MediaType], [MediaSizeInBytes], [OldSessionState], [NewSessionState], [AppEventType], [EventDate], [DeviceSessionId], [TargetSessionId], [UserId], [ShouldBeDeleted], [HasBeenDeleted], [Payload], [LocalBrowserSessionGuid])
VALUES (NULL, @0, @1, @2, @3, @4, @5, @6, @7, NULL, @8, @9, @10, NULL, @11)
SELECT [AppEventId]
FROM [dbo].[AppEvent]
WHERE @@ROWCOUNT &gt; 0 AND [AppEventId] = scope_identity()   </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <keylock hobtid="72057594044547072" dbid="11" objectname="e6288089-3180-4261-aa4e-916673f3cd8a.dbo.DeviceSession" indexname="PK_dbo.DeviceSession" id="lockc1f1b6b300" mode="X" associatedObjectId="72057594044547072">
      <owner-list>
        <owner id="processc1e7c26ca8" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="processc1eaf13468" mode="S" requestType="wait" />
      </waiter-list>
    </keylock>
    <keylock hobtid="72057594043170816" dbid="11" objectname="e6288089-3180-4261-aa4e-916673f3cd8a.dbo.User" indexname="PK_dbo.User" id="lockc1f1b6c280" mode="X" associatedObjectId="72057594043170816">
      <owner-list>
        <owner id="processc1eaf13468" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="processc1e7c26ca8" mode="S" requestType="wait" />
      </waiter-list>
    </keylock>
  </resource-list>
</deadlock>

I'm now trying to understand what's causing the deadlock; I've never interpreted this kind of log before. I see that there is some contention between User and DeviceSession but I'm not sure where that's coming from. How does one go about interpreting an XDL / Deadlock dump?

Answer 1

The deadlock you have is because each session has already taking a lock (on different resources) and subsequently wants to acquire the lock that is already held by the other session.

The easiest way to avoid deadlocks is to always perform actions in the same order throughout your software. This will allow the second session to block until the first session has completed its task.

I also presume you are not using snapshot isolation. This will stop readers from blocking writers and will most likely resolve this issue. https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server

Snapshot isolation does take more memory, so don't just turn it on. Performance profile your application with and without it.

Also, the InputBuf element refers to the current statement being run (requesting the lock), not the statement that caused the existing locks to occur.