Deadlock due to Implicit Conversion

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.

image

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

Multi-Victim Deadlocks

In some cases, to resolve a deadlock condition, the deadlock monitor may need to identify and rollback multiple transactions. The following demo shows one of such cases.

Setup

Run the below script to create the necessary tables

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

USE DeadLockTest
GO
CREATE TABLE TabA(id int IDENTITY(1,1), col1 char(100) DEFAULT ‘abc’)
CREATE TABLE TabB(id int IDENTITY(1,1), col1 char(100) DEFAULT ‘abc’)
CREATE TABLE TabC(id int IDENTITY(1,1), col1 char(100) DEFAULT ‘abc’)

INSERT INTO TabA DEFAULT VALUES
INSERT INTO TabB DEFAULT VALUES
INSERT INTO TabC DEFAULT VALUES

Open a new session(Session 1) and run the below script

SET DEADLOCK_PRIORITY HIGH
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT * FROM TabA A
INNER JOIN TabB B ON A.id = B.Id
INNER JOIN TabC C ON C.id = B.Id

Open another session(Session 2) and run the below script

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT * FROM TabA
UPDATE TabA SET Col1 = ‘xyz’

Open another session(Session 3) and run the below script

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT * FROM TabB
UPDATE TabB SET Col1 = ‘xyz’

Open one final session(Session 4) and run the below script

SET DEADLOCK_PRIORITY NORMAL
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT * FROM TabC
UPDATE TabC SET Col1 = ‘xyz’

Session 2,3, and 4 will be blocked behind session 1 for the Exclusive lock. Now go back to Session 1 and execute the below script to force a deadlock condition.

SELECT * FROM TabA  A WITH(XLOCK)
INNER JOIN TabB B WITH(XLOCK)   ON A.id = B.Id
INNER JOIN TabC C WITH(XLOCK)  ON C.id = B.Id

ROLLBACK TRAN

In few seconds Sessions 2,3, and 4 will fail with the deadlock error.

Analyze

We are running the first session with a higher deadlock priority compared to other sessions. A deadlock victim is selected based the priority, log used, and  its ability to resolve all deadlock conditions. If we set the DEADLOCK PRIORITY to NORMAL for the first session, SQL server will choose it as the deadlock victim since choosing a single victim will resolve all deadlock conditions.

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

Deadlocks involving Select Into

In fact there is nothing special about a deadlock involving a SELCT INTO statement except for an interesting ROLLBACK behavior. Follow the below demo to find out.

Setup

Run the below script to create the necessary tables.

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

USE DeadLockTest
GO

CREATE TABLE dbo.sidl1(id int IDENTITY(1,1) PRIMARY KEY, filler char(100) default ‘abc’)
GO
CREATE TABLE dbo.sidl2(id int IDENTITY(1,1) PRIMARY KEY, filler char(100) default ‘abc’)
GO

INSERT INTO dbo.sidl1 DEFAULT VALUES

INSERT INTO dbo.sidl2 DEFAULT VALUES

Now open a new session (session 1) and execute the below statements

SET DEADLOCK_PRIORITY HIGH
BEGIN TRAN
UPDATE  dbo.sidl2
SET Filler = ‘xyz’
WHERE id = 1

Open another session (session 2) and execute the below SELECT INTO statement.

SELECT a.*
INTO dbo.sidl3
FROM dbo.sidl1 a
INNER JOIN dbo.sidl2 b
ON a.id = B.id

This session will be blocked since Session one is already holding a non-compatible lock on table sidl2. Now go back to session 1 and execute the below statement to force a deadlock condition.

UPDATE  dbo.sidl1
SET Filler = ‘xyz’
WHERE id = 1

ROLLBACK TRAN

The Session running the SELECT INTO statement (Session 1) will fail with a deadlock error since we are running the first session with a higher deadlock priority.

If you try to rerun the SELECT INTO statement, you will get the below error.

Msg 2714, Level 16, State 6, Line 1
There is already an object named ‘sidl3’ in the database.

Analyze

You can see that even though the transaction failed with a deadlock error, the table creation is not rolled back. This is due to the fact that when you execute a SELECT INTO statement, the table creation and population are treated as two different transactions. Read this post to learn more about inspecting this behavior.

Fix

Place the SELECT INTO in an explicit user transaction

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

Deadlock Due to Foreign Key Constraints

Another common deadlock pattern is a deadlock involving foreign key validations. This can happen in any of the following scenarios.

1. An Insert parent-Insert child operation deadlocking with another thread performing the same operation

2. A Delete child-Delete parent operation deadlocking with another thread performing the same operation

3. An Insert parent-Insert child operation deadlocking with a Delete parent-Delete child operation.

SQLCat team published a whitepaper on handling this type of deadlocks. The whitepaper discusses various techniques to avoid this kind of deadlocks. Follow this link to read the whitepaper Eliminating Deadlocks Caused By Foreign Keys with Large Transactions

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

Deadlocks due to Hash Collision

