Three Transaction Modes in SQL Server

SQL Server operates in three commonly referenced transaction modes:

1. Autocommit (default): each individual Transact-SQL statement is treated as its own transaction and is automatically committed if it succeeds. This behavior is associated with SET IMPLICIT_TRANSACTIONS OFF.
2. Implicit transactions: SQL Server automatically starts a new transaction after the previous one completes, but you must explicitly end it with COMMIT or ROLLBACK. This behavior is enabled with SET IMPLICIT_TRANSACTIONS ON.
3. Explicit transactions: you explicitly define the unit of work using BEGIN TRANSACTION and close it with COMMIT or ROLLBACK. This is the standard approach for multi-statement business operations that must be all-or-nothing.

Regardless of mode, transaction state and recovery are backed by the transaction log. The log records changes so SQL Server can roll back incomplete work and recover committed work after failures. This is also why excessive transaction log generation and long-running transactions can become operational bottlenecks in busy systems.

The next lesson explains how isolation levels affect transaction behavior, including locking, blocking, and the anomalies different isolation levels permit or prevent.

Transactions are described as nested when one transaction begins while another is already active in the same session. In SQL Server, nested transactions are best understood as a counting mechanism: each BEGIN TRANSACTION increments a counter, and each COMMIT decrements it. The work is not fully committed until the outermost transaction commits.

A key operational detail is that a ROLLBACK rolls back the entire transaction scope (not just the “inner” portion) unless you use savepoints (via SAVE TRANSACTION) to roll back to a known intermediate state. This module covers nested transactions and savepoints in a later lesson because they are closely tied to error-handling patterns.

Transactions are easiest to understand when you think in terms of business invariants. A classic example is transferring money between accounts. The transfer must not allow “half completion.” If one update succeeds and the other fails, the database becomes incorrect.

Conceptually, the transfer requires two statements: one to subtract from checking and one to add to savings.

BEGIN TRANSACTION;

UPDATE checking
SET Balance = Balance - 1000
WHERE Account = 'Glenn';

UPDATE savings
SET Balance = Balance + 1000
WHERE Account = 'Glenn';

COMMIT;
  

This example is simplified, but it captures the key point: the database must treat both updates as one unit. If the first statement runs and the second fails, the correct response is to roll back, restoring the original balances. In real systems, this pattern is combined with proper error handling (TRY...CATCH plus rollback checks) and appropriate isolation choices to prevent inconsistent reads and minimize blocking.

Modern SQL Server workloads also frequently run through application connection pools. That makes correct transaction handling even more important, because an uncommitted transaction left open in one session can be reused by another request if the application does not clean up properly.