SQL Server Exclusive(X) lock not consistently blocking Shared(S) lock on a resource

My understanding of locking in SQL Server has been that if one process holds an exclusive (X) lock on a resource e.g.a key row in an index, another process cannot acquire a Shared (S) lock on that same resource and must wait for it to be released.

I have been trying to demo a fix I have for a deadlock between 2 different stored procedures accessing the same table, one using an explicit transaction to wrap around a SELECT and then a DELETE, and the other which just does a SELECT with no explicit transaction. These both happen under the READ COMMITTED transaction isolation level.

I go through the steps to emulate each of the procs in 2 SSMS query windows and query sys.dm_tran_locks to view the locks being held and waiting after each step.

I would like someone to explain why, although I do sometimes get the expected deadlock, I don't always, and I can see the X lock having been granted to one connection while I can still do a select from the other connection.

The demo is contrived and the reason for splitting the SELECT on the non-clustered index, to get the clustered key value, with the SELECT that reads other columns from the clustered index using the clustered key (and wrapping both selects in an explicit transaction with HOLDLOCK on the first) is to emulate the actual query plan of my real world query, which seeks on the non-clustered index and does a Key Lookup on the clustered index. I needed to show what happens when the DELETE query on the Read-write connection clashes with the SELECT on the Readonly connection

The test database does not allow SNAPSHOT ISOLATION or READ COMMITTED SNAPSHOT and both connections have been explicitly set to READ COMMITTED Transaction isolation level so shared locks should be required to read a row.

I have tested this on my local machine with SQL2017 CU9 (on 64 bit Windows 10 Enterprise build 16299), on a server with SQL2017 CU7 (on Windows Server 2016 Standard build 14393) and also a server with SQL2016 sp1-CU2 (on Windows Se

I set up the following Extended Events session to capture the locks taken during step 6, filtered to that spid (55 for me):

CREATE EVENT SESSION [locks] ON SERVER 
ADD EVENT sqlserver.lock_acquired(
    ACTION(sqlserver.session_id,sqlserver.sql_text)
    WHERE ([sqlserver].[session_id]=(55)))
ADD TARGET package0.ring_buffer
WITH (STARTUP_STATE=OFF)
GO

The lock taken was an Intent-Shared (IS) lock on the page (despite the ROWLOCK hint).

This matches the X-locked object_id in the results of the sys.dm_tran_locks query you provided:

See Paul White's post "The Case of the Missing Shared Locks" for details, but this is a specific optimization that allows bypassing the blocking that you expect:


SQL Server contains an optimization that allows it to avoid taking row-level shared (S) locks in the right circumstances. Specifically, it can skip shared locks if there is no risk of reading uncommitted data without them.

The query in step 6 takes an IS lock on the page, skipping the normal first step of taking an S lock on the key (row).

It's possible that the intermittent nature of this behavior in your demo code is due to the fact the the ROWLOCK hint is not always used:


ROWLOCK is genuinely a hint, rather than a directive — the storage engine may or may not respect it.

Or there are other uncommitted changes on the data page (this isn't the case in your repro, but could be the case in your real scenario):


SQL Server cannot apply the locking optimization if there is an uncommitted change on the same page.

Incidentally, I haven't experienced the deadlock behavior at all on my laptop (SQL Server 2017 CU13).

StackExchange Database Administrators Q#233412, answer score: 3