sqlindian – On Locks and Deadlocks

Random thoughts on performance and concurrency

Deadlock due to Implicit Conversion

leave a comment »

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.


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

USE DeadLockTest

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


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

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.



WHERE Status = ‘Pending’


WAITFOR DELAY ’00:00:05′

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


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


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.


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

Written by sqlindian

August 10, 2012 at 1:11 AM