SQL Server - Exclusive lock taken by a SELECT statement - Why?

Question

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>

Erik Reasonable Rates Darling · Answer 1 · 2022-04-08T18:29:19.683

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.

score 1 · Answer 2 · answered May 05 '22 at 10:22

1

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?

answered May 05 '22 at 10:22

FrugalShaun

441
3
11

SQL Server - Exclusive lock taken by a SELECT statement - Why?

2 Answers2