Transactions and `TRY/CATCH`: Complete or roll back a unit of work

When an operation modifies multiple tables, keeping only the first half can leave inconsistent data. A transaction groups statements to commit together with COMMIT or roll back with ROLLBACK.

TRY/CATCH allows you to capture errors and ensure that a pending transaction is not silently left open.

The idea

BEGIN TRANSACTION starts the unit of work.
COMMIT confirms the changes.
ROLLBACK reverts changes not yet committed.
TRY/CATCH separates normal execution from error handling.

Syntax

BEGIN TRY
    BEGIN TRANSACTION;

    -- Instrucciones relacionadas.

    COMMIT;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0
        ROLLBACK;

    THROW;
END CATCH;

THROW returns the error to the process that executed the code. Hiding it could make the application believe that the operation completed successfully.

Example: creating an order and its departure

The example also uses three auxiliary constructions:

SET XACT_ABORT ON indicates that certain execution errors should terminate and roll back the entire transaction.
SYSDATETIME() gets the current date and time from the server.
SCOPE_IDENTITY() returns the last IDENTITY value generated in the same scope. The scope is the batch, procedure or trigger where the insertion was executed.

SET XACT_ABORT ON;

BEGIN TRY
    BEGIN TRANSACTION;

    INSERT INTO dbo.Pedido
        (IdCliente, FechaPedido, Estado)
    VALUES
        (3, SYSDATETIME(), 'Pendiente');

    DECLARE @IdPedido BIGINT = SCOPE_IDENTITY();

    INSERT INTO dbo.PedidoDetalle
        (IdPedido, NumeroPartida, IdProducto,
         Cantidad, PrecioUnitario, Descuento)
    VALUES
        (@IdPedido, 1, 1, 2, 349.00, 0);

    COMMIT;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0
        ROLLBACK;

    THROW;
END CATCH;

If the item cannot be inserted, the CATCH block also reverts the order header. SET XACT_ABORT ON causes many execution errors to terminate the transaction instead of allowing the batch to continue in a partial state.

XACT_STATE() reports the status of the current transaction: returning 1 when it can still be committed, 0 when there is no active transaction, and -1 when it can only be rolled back. Within CATCH, condition <> 0 covers the two states where a transaction still exists.

Nested transactions

Calling BEGIN TRANSACTION multiple times increments @@TRANCOUNT, but does not create independent units that can be confirmed separately. A ROLLBACK without a save point rolls back the entire transaction.

When a procedure can be executed within another transaction, it must be agreed who is responsible for starting it, committing it, and rolling it back. An isolated pattern does not replace that contract.

Keep the transaction short

While a transaction remains open, it can retain locks—protections that coordinate concurrent access—increase use of the transaction log, where SQL Server records changes, and make other sessions wait. Validations that do not need protection inside the same unit should happen first; human interaction should never keep a production transaction open.

What should be checked

A transaction guarantees atomicity: either every included operation is committed or none remains. It does not validate business logic. We still need correct filters, constraints, concurrency handling, and checks on affected rows.

We also review the isolation level—the rules that determine which changes from other transactions a session may observe—and possible blocking. Extending a transaction to feel safer can have the opposite effect if it unnecessarily increases contention: several sessions waiting for the same resources.