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
– Eric Brandt, Commented Apr 17, 2019 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
– Vitaly Borisov, Commented Apr 17, 2019 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
– Martin Smith, Commented Apr 19, 2019 at 8:56

SQLApostle · Accepted Answer · 2019-04-17 22:29:39Z

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

Marc.2377 · Accepted Answer · 2019-04-24 00:03:24Z

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.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related