Deadlock due to Implicit Conversion

Jes Schultz Borland (b|t) from Brent Ozar PLF recently blogged about Identifying and Correcting SQL Server Implicit Conversion. Jes’s post discuss about how data type mismatches on the join and filter predicates cause performance issues and how to resolve it.

In this post I am going to demonstrate how Implicit conversion can lead to deadlock errors and how to troubleshoot and fix them.

Setup

For this demo, we will use a standard queue reader pattern, which reads a single row from a table, perform some operations based on it and finally update the row.

First we need to create the table and populate it.

IF DB_ID(‘DeadLockTest’) IS NULL
CREATE Database DeadLockTest
GO

USE DeadLockTest
GO

CREATE TABLE dbo.icdl(SlNo VARCHAR(10) primary key clustered,
status varchar(12),
filler char(100))
GO

SET NOCOUNT ON

DECLARE @i int
SET @i = 1
WHILE @i < 1000
BEGIN
INSERT INTO dbo.icdl(SlNo, status,filler)
VALUES(RIGHT(REPLICATE(‘0′,10)+ CAST(@i AS VARCHAR(10)),10),’Pending’, ‘Abcd’)
SET @i = @i+1
END

Now run the below query simultaneously in 2 sessions. Couple of things to note about the script is that, it uses TOP 1 to get a single row. It also uses the hints (ROWLOCK, UPDLOCK,READPAST) which ensures that the script will not be blocked by another instance and it places a U lock at the key level.  Also the final UPDATE is performed using an equality predicate on the primary key.

DECLARE @SlNo NVARCHAR(10)

BEGIN TRAN

SELECT TOP 1 @SlNo = SlNo
FROM dbo.icdl WITH(UPDLOCK, ROWLOCK, READPAST)
WHERE Status = ‘Pending’
ORDER By SlNo

SELECT @slNo

WAITFOR DELAY ’00:00:05′

UPDATE dbo.icdl
SET Status = ‘Processed’
WHERE SlNo = @slNo

COMMIT TRAN

In five seconds, one of the session will fail with a deadlock error.

Analyze

As usual let’s build the deadlock table to analyze what is happening here.

Process	Resource	Mode	State	Command
process43b34c8	KEY: 26:72057594044678144 (fff536a51673)	U	WAIT	UPDATE
process43b34c8	KEY: 26:72057594044678144 (c3d783fe2889)	X	GRANT	UPDATE
process406f921	KEY: 26:72057594044678144 (c3d783fe2889)	U	WAIT	UPDATE
process406f921	KEY: 26:72057594044678144 (fff536a51673)	U	GRANT	SELECT

This tells us that the contention is on two individual keys of the same HOBT. Lets see which rows these keys belong to.

SELECT %%lockres%% AS LockResource, Slno
FROM dbo.icdl
WHERE %%lockres%% IN(‘(fff536a51673)’,'(c3d783fe2889)’)

LockResource	Slno
(c3d783fe2889)	0000000001
(fff536a51673)	0000000002

Our script is designed to work on individual rows alone. We expect the SELECT statement to acquire a U lock on the row and the UPDATE statement to convert the U lock to X lock and finish the processing without requiring locks on any other rows.

But what actually happening here is Process 1 is waiting for an UPDATE lock on the second row (0000000002) and holding an EXCLUSIVE lock the first row (0000000001) . Whereas Process1 is holding an UPDATE lock on the second row (0000000002) , which is acquired during the SELECT phase, and is waiting for UPDATE lock on the first row (0000000001).

At this stage, we will need to look at the execution plan to see why the UPDATE command requires locks on rows belonging to another key value.

The execution plan reveals that there is an IMPLICIT CONVERSION on the Slno column and as a result, we are getting a Clustered Index scan even though we are using an equality predicate on the  Clustered index key column. If you look at the script, you can see that the column is defined as VARCHAR(10) whereas the variable is defined as NVARCHAR(10). As per the data type precedence rules,  when we compare a VARCHAR value to an NVARCHAR value, the VARCHAR value will be implicitly converted to NVARCHAR.

Fix

Once we know the real issue, its an easy fix. You can just change the data type of the variable to match the type of the table. If you are joining two tables with different data types, and you cannot change the type, it will be good idea to explicitly convert the value in the smaller table to match the type of the larger table to ensure efficient index utilization.

Note: The Queue reader pattern used in this post is also vulnerable to this deadlock scenario.

This post is part of the series SQL Server deadlocks and live locks – Common Patterns. If you have questions, please leave a comment here or reach me at @sqlindian