Transactions in SQL Server

A transaction groups statements into an all-or-nothing unit. SQL Server runs in autocommit mode by default: every individual statement is its own transaction. The moment two statements must succeed or fail together -- debit one account, credit another -- you need an explicit transaction. T-SQL's transaction semantics have several behaviors that surprise people coming from PostgreSQL or MySQL, and the biggest one is that errors do not automatically roll back the transaction.

Basic Syntax

BEGIN TRANSACTION;
 
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
 
COMMIT TRANSACTION;

BEGIN TRAN, COMMIT, and ROLLBACK are the accepted short forms. If the connection drops before COMMIT, SQL Server rolls the transaction back.

The XACT_ABORT Trap

This is the most important thing on this page. By default (SET XACT_ABORT OFF), many runtime errors abort only the statement that failed -- the transaction stays open and the following statements keep executing:

BEGIN TRAN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- suppose this fails with a constraint violation:
UPDATE accounts SET balance = balance / 0 WHERE id = 2;
COMMIT;  -- still runs. The first UPDATE is committed. Half a transfer.

Whether an error kills the statement, the batch, or the transaction depends on the error's severity class -- behavior that is effectively impossible to memorize. The fix is to stop depending on it:

SET XACT_ABORT ON;

With XACT_ABORT ON, any runtime error rolls back the entire transaction and aborts the batch. Put it at the top of every stored procedure that writes data. There is a long-standing consensus among SQL Server practitioners on this, and no serious argument against it for write paths.

Error Handling: TRY/CATCH and XACT_STATE

For controlled error handling, combine XACT_ABORT with TRY...CATCH:

SET XACT_ABORT ON;
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 XACT_STATE() <> 0 ROLLBACK;
    THROW;  -- rethrow the original error
END CATCH;

XACT_STATE() returns 1 (open, committable), -1 (open but doomed -- only ROLLBACK is allowed), or 0 (no transaction). With XACT_ABORT ON, an error inside a transaction puts it in the doomed -1 state, so check before rolling back. Prefer THROW over RAISERROR for rethrowing: it preserves the original error number and severity.

Nested Transactions Are a Lie

BEGIN TRAN inside an open transaction does not create a nested transaction. It only increments @@TRANCOUNT:

BEGIN TRAN;            -- @@TRANCOUNT = 1
    BEGIN TRAN;        -- @@TRANCOUNT = 2 (nothing else happens)
    COMMIT;            -- @@TRANCOUNT = 1 (nothing is committed!)
ROLLBACK;              -- rolls back EVERYTHING, @@TRANCOUNT = 0

Two rules fall out of this:

• An inner COMMIT only decrements the counter. Work is committed only when the outermost COMMIT brings @@TRANCOUNT to 0.
• ROLLBACK always rolls back to the outermost BEGIN TRAN and resets @@TRANCOUNT to 0, no matter how deep you are. A stored procedure that issues ROLLBACK inside a caller's transaction destroys the caller's work too (and raises error 266 on exit because the trancount changed).

If you need partial rollback, use a savepoint:

BEGIN TRAN;
UPDATE orders SET status = 'shipped' WHERE id = 42;
SAVE TRANSACTION before_audit;
INSERT INTO audit_log (order_id, event) VALUES (42, 'shipped');
-- undo just the audit insert:
ROLLBACK TRANSACTION before_audit;
COMMIT;  -- the UPDATE still commits

ROLLBACK TRANSACTION savepoint_name does not decrement @@TRANCOUNT -- the transaction stays open.

Isolation Levels

Set per session with SET TRANSACTION ISOLATION LEVEL .... SQL Server implements five:

Three of these deserve detail:

READ COMMITTED is the default, and it comes in two flavors. In the classic locking flavor, readers take shared locks and block behind writers. With read committed snapshot isolation (RCSI) enabled --

ALTER DATABASE mydb SET READ_COMMITTED_SNAPSHOT ON;

-- readers instead see the last committed version of each row from the version store and never block behind writers. Azure SQL Database has RCSI on by default; on-prem SQL Server has it off. Turning it on is the single most effective fix for reader/writer blocking, at the cost of tempdb version-store overhead. Verify with SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name = 'mydb'.

SNAPSHOT gives the whole transaction one consistent view as of its start (like PostgreSQL's REPEATABLE READ). It requires ALTER DATABASE mydb SET ALLOW_SNAPSHOT_ISOLATION ON plus SET TRANSACTION ISOLATION LEVEL SNAPSHOT per session. The catch: if you update a row that another transaction modified after your snapshot began, your transaction is killed with error 3960 ("Snapshot isolation transaction aborted due to update conflict"). Snapshot readers don't block, but snapshot writers must be prepared to retry.

READ UNCOMMITTED -- usually spelled as the WITH (NOLOCK) table hint -- doesn't just risk reading uncommitted data that later rolls back. During page splits a NOLOCK scan can read the same row twice or skip rows entirely, so even "approximately right" isn't guaranteed. If you're reaching for NOLOCK to fix blocking, RCSI is almost always the correct answer instead.

Deadlocks

When two transactions block each other, SQL Server picks a victim and kills it with error 1205. This is not a bug you eliminate; it's a runtime condition you handle:

• Retry the victim. Error 1205 is the canonical retryable error -- wrap write transactions in retry logic (2-3 attempts with short backoff).
• Reduce the window: access tables in a consistent order across code paths, keep transactions short, and never hold a transaction open across user interaction or network calls.
• Diagnose with the system_health Extended Events session, which captures deadlock graphs by default.

SQL Server 2025: Optimized Locking

SQL Server 2025 introduces optimized locking (previously Azure SQL Database-only): a single lock per transaction ID replaces per-row exclusive locks held to commit, and "lock after qualification" evaluates predicates against committed versions before locking. The practical effects are far less lock memory, far less lock escalation, and fewer deadlocks -- with no code changes. It requires accelerated database recovery, and its LAQ component requires RCSI (as of July 2026). If lock escalation on big batch UPDATEs is a recurring pain, this is the headline reason to look at 2025.

Common Mistakes

Exploring lock and isolation behavior means running a lot of ad-hoc probe queries; Mako's AI autocomplete is handy for generating sys.dm_tran_locks and isolation-level test queries as you go.

Mako connects to SQL Server with AI-powered autocomplete. Try it free at mako.ai.