XACT_ABORT: Why a Half-Done Transaction Can Survive an Error

The second insert failed, but the first insert is still sitting inside an open transaction. XACT_ABORT changes that failure behavior, while a proper CATCH block makes the cleanup explicit.

Make the Failure Small and Visible

An explicit transaction groups work, but an error does not always end that group. Some runtime errors cancel only the failing statement. Earlier successful statements remain part of the transaction. If the code later commits, those earlier changes become permanent without the intended complete operation.

I look for transaction cleanup whenever an application reports a constraint error. The error message tells us what failed. It does not tell us whether the connection still owns locks or retains earlier changes. A failed operation can leave a surprisingly successful mess behind it.

Use a separate SSMS session with no existing transaction. The next block creates a temporary table for both demonstrations. Its primary key supplies a predictable runtime failure: inserting the same identifier twice. Run the setup once, then run each complete demonstration separately.

IF @@TRANCOUNT <> 0
    THROW 50000, 'Use a session with no open transaction for this demo.', 1;
CREATE TABLE #AtomicDemo
(
    ItemID int NOT NULL PRIMARY KEY,
    ItemText varchar(30) NOT NULL
);
SELECT @@TRANCOUNT AS TransactionCount, XACT_STATE() AS TransactionState;

XACT_ABORT OFF Lets the Earlier Insert Survive

With the setting OFF, this duplicate-key error rolls back the second insert statement. The first insert remains visible to the same session. Inside CATCH, inspect the state before cleanup. The sample deliberately rolls everything back afterward, keeping the next demonstration independent.

Do not add a COMMIT in this error branch simply because the transaction remains committable. A committable transaction is technically valid. It does not prove that the business operation finished correctly. Here, one successful insert is only half of the requested work.

The row query inside CATCH reveals the earlier insert while the transaction is open. The final query runs after rollback. Compare those outputs on your own instance. These are expected behavioral checks, not measurements of duration, reads, or production row counts.

SET XACT_ABORT OFF;
BEGIN TRY
    BEGIN TRANSACTION;
    INSERT #AtomicDemo VALUES (1, 'First insert');
    INSERT #AtomicDemo VALUES (1, 'Second insert');
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber,
        @@TRANCOUNT AS TransactionCount, XACT_STATE() AS TransactionState;
    SELECT ItemID, ItemText FROM #AtomicDemo;
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
END CATCH;
SELECT ItemID, ItemText FROM #AtomicDemo;
SELECT @@TRANCOUNT AS TransactionCount, XACT_STATE() AS TransactionState;

XACT_ABORT ON Makes the Operation All or Nothing

Now repeat the same inserts with XACT_ABORT ON. For this runtime constraint violation inside TRY, the transaction becomes uncommittable. CATCH still executes and can inspect its state. The explicit rollback removes the whole transaction, including the first insert.

Outside this caught-error pattern, termination and automatic rollback behave differently from what you see inside CATCH. Do not assume ON always means @@TRANCOUNT is already zero at the instant control enters the handler. Inspect the actual state, then clean up any active transaction.

That distinction prevents a common error-handler mistake. Code tries to write an error log inside the doomed transaction, then gets a second failure. Roll back first. Write diagnostics afterward, or use an independently designed logging path that does not depend on the failed transaction.

SET XACT_ABORT ON;
BEGIN TRY
    BEGIN TRANSACTION;
    INSERT #AtomicDemo VALUES (1, 'First insert');
    INSERT #AtomicDemo VALUES (1, 'Second insert');
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber,
        @@TRANCOUNT AS TransactionCount, XACT_STATE() AS TransactionState;
    SELECT ItemID, ItemText FROM #AtomicDemo;
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
END CATCH;
SELECT ItemID, ItemText FROM #AtomicDemo;
SELECT @@TRANCOUNT AS TransactionCount, XACT_STATE() AS TransactionState;

Count and State Answer Different Questions

