SQL Server - Transactions Roll Back on Error

SQL Server - transactions roll back on error?

You can put set xact_abort on before your transaction to make sure sql rolls back automatically in case of error.

Rollback SQL transaction without returning an error

/* *** DDL and Test Data *** */
USE tempdb;
GO
SET ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING ON;
GO
CREATE TABLE dbo.Tasks
(
    TaskID int NOT NULL PRIMARY KEY
    ,JobID int NOT NULL
    ,Task varchar(50) NOT NULL
    ,TaskStart date NOT NULL
    ,TaskEnd date NULL
);
GO
INSERT INTO dbo.Tasks
VALUES (1, 1, 'Task1 Job1', '20210901', '20210902')
    ,(2, 1, 'Task2 Job1', '20210902', NULL)
    ,(3, 2, 'Task1 Job2', '20210903', '20210904')
    ,(4, 2, 'Task2 Job2', '20210905', '20210906')
    ,(5, 3, 'Task1 Job3', '20210910', '20210911')
    ,(6, 3, 'Task2 Job3', '20210915', NULL)
    ,(7, 4, 'Task1 Job4', '20210903', '20210904')
    ,(8, 4, 'Task2 Job4', '20210905', '20210906');
GO
CREATE TABLE dbo.Jobs
(
    JobID int NOT NULL PRIMARY KEY
    ,QuotationID uniqueidentifier NULL
    ,Job varchar(50) NOT NULL
);
GO
INSERT INTO dbo.Jobs
VALUES (1, 'F15EAF03-4F45-40E8-85D6-D89B20E00F38', 'Job1')
    ,(2, NULL, 'Job2')
    ,(3, 'F15EAF03-4F45-40E8-85D6-D89B20E00F38', 'Job3')
    ,(4, '3D74F043-2FAC-4C91-8288-13D71C6558C8', 'Job4');
GO
/* *** End DDL and Test Data *** */

/* *** Create Trigger *** */
SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
GO
CREATE TRIGGER dbo.Tasks_TR_U
ON dbo.Tasks
INSTEAD OF UPDATE
AS
BEGIN
    SET NOCOUNT, XACT_ABORT ON;

    UPDATE T
    SET Task = I.Task
        ,TaskStart = I.TaskStart
        ,TaskEnd = I.TaskEnd
    FROM dbo.Tasks T
        JOIN inserted I
            ON T.TaskID = I.TaskID
    WHERE EXISTS
    -- Only QuotationID of NULL allowed
    (
        SELECT 1
        FROM dbo.Jobs J
        WHERE J.JobID = T.JobID
            AND J.QuotationID IS NULL
    )
        -- Only update if any changes
        AND NOT
        (
            T.Task = I.Task
            AND T.TaskStart = I.TaskStart
            AND ISNULL(T.TaskEnd, '1900') = ISNULL(I.TaskEnd, '1900')
        );
END
GO
/* *** End Create Trigger *** */


/* Check data in tables. */
select * from dbo.Tasks;
select * from dbo.Jobs;

/* Run test */
UPDATE dbo.Tasks
SET Task = Task + ' Updated'

/* Only JobId = 2 should be updated. */
select * from dbo.Tasks;

/* Drop Test Tables
DROP TABLE dbo.Tasks;
DROP TABLE dbo.Jobs;
*/

SQL Server Rollback Transaction From Error Outside Transaction

Recall that every Begin Transaction increases @@Trancount by 1 and every Commit decreases it by 1.

In your first example, the transaction is already committed and @@TranCount is reduced to zero before you throw the error, so in the Catch clause, @@Trancount will be zero. This is good. A committed transaction is already history, it can't be rolled back.

In your second example when you raise the error after Begin but before Commit, then @@TranCount is 1 when you reach the Catch clause, and you rollback.

Presumably the behaviour you want is that an error in the remote call should cause a rollback? Which you can achieve by moving the Commit to after the remote call, making it the very last statement before End Try.

Note however that cross-server transactions are relatively expensive, and require MS DTC to be running on both servers. DBAs may frown upon doing that on heavily-loaded servers.

Btw, a transaction around a single insert is usually pointless. Normal behaviour is 'autocommit' mode, which means insert/update/delete statements are 'bounded by an unseen BEGIN TRANSACTION and an unseen COMMIT TRANSACTION statement'

Rollback sql transaction only if unhandled error occurs

It seems simpler to me to just check if the object exists, or the user exists prior to running the insert, e.g.:

dbfiddle.uk demo - if exists

if not exists (select 1 from sysobjects where name=N'Users' and xtype='U')
begin;
CREATE TABLE [dbo].[Users](
    [UserId] [int] UNIQUE NOT NULL,
    [UserFullName] [nvarchar](100) NOT NULL,
    [UserName] [nvarchar](100) NOT NULL,
    [UserShortName] [nvarchar](50) NOT NULL,
    [UserLogin] [varchar](50) NOT NULL
);
PRINT 'Created table'
end;

---

if not exists (select 1 from dbo.users where userlogin = 'SA')
begin;
INSERT INTO [dbo].[Users](
    [UserId]
    ,[UserFullName]
    ,[UserName]
    ,[UserShortName]
    ,[UserLogin]
) VALUES (
    1,'System Administrator','Admin','SA',  'SA'
)
PRINT 'Inserted user'
end;

Do I need to explicitly rollback a transaction?

It is important to rollback the tx if there is an error while executing any query, otherwise it is still running and holding locks. Check out this post .

When does SQL server roll back an erroneous transaction?

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.

---

Related Topics