XACT_ABORT doesn't always rollback the transaction on error. When does it do it exactly?

Question

Question

The documentation of SET XACT_ABORT says little more than this about the effect of enabling this option.

When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

I do not believe this is the full truth. After reading this, I was worried that if a stored procedure which enables this option is executed in a transaction created by an external process, it may end up rolling back the outer transaction. Luckily, my fears turned out to be unfounded. However, this means now that I do no truly understand how XACT_ABORT works. What are the conditions SQL Server checks for whether a transaction should be rolled back or not?

Prior investigation

I have carried out the following experiment: (a summary of this code is below, as having a numbered list before a code block breaks StackOverflow's formatting, duh)

CREATE TABLE Dummy
(
    ID INT NOT NULL IDENTITY CONSTRAINT PK_Dummy PRIMARY KEY,
    Text NVARCHAR(128) NOT NULL
)

CREATE UNIQUE NONCLUSTERED INDEX IX_Dummy_Text ON dbo.Dummy(Text)

GO 

CREATE OR ALTER PROCEDURE InsertDummy
    @Text NVARCHAR(128)
AS
BEGIN
    SET NOCOUNT OFF
    SET XACT_ABORT ON

    INSERT dbo.Dummy (Text) VALUES (@Text)
END

GO

SET XACT_ABORT ON

BEGIN TRANSACTION
BEGIN TRY
    EXEC dbo.InsertDummy @Text = N'Dummy'
    EXEC dbo.InsertDummy @Text = N'Dummy' --DUPLICATE!
END TRY
BEGIN CATCH
    PRINT 'ERROR! @@TRANCOUNT is ' + CONVERT(NVARCHAR, @@TRANCOUNT)

    -- Echo the error
    DECLARE @ErrorMessage NVARCHAR(4000);  
    DECLARE @ErrorSeverity INT;  
    DECLARE @ErrorState INT;  

    SELECT @ErrorMessage = ERROR_MESSAGE();  
    SELECT @ErrorSeverity = ERROR_SEVERITY();  
    SELECT @ErrorState = ERROR_STATE();  

    RAISERROR (@ErrorMessage, -- Message text.  
                @ErrorSeverity, -- Severity.  
                @ErrorState -- State.  
                );  
END CATCH

PRINT 'At the end @@TRANCOUNT is ' + CONVERT(NVARCHAR, @@TRANCOUNT)
IF @@TRANCOUNT>0
    ROLLBACK

Create a Dummy table with a UNIQUE index
A stored procedure which inserts into Dummy. The procedures enables XACT_ABORT.
Code which executes this procedure twice, in a transaction. The second call fails, as it attempts to insert a duplicate value into Dummy.
The same code prints out the @@TRANCOUNT value to show if we are still in a transaction or not. It also enables XACT_ABORT.

The output of this test is:

(1 row affected)

(0 rows affected)
ERROR! @@TRANCOUNT is 1
Msg 50000, Level 14, State 1, Line 74
Cannot insert duplicate key row in object 'dbo.Dummy' with unique index 'IX_Dummy_Text'. The duplicate key value is (Dummy).
At the end @@TRANCOUNT is 1

An error was raised yet the the transaction not rolled back. The way this setting works is clearly not as simplistic as the documentation would have me believe. Why was the transaction not rolled back?

This answer mentions that XACT_ABORT only rolls back the transaction if the severity of the error is at least 16. The error in this example is only level 14. However, even if I replace the INSERT in the procedure with RAISERROR (N'Custom error', 16, 0), the transaction is still not rolled back.

UPDATE: What I found that although the transaction is not rolled back in my test, it is doomed! @@TRANCOUNT is 1 when I execute this sample regardless of the XACT_ABORT setting: but if the setting is ON, XACT_STATE() is -1, indicating an uncomittable transaction. When XACT_ABORT is OFF, XACT_STATE() is 1.

FYI, you have nested transactions in those statements, which are a myth. — Larnu
I recommend reading all of this. Error handling in T-SQL is quite involved, and you won't get the full story from the documentation. — Jeroen Mostert
@Larnu Well, actually I don't: the procedure in this sample will only create a savepoint. Sorry, maybe I should have simplified the sample by removing this conditional logic but I wanted to stick to the pattern suggested in SAVE TRANSACTION docs and what is used by my actual production code procedure. — kamilk
You do in your sample, @kamilk . YOu start off with BEGIN TRANSACTION then follow on with EXEC dbo.InsertDummy. Inside dbo.InsertDummy you have a further BEGIN TRANSACTION;. Thus nested transactions. — Larnu
@Larnu The second BEGIN TRANSACTION won't be executed as @TranCounter will be equal to 1. — kamilk

Esat Erkec Esat Erkec · Accepted Answer · 2020-02-21T20:07:46

Question "An error was raised yet the transaction not rolled back. The way these setting works is clearly not as simplistic as the documentation would have me believe. Why was the transaction not rolled back"

The answer to that is that RAISERROR will not cause XACT_ABORT to trigger! This means we can be in a very messed up state transaction wise Abort, Abort, We Are XACT_ABORT:ing, Or Are We?!

According to MSDN ,

The THROW statement honors SET XACT_ABORT. RAISERROR does not. New applications should use THROW instead of RAISERROR.

We can use the THROW statement instead of the RAISERROR. So we can use the following statement in order to trigger the XACT_ABORT

TRUNCATE TABLE Dummy
GO
SET XACT_ABORT ON

BEGIN TRANSACTION
BEGIN TRY
    EXEC dbo.InsertDummy @Text = N'Dummy'
    EXEC dbo.InsertDummy @Text = N'Dummy' --DUPLICATE!
END TRY
BEGIN CATCH
THROW
END CATCH

PRINT 'At the end @@TRANCOUNT is ' + CONVERT(NVARCHAR, @@TRANCOUNT)
IF @@TRANCOUNT>0
    ROLLBACK

The output will be;

(1 row affected)

(0 rows affected)
Msg 2601, Level 14, State 1, Procedure dbo.InsertDummy, Line 7 [Batch Start Line 5]
Cannot insert duplicate key row in object 'dbo.Dummy' with unique index 'IX_Dummy_Text'. The duplicate key value is (Dummy).

For the updated issue you can see set xact_abort on and try-catch together

XACT_ABORT doesn't always rollback the transaction on error. When does it do it exactly?

Question

Prior investigation

1 Answers