Apart from discussing about troubleshooting a rare deadlock condition, James Rowland-Jones, in one of his most popular articles, illustrates a lot of fundamentals about deadlock troubleshooting and SQL server lock manager in his post The Curious Case of the Dubious Deadlock and the Not So Logical Lock

I strongly recommend you to read this relatively lengthy article and also follow the comments in the post.

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

Deadlocks due to Partition Level Lock Escalation

SQL server Guru Paul Randal (b | t) explains how enabling partition level lock escalation can cause deadlocks in the post Partition level lock escalation – details and examples.

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

Deadlock due to Parallelism

 

Do you think a simple query like the below one can deadlock with another instance running the same query?

SELECT @id = id FROM dbo.midl WITH(UPDLOCK)
WHERE id2 = @id

Read on to find out.

Setup

Since this post is about deadlock due to parallelism, make sure that parallelism is enabled on your server.

Run the below script to create the table.

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

CREATE TABLE dbo.midl(id int identity(1,1) primary key clustered, id2 int, filler char(100) default ‘abc’ )

SET NOCOUNT ON

DECLARE @i int
SET @i = 1
WHILE @i < 60000
BEGIN
INSERT INTO dbo.midl DEFAULT VALUES
SET @i = @i+1
END

UPDATE dbo.midl SET id2 = id+1

Now run the following query in multiple sessions or use a tool like SQLQueryStress developed by Adam Machanic (b | t) which can be used for executing a query in multiple sessions.

SET NOCOUNT ON

DECLARE @id int
WHILE 1=1
BEGIN
SET @id = (RAND() * 1000) + 1
SELECT @id = id FROM dbo.midl WITH(UPDLOCK)
WHERE id2 = @id
END

In my tests with five concurrent sessions, three of them deadlocked under a minute.

The queries failed with the error

Msg 1205, Level 13, State 51, Line 6

Transaction (Process ID 60) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Analyze

If you had paid close attention to the error message, it says the deadlock happened on lock | communication buffer resources, which is a hint about the actual issue here. If you pay close attention to the resource list in the deadlock trace you can see the exchange operators appearing in the waiter list.

exchangeEvent id=Pipe8ac26500 WaitType=e_waitPipeGetRow nodeId=1

Deadlocks involving parallelism always involve thread or lock resources. So we can proceed in our conventional route and prepare our deadlock table.

I had simplified the below table to include only two process instead of all four participants in the deadlock.

Process Resource Mode State Command Object
process579948 KEY: 26:72057594039500800 (8194443284a0) U WAIT SELECT indexname=PK__midl__X
process579948 KEY: 26:72057594039500800 (1524a5133a9d) U GRANT SELECT indexname=PK__midl__X
process81e714c8 KEY: 26:72057594039500800 (1524a5133a9d) U WAIT SELECT indexname=PK__midl__X
process81e714c8 KEY: 26:72057594039500800 (8194443284a0) U GRANT SELECT indexname=PK__midl__X

 

This only tells us that the contention is on the PK. We need to see what keys are being actually involved. We can get this using the undocumented %%lockres%% function.

By running the below query,

