CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008transactionsrdbms
Marc.2377
Marc.2377

Reputation: 8714

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

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?

Upvotes: 3

Views: 937

Answers (2)

Vitaly Borisov
Vitaly Borisov

Reputation: 1193

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.

Upvotes: 2

SQLApostle
SQLApostle

Reputation: 590

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

Upvotes: 3

Related Questions