Transaction Isolation Level

The SET TRANSACTION ISOLATION LEVEL statement defines the lock behavior for the duration of a transaction. Choosing the correct isolation level balances data consistency with concurrency performance.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRAN;
-- statements
COMMIT;

Supported Isolation Levels

READ UNCOMMITTED▶

Allows dirty reads. No shared locks are issued, and data can be read even if it is being modified by other transactions.

Phenomena prevented: None
Typical use‑case: Reporting with minimal locking overhead.

READ COMMITTED▶

The default level. Shared locks are placed on data while it is being read and released immediately after the read operation completes.

Phenomena prevented: Dirty reads
Allows non‑repeatable reads and phantom rows.

REPEATABLE READ▶

Shared locks are held on all rows read until the transaction completes, preventing non‑repeatable reads.

Phenomena prevented: Dirty reads, non‑repeatable reads
Phantoms can still occur.

SNAPSHOT▶

Provides a transaction‑consistent view of the data as of the start of the transaction, using row versioning.

Phenomena prevented: Dirty reads, non‑repeatable reads, phantom reads
Requires ALLOW_SNAPSHOT_ISOLATION ON.

SERIALIZABLE▶

The strictest level. It places range locks on the data set, preventing other transactions from inserting or modifying rows that would affect query results.

Phenomena prevented: All (dirty, non‑repeatable, phantom)
Highest concurrency cost.

Isolation Level Comparison

Isolation Level	Dirty Reads	Non‑repeatable Reads	Phantom Reads
READ UNCOMMITTED	Yes	Yes	Yes
READ COMMITTED	No	Yes	Yes
REPEATABLE READ	No	No	Yes
SNAPSHOT	No	No	No
SERIALIZABLE	No	No	No

Example: Switching Isolation Levels

-- Set to READ UNCOMMITTED
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO

BEGIN TRAN;
SELECT * FROM dbo.Orders WITH (NOLOCK);
COMMIT;
GO

-- Set to SERIALIZABLE
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO

BEGIN TRAN;
SELECT * FROM dbo.Orders WHERE OrderDate BETWEEN '2024-01-01' AND '2024-12-31';
COMMIT;