SQL Server Transaction Errors: Roll Back and Preserve the Failure

A checkout operation that reduces stock but fails to create a reservation has not partially succeeded. It has broken an invariant. SQL Server can make the related writes atomic, but only if the application handles errors and transaction boundaries consistently. A CATCH block that prints an error and returns normally can be just as dangerous as no handler at all.

Define success before writing the handler. In this example, reducing stock and recording the reservation must either both commit or both disappear. The sample owns its transaction and deliberately rejects an existing outer transaction. Reusable procedures called inside larger business transactions need a different, explicitly documented ownership contract.

Force a failure after the first write

Create these temporary tables in one practice connection.

CREATE TABLE #Stock (ProductId int PRIMARY KEY, Quantity int NOT NULL);
CREATE TABLE #Reservations (
    ReservationId int PRIMARY KEY, ProductId int NOT NULL, Quantity int NOT NULL
);
INSERT #Stock VALUES (1, 10);
INSERT #Reservations VALUES (1, 1, 1);

The reservation identifier already exists. The next batch reduces stock and then intentionally hits that duplicate key.

SET XACT_ABORT ON;
IF @@TRANCOUNT <> 0
    THROW 50001, 'Run without an existing transaction.', 1;

DECLARE @ReservationId int = 1; -- Deliberate duplicate for the failure test.
DECLARE @Quantity int = 3;
BEGIN TRY
    BEGIN TRANSACTION;
    UPDATE #Stock
    SET Quantity = Quantity - @Quantity
    WHERE ProductId = 1 AND Quantity >= @Quantity;
    IF @@ROWCOUNT <> 1
        THROW 50002, 'Insufficient stock or missing product.', 1;

    INSERT #Reservations(ReservationId, ProductId, Quantity)
    VALUES (@ReservationId, 1, @Quantity);
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    THROW;
END CATCH;

The conditional UPDATE checks and changes availability in one statement. A separate unlocked availability read would allow competing sessions to act on stale stock. Real input validation must also reject zero or negative quantities; the fixed positive value here keeps the demonstration focused.

Because the INSERT fails, the earlier reduction must be rolled back. Run the following inspection separately in the same connection after the expected error.

-- Run separately after the expected error, in the same connection.
SELECT ProductId, Quantity FROM #Stock;
SELECT @@TRANCOUNT AS OpenTransactions, XACT_STATE() AS TransactionState;
-- Expected: Quantity = 10, OpenTransactions = 0, TransactionState = 0.

Quantity should still be 10, and there should be no open transaction. For a success case, start from fresh practice data and use a new reservation identifier such as 2; the remaining stock should be 7. Checking only the error message would miss the more important question of whether the first write survived.

Understand the three mechanisms

TRY CATCH transfers control for many execution errors, but it does not catch every possible failure. Certain compilation errors at the same execution level, connection termination, and client cancellation require caller-side handling as well. A procedure cannot promise to clean up a connection after the connection itself has disappeared.

SET XACT_ABORT ON makes many runtime errors abort the transaction rather than merely stop one statement. It is a useful default for this kind of atomic write, but not a replacement for explicit cleanup and propagation. THROW honors XACT_ABORT; RAISERROR has different behavior and is not an interchangeable spelling.

XACT_STATE reports whether there is no transaction, a committable transaction, or an uncommittable transaction. @@TRANCOUNT reports nesting count and cannot answer the committability question. In this transaction-owning pattern, any remaining transaction is rolled back because the whole business operation failed, even if it technically could still commit.

The bare THROW inside CATCH preserves the original failure information. Replacing it with a generic success return or an unrelated error number makes diagnosis and retry classification harder. If you need error details for telemetry, capture ERROR_NUMBER, ERROR_PROCEDURE, and ERROR_LINE along with a request identifier.

Keep the caller's contract intact

A plain ROLLBACK without a savepoint rolls back the entire transaction, including work performed by the caller. Do not copy this owning pattern into a nested helper procedure without redesigning it. Nested BEGIN TRANSACTION and COMMIT statements do not create independently durable inner transactions.

A composable procedure can record its entry transaction count and use a savepoint where appropriate. However, an uncommittable transaction cannot be repaired by rolling back to a savepoint; the owner must roll back the whole transaction. Distributed transactions and other constraints also affect savepoint support. Simpler explicit ownership is often preferable to a universal-looking handler that hides these distinctions.

Write durable error telemetry after rollback or through an independent channel. An attempted log insert inside an uncommittable transaction can fail too, and a log row in the rolled-back transaction will disappear. Avoid recording sensitive parameter values unnecessarily.

Finally, a network timeout around COMMIT leaves the caller with an uncertain outcome, not proof of rollback. Use request identifiers and authoritative status checks for operations that cannot safely repeat. Test failure after each write, the success path, and the caller's response: it must never report success when the transaction was rolled back.

Technical references: Microsoft Learn: SET XACT_ABORT · Microsoft Learn: TRY CATCH · Microsoft Learn: XACT_STATE.