Transactions & Isolation Levels

A transaction is a unit of work — either every statement commits together or none of them do. Isolation levels control how concurrent transactions see each other's uncommitted changes.

BEGIN / COMMIT / ROLLBACK

BEGIN TRAN;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;     -- (or COMMIT TRAN / COMMIT TRANSACTION — all synonyms)

-- Roll back on error
BEGIN TRAN;
BEGIN TRY
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
    COMMIT;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;
    THROW;
END CATCH;

@@TRANCOUNT and Nested Transactions

T-SQL transactions can be named but they don't truly nest — BEGIN TRAN increments @@TRANCOUNT, COMMIT decrements, but only the outermost COMMIT actually commits. A ROLLBACK always rolls back to the outermost transaction (unless you use savepoints).

PRINT @@TRANCOUNT;          -- 0
BEGIN TRAN;
    PRINT @@TRANCOUNT;      -- 1
    BEGIN TRAN;
        PRINT @@TRANCOUNT;  -- 2
    COMMIT;                 -- decrements to 1, but does NOT really commit yet
    PRINT @@TRANCOUNT;      -- 1
COMMIT;                     -- this is the real commit

Savepoints

Use a savepoint to roll back part of a transaction:

BEGIN TRAN;
    INSERT INTO orders (...) VALUES (...);
    SAVE TRAN line_items;

    INSERT INTO order_items (...) VALUES (...);
    IF @@ERROR <> 0
        ROLLBACK TRAN line_items;   -- undo only the items insert
COMMIT;

XACT_ABORT

Without XACT_ABORT ON, many runtime errors leave the transaction open and partially committed. Most production code starts with:

SET XACT_ABORT ON;
SET NOCOUNT ON;

XACT_ABORT ON causes any runtime error to roll back the entire transaction automatically — the safer default.

Isolation Levels

Set per-session with SET TRANSACTION ISOLATION LEVEL .... Each level trades concurrency for consistency:

-- Switch this session to SNAPSHOT for the duration
ALTER DATABASE HR SET ALLOW_SNAPSHOT_ISOLATION ON;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRAN;
    -- Sees a consistent snapshot of the DB at transaction start
    SELECT * FROM employees;
    -- ...
COMMIT;

READ_COMMITTED_SNAPSHOT vs SNAPSHOT

-- Enable RCSI on a database — fix for blocking under READ COMMITTED
ALTER DATABASE HR SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

Locking Hints

Per-statement locking overrides:

-- Read-mostly query that prefers stale data over blocking (use sparingly!)
SELECT * FROM orders WITH (NOLOCK) WHERE order_date >= '2024-01-01';

-- Safe UPSERT pattern: lock the row before deciding insert vs update
BEGIN TRAN;
    UPDATE config WITH (UPDLOCK, HOLDLOCK)
    SET    v = @v
    WHERE  k = @k;
    IF @@ROWCOUNT = 0
        INSERT INTO config (k, v) VALUES (@k, @v);
COMMIT;

-- Queue dispatcher that skips rows other workers are processing
DELETE TOP (1) FROM job_queue WITH (READPAST, ROWLOCK)
OUTPUT deleted.*
WHERE  status = 'PENDING';

Deadlocks

A deadlock occurs when two transactions hold locks the other needs. SQL Server detects them and kills the deadlock victim (lowest-priority transaction) with error 1205.

BEGIN TRY
    BEGIN TRAN;
        UPDATE accounts SET balance = balance - 100 WHERE id = 1;
        UPDATE accounts SET balance = balance + 100 WHERE id = 2;
    COMMIT;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;
    IF ERROR_NUMBER() = 1205     -- deadlock victim
        -- retry once
        ;
    ELSE THROW;
END CATCH;

Avoid deadlocks by accessing tables in a consistent order in every transaction, keeping transactions short, and using READ_COMMITTED_SNAPSHOT to remove read-write blocking entirely.

Best Practices

• Start most procedures with SET XACT_ABORT ON; SET NOCOUNT ON;.
• Keep transactions short — never include user interaction or long network calls.
• Enable READ_COMMITTED_SNAPSHOT on most OLTP databases — eliminates a huge class of blocking.
• Use lock hints sparingly and document why each one is there.
• Always check @@TRANCOUNT before ROLLBACK in CATCH to avoid "no active transaction" errors.

Summary

• BEGIN TRAN / COMMIT / ROLLBACK define units of work; @@TRANCOUNT tracks nesting.
• Savepoints (SAVE TRAN) allow partial rollback; XACT_ABORT ON rolls back the whole txn on any runtime error.
• Isolation levels range from READ UNCOMMITTED (fast, unsafe) to SERIALIZABLE (strict, slow).
• SNAPSHOT gives transaction-wide consistency; READ_COMMITTED_SNAPSHOT is the default-friendly version for OLTP.
• Avoid NOLOCK; use UPDLOCK + HOLDLOCK for safe upserts.