@@TRANCOUNT reports the transaction nesting count. A positive count means the session has an active transaction context. It does not certify that COMMIT is legal. Nested BEGIN TRANSACTION statements also do not create independently committable business transactions.

XACT_STATE returns zero without an active user transaction, one for a committable transaction, and minus one for an uncommittable transaction. The doomed state allows reads but no writes or commit. It requires a full rollback. A savepoint cannot rescue an uncommittable transaction.

Use both values when diagnosing ownership and cleanup. Is the procedure supposed to own the transaction, or participate in a transaction started by its caller? That decision determines which code is allowed to roll back. The short template below deliberately owns its transaction and rejects ambient transactions.

Give the Procedure Clear Ownership

Create the next objects only in a scratch database. The permanent demo table lets the stored procedure reference a normal table. GO separates the table creation from the procedure's own batch. The procedure accepts two identifiers so you can test success and a duplicate-key failure.

A transaction-owning procedure is a straightforward contract. It starts, commits, and rolls back its own work. For procedures participating in caller-owned transactions, use a carefully designed ownership and savepoint pattern instead. With a doomed transaction, the caller must ultimately perform the full rollback.

CREATE TABLE dbo.AtomicProcedureDemo
(
    ItemID int NOT NULL PRIMARY KEY,
    ItemText varchar(30) NOT NULL
);
GO
CREATE PROCEDURE dbo.InsertPair
    @FirstID int,
    @SecondID int
AS
BEGIN
    SET NOCOUNT ON;
    IF @@TRANCOUNT <> 0
        THROW 50001, 'Call this demo procedure outside an existing transaction.', 1;
    SET XACT_ABORT ON;
    BEGIN TRY
        BEGIN TRANSACTION;
        INSERT dbo.AtomicProcedureDemo VALUES (@FirstID, 'First insert');
        INSERT dbo.AtomicProcedureDemo VALUES (@SecondID, 'Second insert');
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
        THROW;
    END CATCH;
END;
GO

Rethrow the Original Failure

The procedure's parameterless THROW preserves the caught error. It also ensures the caller receives failure after cleanup. Returning normally from CATCH without a clear failure signal can make an incomplete operation look successful to the application.

The next test intentionally passes duplicate identifiers. An outer handler prints the error information for the demonstration. Real application code should log or propagate that failure according to its contract. It should not turn every exception into a success response simply because rollback worked.

BEGIN TRY
    EXEC dbo.InsertPair @FirstID = 10, @SecondID = 10;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
SELECT ItemID, ItemText FROM dbo.AtomicProcedureDemo;
SELECT @@TRANCOUNT AS TransactionCount, XACT_STATE() AS TransactionState;

Know Which Errors XACT_ABORT Covers

XACT_ABORT addresses runtime transaction errors. Syntax and other compile errors are outside that setting's scope. Some errors terminate a transaction even with OFF, while some severe failures interrupt the connection. TRY CATCH also does not catch every possible cancellation or connection failure.

THROW honors this setting. RAISERROR does not follow the same rule. Existing procedures using RAISERROR therefore need explicit cleanup rather than assuming a new SET statement makes their handlers correct. Test the actual error paths your application uses.

I test more than the happy path before accepting a write procedure. Include a duplicate key, a foreign-key violation, and a deliberate application error. Check the table contents and connection state after each test. A disconnected test client cannot show whether a reusable application connection was left dirty.

Treat Cleanup as Part of the Operation

Keep transactions short and put validation where it belongs. Avoid waiting for user input while a transaction owns locks. If the operation needs several dependent writes, define what complete success means before choosing its error behavior.

For that all-or-nothing operation, XACT_ABORT ON plus TRY CATCH, conditional rollback, and THROW forms a clear starting pattern. Adapt it to transaction ownership rather than pasting it blindly. Finally, verify that every failure leaves the intended data and a clean connection state.

Related reading on this blog: SET XACT_ABORT ON: Stopping Timeouts From Leaving Open Transactions and THROW or RAISERROR.

A caught error is not completed cleanup, it is a reason to inspect and end the transaction.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.