SQL Server - Exclusive lock taken by a SELECT statement - Why?
1
vote
2
answers
1536
views
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.
SELECT ID, Message FROM [dbo].[Messages] WHERE MessageType = @Type
Proc [Database Id = 32 Object Id = 1686297067]
SELECT ID, Message FROM [dbo].[Messages] WHERE MessageType = @Type
Proc [Database Id = 32 Object Id = 1686297067]
Asked by FrugalShaun
(441 rep)
Apr 8, 2022, 02:46 PM
Last activity: May 5, 2022, 10:22 AM
Last activity: May 5, 2022, 10:22 AM