Sample Header Ad - 728x90

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