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

Advertisements

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