SQL Server Blogs

Voices from the UK SQL Server Community
Welcome to SQL Server Blogs Sign in | Help
in Search

My two cents

Tips, opinions, tests (and misspellings) from a Brazilian DBA in UK

How atomic is a SELECT INTO?

Last week I got an interesting situation that prompted me to challenge a long standing assumption. I always thought that a SELECT INTO was an atomic statement, i.e. it would either complete successfully or the table would not be created.

So I got very surprised when, after a “select into” query was chosen as a deadlock victim, the next execution (as the app would handle the deadlock and retry) would fail with:

Msg 2714, Level 16, State 6, Line 1
There is already an object named '#test' in the database.

The only hypothesis we could come up was that the “create table” part of the statement was committed independently from the actual “insert”. We can confirm that by capturing the “Transaction Log” event on Profiler (filtering by SPID0). The result is that when we run:

SELECT * INTO #results FROM master.sys.objects

we get the following output on Profiler:

image

It is easy to see the two independent transactions. Although this behaviour was a surprise to me, it is very easy to workaround it if you feel the need (as we did in this case). You can either change it into independent “CREATE TABLE / INSERT SELECT” or you can enclose the SELECT INTO in an explicit transaction:

SET XACT_ABORT ON
BEGIN TRANSACTION
SELECT * INTO #results FROM master.sys.objects
COMMIT

image

Comments

 

Dave Ballantyne said:

I remeber this behaviour being introduced into 6.5 (i *think*) .

Basically , it was an atomic operation but due to the page level locking if you had processes creating temp tables they would be blocked behind your potentially slow running Select statement.    For additional fun,  Drops of temp table used to deadlock with creates due to the order that locks where acquired in.

I would imagine that it could be now returned to an atomic operation in the engine.

May 24, 2012 2:41 PM
 

Deadlocks involving Select Into « sqlindian – blog Uncommitted said:

Pingback from  Deadlocks involving Select Into «  sqlindian – blog Uncommitted

July 14, 2012 1:54 AM
 

SQL Server said:

In fact there is nothing special about a deadlock involving a SELCT INTO statement except for an interesting

February 7, 2013 10:33 PM
Powered by Community Server (Commercial Edition), by Telligent Systems