SpDev Install free

Transactions in SQL Server: BEGIN, COMMIT, ROLLBACK and XACT_ABORT

How SQL Server transactions really behave: why a failed statement doesn't roll back by itself, what XACT_ABORT changes, how nested transactions work, and the error 266 you get from procedures.

By Areeb Ahmer, . 5 minute read.

A transaction groups several changes so that they all happen or none do. In SQL Server you open one with BEGIN TRANSACTION, and end it with COMMIT to keep the changes or ROLLBACK to undo them. That much is in every tutorial.

What surprises people is what happens in between, when something fails. By default, SQL Server doesn’t roll back a transaction because one of its statements failed. The examples below, run against the SpDevDemo database, show exactly what does happen.

A failed statement doesn’t end the transaction

CREATE TABLE dbo.Payments (Id INT PRIMARY KEY);
GO

SET XACT_ABORT OFF;      -- the default for most connections
BEGIN TRANSACTION;
    INSERT dbo.Payments VALUES (1);
    INSERT dbo.Payments VALUES (1);    -- duplicate key: fails
    INSERT dbo.Payments VALUES (2);
COMMIT;

SELECT COUNT(*) AS RowsSaved FROM dbo.Payments;
Msg 2627, Level 14, State 1
Violation of PRIMARY KEY constraint 'PK__Payments__...'. Cannot insert duplicate key in object 'dbo.Payments'.
The statement has been terminated.

RowsSaved
---------
2

The failed INSERT was cancelled, the batch carried on, and COMMIT saved the other two rows. For a payment and its ledger entry, that’s the exact outcome transactions are meant to prevent.

Most errors like this, constraint violations, conversion errors, divide by zero, terminate only the statement. A few, such as deadlocks, terminate the batch and roll the transaction back. Which is which isn’t something you want your data’s consistency to depend on.

SET XACT_ABORT ON

TRUNCATE TABLE dbo.Payments;
GO

SET XACT_ABORT ON;
BEGIN TRANSACTION;
    INSERT dbo.Payments VALUES (1);
    INSERT dbo.Payments VALUES (1);    -- fails: now the whole transaction is rolled back
    INSERT dbo.Payments VALUES (2);
COMMIT;
GO

SELECT COUNT(*) AS RowsSaved, @@TRANCOUNT AS OpenTransactions FROM dbo.Payments;
SET XACT_ABORT OFF;
RowsSaved  OpenTransactions
---------  ----------------
0          0

With XACT_ABORT ON, any runtime error rolls back the whole transaction and stops the batch. It also protects you from a less visible problem: when a client times out, it stops waiting, but without XACT_ABORT the transaction stays open on the server, holding its locks, until the connection is closed or reused. With XACT_ABORT ON, the timeout rolls it back.

Put SET XACT_ABORT ON at the top of every procedure that opens a transaction. Inside a procedure, the setting lasts until the procedure returns.

One exception: RAISERROR doesn’t respect XACT_ABORT. The batch continues and the transaction stays open. THROW does respect it:

SET XACT_ABORT ON;
BEGIN TRANSACTION;
RAISERROR('Something is wrong', 16, 1);
PRINT CONCAT('Still running. Open transactions: ', @@TRANCOUNT);
ROLLBACK;
GO

SET XACT_ABORT ON;
BEGIN TRANSACTION;
THROW 50001, 'Something is wrong', 1;
PRINT 'Never printed';
GO

PRINT CONCAT('After THROW. Open transactions: ', @@TRANCOUNT);
SET XACT_ABORT OFF;
Msg 50000, Level 16, State 1
Something is wrong
Still running. Open transactions: 1
Msg 50001, Level 16, State 1
Something is wrong
After THROW. Open transactions: 0

Use THROW for your own errors in new code.

Transactions and TRY…CATCH

The usual pattern combines both:

SET XACT_ABORT ON;
BEGIN TRY
    BEGIN TRANSACTION;
    -- the changes
    COMMIT;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;
    THROW;
END CATCH

Inside the CATCH block, XACT_STATE() tells you the transaction’s state: 1 it’s open and can still be committed, -1 it’s open but doomed (it can only be rolled back), 0 there’s none. With XACT_ABORT ON, an error that reaches CATCH always dooms the transaction, which is what you want. More in error handling.

Nested transactions are not really nested

SQL Server lets you write BEGIN TRANSACTION inside another one. It only increments a counter:

BEGIN TRANSACTION;
PRINT CONCAT('Level: ', @@TRANCOUNT);
    BEGIN TRANSACTION;
    PRINT CONCAT('Level: ', @@TRANCOUNT);
    COMMIT;                                   -- commits nothing: decrements the counter
    PRINT CONCAT('After inner COMMIT: ', @@TRANCOUNT);
ROLLBACK;                                     -- rolls back everything, including the "committed" inner part
PRINT CONCAT('After ROLLBACK: ', @@TRANCOUNT);
Level: 1
Level: 2
After inner COMMIT: 1
After ROLLBACK: 0
  • An inner COMMIT doesn’t commit anything; only the outermost COMMIT does.
  • Any ROLLBACK rolls back all levels, back to the outermost BEGIN TRANSACTION.

Error 266: a procedure that rolls back its caller’s transaction

That second rule causes a classic error when a procedure that manages its own transaction is called inside another transaction:

CREATE OR ALTER PROCEDURE dbo.SaveSomething
AS
BEGIN
    BEGIN TRANSACTION;
    -- something goes wrong
    ROLLBACK;
END
GO

BEGIN TRANSACTION;
EXEC dbo.SaveSomething;
PRINT CONCAT('Open transactions after the call: ', @@TRANCOUNT);
IF @@TRANCOUNT > 0 ROLLBACK;
Msg 266, Level 16, State 2, Procedure dbo.SaveSomething
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.
Open transactions after the call: 0

The procedure’s ROLLBACK undid the caller’s work too, and SQL Server warns that the count changed. Two ways to write procedures that behave well when nested:

  • Let errors propagate. The procedure rolls back only if it started the transaction (IF @@TRANCOUNT = 0 BEGIN TRANSACTION, remembering that it did), and otherwise rethrows with THROW so the caller decides.
  • Savepoints for partial rollback: SAVE TRANSACTION BeforeStep; then ROLLBACK TRANSACTION BeforeStep; undoes work back to the savepoint without ending the transaction. This doesn’t work once the transaction is doomed.

Keep transactions short

A transaction holds its locks until it ends. Everything inside it that isn’t a change to the data makes everyone else wait longer:

  • Don’t wait for user input or call external services inside a transaction.
  • Do the reads and calculations first, then open the transaction for the writes.
  • Don’t loop over thousands of rows inside one transaction when one set-based statement would do.

Isolation, in one paragraph

The isolation level decides what a transaction can see of other transactions’ uncommitted work and how long it holds read locks. The default, READ COMMITTED, blocks readers behind writers. Turning on READ_COMMITTED_SNAPSHOT for the database makes readers see the last committed version instead, so they don’t block or get blocked, at the cost of version storage in tempdb. It’s usually a better answer to blocking than NOLOCK.

SpDev’s sandbox mode is a transaction too: it runs a procedure inside one and rolls it back at the end, so you can test procedures that change data. When the procedure commits or rolls back on its own, a sandbox can’t protect you, and SpDev reports it. Its profiler also shows long transactions held open across slow statements.