How atomic is a SELECT INTO?
- by leo.pasta
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:
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