SELECT id, %%lockres%% as LockResource
FROM dbo.midl
WHERE %%lockres%% IN(‘(8194443284a0)’,'(1524a5133a9d)’)

Id LockResource
1 (8194443284a0)
12709 (1524a5133a9d)

 

Now we can see that, process81e714c8 is holding a lock on row with id 1 and is waiting for a lock on id 12709. This is OK since it is a normal blocking scenario. But process579948 is holding a lock on row with id 12709 and waiting for a lock on id 1. If we look at the execution plan, we can see why.

 

image

When a task is executed using a parallel operator, the input is divided into a number of parallel buckets and each parallel thread works on its own subset of data.

Fix

This is again an easy fix. You have probably noticed that the id2 column does not have an index. If you create a non-clustered index on the id2 column, the deadlock will disappear.

PS: If you did not get the deadlock error, try adding few more thousands of rows and/or few more concurrent processes running the same query.

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

 

Deadlock due to Different Lock granularity

A deadlock can occur when a table is accessed with different lock granularity  in the same transaction. The following script demonstrates the concept.

Setup

Run the following script to create the necessary table.

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

USE DeadLockTest
GO
CREATE TABLE dbo.lgdl(id int identity(1,1) primary key clustered, filler char(100) default ‘abc’)
GO

INSERT INTO lgdl DEFAULT VALUES
GO
INSERT INTO lgdl DEFAULT VALUES
GO

Now open a new session (session 1) and run the below script.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN

SELECT * FROM lgdl WITH(ROWLOCK) WHERE id = 1

WAITFOR DELAY ’00:00:05′

UPDATE l
SET filler = ‘xyz’
FROM lgdl L WITH(PAGLOCK)
WHERE id =1

ROLLBACK TRAN

Open another session (session 2)  and run the below script.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN

SELECT * FROM lgdl WITH(ROWLOCK) WHERE id = 2

WAITFOR DELAY ’00:00:05′

UPDATE l
SET filler = ‘xyz’
FROM lgdl L  WITH(PAGLOCK)
WHERE id =2

ROLLBACK TRAN

Analyze

Lets build the deadlock analysis table from the trace results.

Process Resource Mode State Command Object
process7ed948 PAGE: 26:1:2898 U WAIT UPDATE dbo.lgdl
process7ed948 PAGE: 26:1:2898 IS GRANT UPDATE dbo.lgdl
process44f708 PAGE: 26:1:2898 X WAIT UPDATE dbo.lgdl
process44f708 PAGE: 26:1:2898 U GRANT UPDATE dbo.lgdl

So what is happening here is, both the processes will start a S lock on the individual keys and an IS lock on the page. Since we explicitly specified  PAGLOCK in the update statement both transactions will need to acquire locks at the page level for the UPDATE statement. SQL server acquires locks for updates in two phases. First it will acquire a U lock for the read phase of the Update and then an X lock for the Update. So when the first transaction tries to acquire a U lock on the page, it will succeed because a U lock is compatible with an IS lock. However, when it tries to convert the U lock to an X lock, it will be blocked since an X lock is not compatible with an IS lock.

Meanwhile when the second the second transaction tries to convert the IS lock to a U lock, it will be blocked because a U lock is not compatible with another U lock. And when two transactions block each other the result is a deadlock.

For details on SQL server lock compatibility, please refer to this entry on MSDN.

Fix

Make sure a table is accessed using the same lock granularity inside a transaction especillly when using lock granularity hints.

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

Deadlock due to Savepoint Rollback behavior

Today I want to introduce you to a specific behavior about how locks are rolled back when you rollback a transaction to a Savepoint and how it can cause a deadlock.

Setup

Run the below script to create the necessary tables.

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

USE DeadLockTest
GO

CREATE TABLE dbo.spdl1(id int identity(1,1) primary key clustered, status char(20))
CREATE TABLE dbo.spdl2(id int identity(1,1) primary key clustered, status char(20))
GO
SET NOCOUNT ON

INSERT INTO dbo.spdl1 DEFAULT VALUES
INSERT INTO dbo.spdl2 DEFAULT VALUES

Now open a new session (session 1) and run the below script.

USE DeadLockTest
GO

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

BEGIN TRAN

SELECT * FROM dbo.spdl1 WHERE id = 1

SAVE TRANSACTION SP;

SELECT * FROM dbo.spdl2 WHERE id = 1

UPDATE dbo.spdl2 SET Status = ‘In Progress’
UPDATE dbo.spdl1 SET Status = ‘In Progress’

–Something went wrong.
–Rollback to the save point

ROLLBACK TRANSACTION SP;

Note that we performed two update statements and rolled it back. Open a second session (session 2) and run the below script.

USE DeadLockTest
GO

SET DEADLOCK_PRIORITY LOW

BEGIN TRAN

UPDATE dbo.spdl2 SET Status = ‘In Progress’ WHERE id = 1

The update will succeed.  Now run the below statement in the same window (session 2)

SELECT * FROM dbo.spdl1 WHERE id = 1

The above statement will be (surprisingly) blocked. Now go back to Session 1 and run the below script to cause a deadlock condition.

UPDATE dbo.spdl2 SET Status = ‘Failed’

The second session will now fail with the deadlock error.

Analyze

To see what is happening here, lets build the deadlock analysis table.

Process Resource Mode State Command Object
process6dd708 KEY: 26:72057594042122240 (8194443284a0) S WAIT SELECT indexname=PK__spdl1
process6dd708 KEY: 26:72057594042253312 (8194443284a0) X GRANT UPDATE indexname=PK__spdl2
process6c2e08 KEY: 26:72057594042253312 (8194443284a0) U WAIT UPDATE indexname=PK__spdl2
process6c2e08 KEY: 26:72057594042122240 (8194443284a0)  X GRANT SELECT ? indexname=PK__spdl1

As you can see above the process process6c2e08, which executed two update statements and rolled it back is still holding an exclusive lock (X) on one table, but obviously it released the exclusive lock on the other table, because the second session was able to acquire an X lock on that table and the UPDATE statement succeeded.

Couple of things to note here is that we are running Session 1 in REPEATABLE READ isolation level so we expect the shared locks to be held until the end of the transaction. The first SELECT statement is before the SAVE TRANSACTION statement and the second SELECT statement is after the SAVE TRANSACTION statement. So both these SELECT statements will first acquire Shared locks. When the update statements are executed, both the Shared locks will be converted to Exclusive locks. When we hit the ROLLBACK Savepoint, the ideal behavior is

1. Release the exclusive lock on the second table.

2. Downgrade the Exclusive lock on the first table to Shared Lock since the SELECT statement is outside the Savepoint.

But SQL Server does not undo a lock conversion by changing the lock mode to the previous mode existed before the Savepoint. So in our case, the exclusive lock on the first table will be held even if we rollback the UPDATE Statements.

IF you query the DMV sys.dm_tran_locks after the ROLLBACK savepoint statement, you can see that the process is still holding the exclusive lock.

Fix

Just move the first select statement below the SAVE TRANSACTION statement!

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