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