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 Server 2012 R2 Standard build 9600). All were 64 bit Developer versions of SQL Server.
I've included the code to reproduce this below: -
Create the database and table then populate it
CREATE DATABASE LockingRepro; GO USE [LockingRepro]; GO CREATE TABLE dbo.Person ( PersonId INT NOT NULL CONSTRAINT PK_Person PRIMARY KEY CLUSTERED, LoginId VARCHAR(50) NOT NULL ) ON [PRIMARY]; CREATE TABLE dbo.PersonSession ( PersonId INT NOT NULL CONSTRAINT PK_PersonSession PRIMARY KEY CLUSTERED, SessionId UNIQUEIDENTIFIER NOT NULL, LastUpdated DATETIME NOT NULL, SessionExpiryDate DATETIME NULL ) ON [PRIMARY]; GO CREATE UNIQUE NONCLUSTERED INDEX [IX_SessionId] ON dbo.PersonSession (SessionId) ON [PRIMARY]; GO ALTER TABLE dbo.PersonSession ADD CONSTRAINT [FK_PersonSession_Person] FOREIGN KEY (PersonId) REFERENCES dbo.Person (PersonId); GO GO SET NOCOUNT ON; DECLARE @i INT = 0; WHILE @i < 1000 BEGIN SET @i += 1; INSERT INTO dbo.Person (PersonId, LoginId) VALUES (@i, 'xxxxxxxxxxxx' + CAST(@i AS VARCHAR(5))); INSERT INTO dbo.PersonSession ( PersonId, SessionId, LastUpdated, SessionExpiryDate) VALUES ( @i, NEWID(), GETUTCDATE(), DATEADD(MINUTE, 30, GETUTCDATE())); END GO --Get the SessionId guid to paste into the other queries SELECT SessionId FROM dbo.PersonSession WHERE PersonId = 100
Paste this code into a new query window to emulate the read write proc steps
--Read-write query window --Pre-test steps USE [LockingRepro]; GO SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL READ COMMITTED SELECT @@SPID -- Grab the Spid to use in the lock querying window ------------------------------ --1 BEGIN TRAN --2 SELECT * -- FROM dbo.PersonSession WITH (ROWLOCK, UPDLOCK, HOLDLOCK) WHERE SessionId = 'Paste the SessionId for PersonId 100 here' --5 DELETE d FROM dbo.PersonSession AS d WITH (ROWLOCK) WHERE PersonId = 100 -- Clean up IF @@TRANCOUNT > 0 ROLLBACK
Paste this code into a new query window to emulate the read only proc steps
-- Read only query window --Pre-test steps USE [LockingRepro]; GO SET TRANSACTION ISOLATION LEVEL READ COMMITTED SET NOCOUNT ON SELECT @@SPID ----------------------------------------- --3 BEGIN TRAN --4 SELECT PersonId FROM dbo.PersonSession WITH (ROWLOCK, HOLDLOCK) WHERE SessionId = 'Paste the SessionId for PersonId 100 here' --6 SELECT PersonId, LastUpdated, SessionId, SessionExpiryDate FROM dbo.PersonSession WITH (ROWLOCK) WHERE PersonId = 100 -- Clean up IF @@TRANCOUNT > 0 ROLLBACK
Paste this code into a separate query window and edit to use the SPIDs from the ReadOnly and ReadWrtie query windows
You will need to execute this query very quickly following the query in step 6 to be able to capture the locks before a rollback occurs due to a deadlock.
USE [LockingRepro]; GO DECLARE @ReadOnlySpid INT = nn, -- edit to specify the spids from the other query windows @ReadWriteSpid INT = nn SELECT CASE WHEN request_session_id = @ReadOnlySpid THEN 'ReadOnly' ELSE 'ReadWrite' END AS [Connection], DB_NAME(resource_database_id) AS [Database], resource_type, CASE WHEN resource_database_id = DB_ID() THEN CASE WHEN resource_type = 'OBJECT' THEN OBJECT_SCHEMA_NAME(resource_associated_entity_id) + '.' + OBJECT_NAME(resource_associated_entity_id) WHEN resource_type IN( 'PAGE', 'KEY', 'EXTENT', 'RID', 'HOBT') THEN ( SELECT OBJECT_SCHEMA_NAME(p.[object_id]) + '.' + OBJECT_NAME(p.[object_id]) + '.' + i.[name] + ' (Ptn:' + FORMAT(p.partition_number, '0') + ')' FROM sys.partitions AS p LEFT JOIN sys.indexes AS i ON p.[object_id] = i.[object_id] AND p.index_id = i.index_id WHERE [partition_id] = resource_associated_entity_id ) WHEN resource_type = 'ALLOCATION_UNIT' THEN ( SELECT OBJECT_SCHEMA_NAME(p.[object_id]) + '.' + OBJECT_NAME(p.[object_id]) + '.' + i.[name] + ' (Ptn:' + FORMAT(p.partition_number, '0') + ' Alloc:' + au.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS + ')' FROM sys.partitions AS p INNER JOIN sys.allocation_units AS au ON p.[partition_id] = au.container_id LEFT JOIN sys.indexes AS i ON p.[object_id] = i.[object_id] AND p.index_id = i.index_id WHERE au.allocation_unit_id = resource_associated_entity_id ) END ELSE CAST(resource_associated_entity_id AS VARCHAR(100)) END AS [Entity], resource_associated_entity_id, resource_description, resource_lock_partition, request_mode, request_type, request_status, request_reference_count FROM sys.dm_tran_locks WHERE request_session_id IN (@ReadOnlySpid, @ReadWriteSpid) AND resource_type <> 'DATABASE' ORDER BY request_session_id
Example of the lock query output for the expected behaviour i.e. a deadlock occurs
Step 5 - the ReadWrite connection executes the DELETE using the clustered key - it acquires an X lock on the clustered index key but is blocked waiting to convert its U lock on the non-clustered index key to an X lock because the ReadOnly connection holds an S lock on it
Step 6 - The ReadOnly connection executes the SELECT using the clustered index key and is blocked waiting to acquire an S lock on the clustered index key, which the ReadWrite holds an X lock on, and within a couple of seconds the deadlock is detected
Example of the lock query output for the unexpected behaviour This is the case where the ReadOnly connection SELECT in step 6 returns its result and no deadlock or blocking occurs, even though the clustered index key has an X lock granted on it to the ReadWrite connection.
All the output for steps 1 to 5 were identical to the above expected behaviour example so I won't repeat that.
The query returns the results quickly so I've not been able to capture whether it actually gets a shared lock or not but the query can be executed repeatedly while the X lock on the clustered index is clearly still held by the ReadWrite connection.