0

I'm trying to make sense of why SQL Server (2014) is placing an eXclusive key lock during a deadlock scenario. I've pasted the entire deadlock graph below.

I'm confused because the deadlock is happening between two SELECT statements, both running as a single READ COMMITTED statement, and NOT within a transaction (so there're no UPDATES etc occurring elsewhere in the same transaction).

I believe the deadlock occurs because each process is creating a range of key locks on the index and due to the order they are acquired, there is a deadlock occurring. However, if the processes were creating only Shared locks, there should be no deadlock (based on my understanding)!

So the fundamental question is - why would a eXclusive key lock be acquired by these SELECT statements?

I expect this just comes down to a misunderstanding on my part about locking. Any advice would be much appreciated.

    <deadlock>
  <victim-list>
    <victimProcess id="processd7f647848" />
  </victim-list>
  <process-list>
    <process id="processd7f647848" taskpriority="0" logused="25948" waitresource="KEY: 32:72057594051428352 (e2d15ad895e9)" waittime="5014" ownerId="394724294049" transactionname="user_transaction" lasttranstarted="2022-04-06T09:46:18.770" XDES="0x67bf873350" lockMode="S" schedulerid="15" kpid="26196" status="suspended" spid="306" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2022-04-06T09:46:18.783" lastbatchcompleted="2022-04-06T09:46:18.783" lastattention="1900-01-01T00:00:00.783" clientapp=".Net SqlClient Data Provider" hostname="myhost" hostpid="1500" loginname="myuser" isolationlevel="read committed (2)" xactid="394724294049" currentdb="32" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
      <executionStack>
        <frame procname="MyDatabase.Main.GetMessages" line="154" stmtstart="11934" stmtend="12156" sqlhandle="0x03002000ebd98264d1bfae0066ae000001000000000000000000000000000000000000000000000000000000">
SELECT ID, Message FROM [dbo].[Messages] WHERE MessageType = @Type    </frame>
      </executionStack>
      <inputbuf>
Proc [Database Id = 32 Object Id = 1686297067]   </inputbuf>
    </process>
    <process id="processd7f61f848" taskpriority="0" logused="27364" waitresource="KEY: 32:72057594051428352 (1b9d314f70e5)" waittime="6194" ownerId="394724293688" transactionname="user_transaction" lasttranstarted="2022-04-06T09:46:18.763" XDES="0x12ca804d350" lockMode="S" schedulerid="10" kpid="27008" status="suspended" spid="487" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2022-04-06T09:46:18.780" lastbatchcompleted="2022-04-06T09:46:18.780" lastattention="1900-01-01T00:00:00.780" clientapp=".Net SqlClient Data Provider" hostname="myhost" hostpid="1500" loginname="myuser" isolationlevel="read committed (2)" xactid="394724293688" currentdb="32" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
      <executionStack>
        <frame procname="MyDatabase.Main.GetMessages" line="154" stmtstart="11934" stmtend="12156" sqlhandle="0x03002000ebd98264d1bfae0066ae000001000000000000000000000000000000000000000000000000000000">
SELECT ID, Message FROM [dbo].[Messages] WHERE MessageType = @Type   </frame>
      </executionStack>
      <inputbuf>
Proc [Database Id = 32 Object Id = 1686297067]   </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <keylock hobtid="72057594051428352" dbid="32" objectname="MyDatabase.dbo.Messages" indexname="IX_Messages_Type" id="lockba5d041f80" mode="X" associatedObjectId="72057594051428352">
      <owner-list>
        <owner id="processd7f61f848" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="processd7f647848" mode="S" requestType="wait" />
      </waiter-list>
    </keylock>
    <keylock hobtid="72057594051428352" dbid="32" objectname="MyDatabase.dbo.Messages" indexname="IX_Messages_Type" id="lock11fad883600" mode="X" associatedObjectId="72057594051428352">
      <owner-list>
        <owner id="processd7f647848" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="processd7f61f848" mode="S" requestType="wait" />
      </waiter-list>
    </keylock>
  </resource-list>
</deadlock>
1
  • Possibly synchronous statistics update deadlock? Do you have it set to synchronous or asynchronous? Apr 11 at 16:03

2 Answers 2

4

The first thing you should do is run this:

SELECT sp = OBJECT_NAME(1686297067, 32);

That'll get you the name of the stored procedure that's part of the deadlock, which SQL Server couldn't be bothered to figure out on its own.

Which should get you to the statement(s) that were more intimately involved with the deadlock graph you're looking at.

I've talked about this before here: Deadlock Graph Frustrations, where seemingly unrelated statements are in the deadlock XML.

1
  • Thanks Erik. Getting the procedure name is straight forward enough, as you've shown. Although the procedure contains several UPDATE statements, there is no transaction i.e. it's just a series of independent statements. There is an explicit SET TRANSACTION ISOLATION LEVEL READ COMMITTED at the start of the proc. Originally this was SERIALIZABLE but I had the dev change it to READ COMMITTED, which actually made the deadlocking worse! The proc is called from SSRS so my current thinking is that perhaps something within SSRS is wrapping a transaction around the procedure call that I'm not seeing. Apr 19 at 9:54
0

So it turns out that SSRS does indeed wrap the datasets in a transaction, based on this article.

What's the deal with transactions in a stored procedure called by an SSRS report?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.