Transactions

23.1 ACID Revisited

Atomicity: all or nothing (the transaction log enables rollback).

Consistency: constraints are always satisfied.

Isolation: concurrent transactions don't interfere (locks or row versions).

Durability: committed data survives crashes (write-ahead log).

23.2 Transaction Modes

Autocommit (default): each statement is its own transaction.

Explicit: BEGIN TRAN ... COMMIT/ROLLBACK.

Implicit: SET IMPLICIT_TRANSACTIONS ON. A transaction starts automatically and needs an explicit COMMIT.

Batch-scoped (MARS).

Distributed: BEGIN DISTRIBUTED TRAN (MSDTC).

23.3 Syntax

BEGIN TRANSACTION OrderTran;
    INSERT ...;
    SAVE TRANSACTION BeforeItems;        -- savepoint
    INSERT ...;
    IF @@ERROR <> 0 ROLLBACK TRANSACTION BeforeItems;   -- partial rollback
COMMIT TRANSACTION OrderTran;

SELECT @@TRANCOUNT;

23.4 Nested Transactions: The Truth

BEGIN TRAN increments @@TRANCOUNT. An inner COMMIT only decrements it. Only the outermost COMMIT really commits.

ROLLBACK (without a savepoint name) rolls back everything and sets @@TRANCOUNT to 0.

23.5 Transaction Best Practices

Keep transactions short. Never wait for user input inside one.

Access objects in a consistent order to reduce deadlocks.

Monitor long-running open transactions: DBCC OPENTRAN, sys.dm_tran_active_transactions.

23.6 Delayed Durability and ADR

DELAYED_DURABILITY = FORCED | ALLOWED: trades durability for log-write latency.

Accelerated Database Recovery (ADR, 2019+): near-instant rollback and recovery through a persistent version store. It also enables aggressive log truncation.