When does SQL server roll back an erroneous transaction?

Question

I have been looking into these two questions:

SQL Server - transactions roll back on error?

From my research, I came to the conclusion that queries should be written like this:

set XACT_ABORT ON
begin tran FB5773_1
begin try
    -- Do some changes
    commit tran FB5773_1;
end try
begin catch
    IF EXISTS (SELECT [name] FROM sys.dm_tran_active_transactions WHERE name = 'FB5773_1')
        rollback tran FB5773_1;
end catch

However, I wonder why are transactions like this

begin tran FB5773_1
--do some stuff
commit tran FB5773_1;

or this

set XACT_ABORT ON
begin tran FB5773_1
--do some stuff
commit tran FB5773_1;

not rollbacking in some cases on error and what are the cases when they are not rollbacking?

transactions like that make all of the individual statements in the transaction an atomic whole. They ALL succeed, or they're all failure. Not everything needs to explicitly rolled back, but you do sometimes need everything to be isolated WHILE you're working on individual smaller chunks of that greater whole. — Marc B, Sep 09 '16 at 16:52
I expected a transaction to be rolled back if there is an error before committing it, however, someone very knowledgable about transactions told me this is not always the case and pointed me to a url ( http://stackoverflow.com/questions/1749719/sql-server-transactions-roll-back-on-error ), where an answer upvoted 114 times tells us that "You can put set xact_abort on before your transaction to make sure sql rolls back automatically in case of error.". As a result, the behavior is unclear for me. — Lajos Arpad, Sep 09 '16 at 16:56
you don't want auto-rollbacks in some cases, because you may DEPEND on there being an error to decide a course of action. if things were auto-rollbacked, you couldn't do that branching decision. — Marc B, Sep 09 '16 at 16:59
@MarcB, the error needs to be inside a try to enable us to write a catch for it. If nothing catches the error, then it is still not rollbacking. — Lajos Arpad, Sep 09 '16 at 17:01

btberry · Accepted Answer · 2016-09-10T00:26:44.290

I think the answer you are looking for can be found in Microsoft's documentation on XACT_ABORT and I will try to enhance that with some examples and other resources. Before we start, a few tables to play with to test what happens under different scenarios:

CREATE TABLE _key
    ( id INT PRIMARY KEY CLUSTERED );
GO

INSERT INTO _key VALUES (1), (2), (3);
GO

CREATE TABLE _table1
    ( table1id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, id INT NOT NULL );
GO

ALTER TABLE dbo._table1
    ADD CONSTRAINT FK_table1 FOREIGN KEY (id) REFERENCES dbo._key (id);
GO

CREATE TABLE _table2
    ( table2id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, id INT NOT NULL );
GO

ALTER TABLE dbo._table2
    ADD CONSTRAINT FK_table2 FOREIGN KEY (id) REFERENCES dbo._key (id);
GO

CREATE PROCEDURE _proc
AS
BEGIN
    SET XACT_ABORT ON;
    BEGIN TRANSACTION;
        INSERT INTO dbo._table1 (id) VALUES(1); --valid insert
        DECLARE @query NVARCHAR(MAX) = N'SELECT ** FROM dbo._table1;'
        EXEC (@query); --will result in compile error
    COMMIT TRANSACTION;
    RETURN 0;
END
GO

Also, between each example I clear out the tables to avoid any doubt about what just happened:

TRUNCATE TABLE _table1;
TRUNCATE TABLE _table2;

I do not think there is confusion about the result of SET XACT_ABORT OFF, which is the default state, but let's go over an example just to be clear ...

SET XACT_ABORT OFF; --this command has no practical effect
BEGIN TRANSACTION;
    INSERT INTO dbo._table1 (id) VALUES(1); --valid insert
    INSERT INTO dbo._table2 (id) VALUES(4); --invalid insert
COMMIT TRANSACTION;

When we run this in SSMS we see an error:

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_table2". The conflict occurred in database "db", table "dbo._key", column 'id'.

When we query to see what is in _table1, we see the record that was inserted and subsequently committed because XACT_ABORT is not on. That result makes sense because we are not aborting but the real question is how SQL Server behaves when that option is turned on. Now the same query with a different abort setting:

SET XACT_ABORT ON;
BEGIN TRANSACTION;
    INSERT INTO dbo._table1 (id) VALUES(1); --valid insert
    INSERT INTO dbo._table2 (id) VALUES(4); --invalid insert
COMMIT TRANSACTION;

We see the same error in SSMS, but this time there are no records inserted into _table1 because the whole batch got rolled back when SQL Server hit the run-time error. And that is the key phrase from MS's documentation:

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

Compile errors, such as syntax errors, are not affected by SET XACT_ABORT.

(Also, I used multiple tables to expose and address the myth that XACT_ABORT will not roll back statements if inserts or updates were made on tables different from the ones referenced in the statement that generates the error. That claim is unfounded and untrue. All statements in the transaction are rolled back regardless of the table.)

So then the big question is: when would the entire batch not be rolled back despite the XACT_ABORT being set on? Erland Sommarskog has addressed this question much more succinctly (and considerably earlier) that I could have. The known conditions under which XACT_ABORT ON does not roll back the batch upon encountering an error are:

Errors you raise yourself with RAISERROR.

Compilation errors (which normally terminate the scope) do not terminate the batch.

Error 266, Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing.

So, for instance:

SET XACT_ABORT ON;
BEGIN TRANSACTION;
    INSERT INTO dbo._table1 (id) VALUES(1); --valid insert
    RAISERROR (N'omg the sky is falling!!!', 16, 1); --my own error
COMMIT TRANSACTION;

This will still result in the record being inserted and committed as my RAISERROR does not trigger the rollback. Likewise, we can run into a syntax error that will not roll back the batch:

EXEC dbo._proc;

This was a really good question and highlights the fact that there is no one-size-fits-all solution to handling errors in SQL Server. XACT_ABORT can be very useful in certain scenarios but developers need to understand potential impact and limitations before relying on the setting to handle rollbacks in all cases.

btberry, thank you, this was a brilliant and eloquent answer. — Lajos Arpad, Sep 10 '16 at 10:42

When does SQL server roll back an erroneous transaction?

1 Answers1