Why is my transaction being rolled back automatically when INSERT INTO fails?

Question

Surprisingly, I could not find any relevant explanation or documentation for this issue I'm having.

In the case of these SQL statements:

SELECT 1 AS Test INTO #tmpTest    
BEGIN TRAN    
SELECT 1 AS Test INTO #tmpTest    
ROLLBACK TRAN

When executed one by one, the SELECT ... INTO at line 3 fails, as expected, with message -

There is already an object named '#tmpTest' in the database.

However, after that, the ROLLBACK statement in line 4 fails:

The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

Even though the transaction did BEGIN successfully from line 2.

I've seen SQL Server - transactions roll back on error? but answers don't apply here because the default xact_abort is off. _{In addition, the answer from Quassnoi contradicts the answer by Raj More.}

What's the actual explanation?

Using SQL Server 2016, I added a `SELECT @@TRANCOUNT` before and after the second `SELECT` statement, then executed line by line. The first one returned (as expected) `1`, then the `SELECT` failed, then the second `@@TRANCOUNT` returned `0`. _BUT_, wrap the bad `SELECT` in a try/catch, and there's still an open transaction going into the `CATCH` block. Fascinating. — Eric Brandt, Apr 17 '19 at 22:09
I've managed to get this message: "Msg 3998, Level 16, State 1, Line 1 Uncommittable transaction is detected at the end of the batch. The transaction is rolled back." — Vitaly Borisov, Apr 17 '19 at 22:12
the other answer is more accurate than the one you accepted. Batch aborting is the key to the behaviour you are seeing. — Martin Smith, Apr 19 '19 at 08:56

SQLApostle · Accepted Answer · 2019-04-17T22:29:39.293

Refer to http://www.sommarskog.se/error-handling-I.html

What you are getting is a batch abortion in this case which leads to an implicit rollback. The blog is about SQL Server 2000 error handling, but most of it still remains valid.

Edit: A little more digging and found this which specifically mentions the case of trying to create a table that already exists : http://www.sommarskog.se/error_handling/Part2.html#BatchTranAbort

score 2 · Answer 2 · edited Apr 24 '19 at 00:03

Per this Microsoft article: XACT_STATE (Transact-SQL)

(...) an error has occurred that has caused the transaction to be classified as an uncommittable transaction. The request cannot commit the transaction or roll back to a savepoint; it can only request a full rollback of the transaction.

I run this first:

SELECT 1 AS Test INTO #tmpTest    
SELECT @@TRANCOUNT, XACT_STATE()
BEGIN TRAN    
SELECT @@TRANCOUNT, XACT_STATE()

Then:

BEGIN TRY
    SELECT 1 AS Test INTO #tmpTest    
END TRY
BEGIN CATCH
    SELECT @@ERROR, ERROR_MESSAGE()
    SELECT @@TRANCOUNT, XACT_STATE()
END CATCH

The SELECT in the CATCH block returned: "There is already an object named '#tmpTest' in the database.", @@TRANCOUNT is 1, but XACT_STATE is -1, so the error message in SSMS states:

Msg 3998, Level 16, State 1, Line 1 Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.

The next SELECT @@TRANCOUNT returns 0.

Why is my transaction being rolled back automatically when INSERT INTO fails?

2 Answers2