SQL Server deadlock due to clustered index scan (where clustered index seek is better) and unnecessary LCK_M_U

Question

This question was migrated from Stack Overflow because it can be answered on Database Administrators Stack Exchange. Migrated 5 days ago.

This is a pretty mundane transaction isolation scenario on a OLTP database (Microsoft SQL Server 2022).

Session A and Session B are running concurrently, and this works pretty much as you would expect. However when the same workload is run using prepared statements, it results in a deadlock.

Schema:

CREATE TABLE [dbo].[T]
(
 [i] [varchar](64) NOT NULL,
 [j] [int] NULL,
 CONSTRAINT [T_i] 
 PRIMARY KEY CLUSTERED ([i] ASC)
 WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
 IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
 ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Session A:

BEGIN TRANSACTION 
WAITFOR DELAY '00:00:10' -- 5 seconds
PRINT 'Log Message: ' + CONVERT(VARCHAR, GETDATE(), 120) + ' A:1';
INSERT INTO T VALUES ('Acct-1', 0), ('Acct-2', 0);
WAITFOR DELAY '00:00:10' -- 5 seconds
PRINT 'Log Message: ' + CONVERT(VARCHAR, GETDATE(), 120) + ' A:2';
UPDATE T 
SET j = 1 
WHERE i IN ('Acct-1', 'Acct-2');
PRINT 'Log Message: ' + CONVERT(VARCHAR, GETDATE(), 120) + ' A:3';
ROLLBACK

Session B:

BEGIN TRANSACTION 
PRINT 'Log Message: ' + CONVERT(VARCHAR, GETDATE(), 120) + ' B:1';
INSERT INTO T VALUES ('Acct-3', 0), ('Acct-4', 0);
WAITFOR DELAY '00:00:15' -- 15 seconds
PRINT 'Log Message: ' + CONVERT(VARCHAR, GETDATE(), 120) + ' B:2';
UPDATE T 
SET j = 1 
WHERE i IN ('Acct-3', 'Acct-4');
PRINT 'Log Message: ' + CONVERT(VARCHAR, GETDATE(), 120) + ' B:3';
ROLLBACK

Shows the correct clustered index seek operator

However, the following two sessions result in a deadlock, even though it is logically equivalent to the above scenario.

Session A:

BEGIN TRANSACTION 
DECLARE @Parameter_1 VARCHAR(20)
DECLARE @Parameter_2 VARCHAR(20)
DECLARE @sql_1 nvarchar(4000)
DECLARE @sql_2 nvarchar(4000)
SET @sql_1 = N'INSERT INTO T VALUES (@Parameter_1, 0), (@Parameter_2, 0);'
SET @sql_2 = N'UPDATE T SET j = 1 WHERE i IN (@Parameter_1, @Parameter_2);'
WAITFOR DELAY '00:00:10' -- 5 seconds
PRINT 'Log Message: ' + CONVERT(VARCHAR, GETDATE(), 120) + ' A:1';
EXEC sp_executesql @sql_1, N'@Parameter_1 VARCHAR(20), @Parameter_2 VARCHAR(20)', N'Acct-1', N'Acct-2'
WAITFOR DELAY '00:00:10' -- 5 seconds
PRINT 'Log Message: ' + CONVERT(VARCHAR, GETDATE(), 120) + ' A:2';
EXEC sp_executesql @sql_2, N'@Parameter_1 VARCHAR(20), @Parameter_2 VARCHAR(20)', N'Acct-1', N'Acct-2'
PRINT 'Log Message: ' + CONVERT(VARCHAR, GETDATE(), 120) + ' A:3';
ROLLBACK;

Session B:

BEGIN TRANSACTION 
DECLARE @Parameter_1 VARCHAR(20)
DECLARE @Parameter_2 VARCHAR(20)
DECLARE @sql_1 nvarchar(4000)
DECLARE @sql_2 nvarchar(4000)
SET @sql_1 = N'INSERT INTO T VALUES (@Parameter_1, 0), (@Parameter_2, 0);'
SET @sql_2 = N'UPDATE T SET j = 1 WHERE i IN (@Parameter_1, @Parameter_2);'
PRINT 'Log Message: ' + CONVERT(VARCHAR, GETDATE(), 120) + ' B:1';
EXEC sp_executesql @sql_1, N'@Parameter_1 VARCHAR(20), @Parameter_2 VARCHAR(20)', N'Acct-3', N'Acct-4'
WAITFOR DELAY '00:00:15' -- 15 seconds
PRINT 'Log Message: ' + CONVERT(VARCHAR, GETDATE(), 120) + ' B:2';
EXEC sp_executesql @sql_2, N'@Parameter_1 VARCHAR(20), @Parameter_2 VARCHAR(20)', N'Acct-3', N'Acct-4'
PRINT 'Log Message: ' + CONVERT(VARCHAR, GETDATE(), 120) + ' B:3';
ROLLBACK;

This is the resulting error message:

Msg 1205, Level 13, State 51, Line 1
Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

The problem appears to be that in the first case, a clustered index seek is used, but in the second (deadlock) case, a clustered index scan occurs. The scan requires a LCK_M_U lock on a clustered key range, resulting in the deadlock.

DeadLock, shows spid and blocked processes

Shows the incorrect clustered index scan operator

Questions

1. How to force SQL Server to use a seek (not scan), given that is already does so in an identical case?

2. Why is the SQL Server optimizer making this poor choice in the case that is only syntactically different and not logically different?

• Sumeet Keswani: I forgot to mention that i did try this by invalidating plan cache (i.e. each query was planned independently), just to make sure that plan caching was not the root cause. However, the problem still persisted.
• Thom A: If you need to make additions to your question, please use the edit feature.
• allmhuran: See Forceseek but note all of the remarks and warnings. Also, you can't really say the scan is "incorrect". SQL will pick what it thinks is the "best" plan. There are situations where a scan is "better" (cheaper) than a seek.

Answer 1

When the query is using literals SQL Server can perform any deduplication at compile time.

For example the below still gives an execution plan with a single seek operator with two seek operations

UPDATE T 
SET j = 1 
WHERE i IN ('Acct-3','Acct-3', 'Acct-4');

When the query is parameterised SQL Server can't just assume the parameter values are different and compile a plan with two seek operations for @Parameter_1 and @Parameter_2 (potentially sending the same row upstream multiple times) so in this case it opts to perform a clustered index scan instead.

You can get a plan which performs this deduplication at runtime (via a Merge interval operator) and performs a seek if you do

UPDATE T
SET j = 1
FROM T WITH (FORCESEEK)
WHERE i IN ( @Parameter_1, @Parameter_2 ); 

If the table had more rows and there was a clear cost benefit to using multiple seek operations vs a single scan the above plan would be chosen naturally without any hints.

The OPTION (RECOMPILE) hint could also be used as an alternative to allow SQL Server to use the parameter embedding optimisation and compile a plan for the parameter values in use each time as though they were literals (but that still wouldn't necessarily guarantee you a seek and you pay a recompilation cost on each execution).

• Sumeet Keswani: Thank you for a very detailed comment and analysis. We tried the solution and it works perfectly.