SQL Server Isolation Levels: READ UNCOMMITTED to Snapshot (2026)

Concurrency Anomalies: Dirty, Non-Repeatable, Phantom Reads

Three classic read anomalies define what isolation levels must prevent:

Dirty Read: Session A reads a row that Session B has modified but not yet committed. If B rolls back, A read data that never existed.

-- Session B
BEGIN TRAN;
UPDATE dbo.Accounts SET Balance = 999999 WHERE ID = 1;
-- (not yet committed)

-- Session A (READ UNCOMMITTED)
SELECT Balance FROM dbo.Accounts WHERE ID = 1;  -- sees 999999 (uncommitted!)

-- Session B
ROLLBACK;  -- 999999 never actually existed

Non-Repeatable Read: Session A reads a row, Session B updates and commits it, Session A reads it again in the same transaction and gets a different value.

Phantom Read: Session A queries a range (e.g., WHERE Amount > 1000), Session B inserts a new row in that range and commits, Session A re-queries the range and sees the new "phantom" row.

Isolation Level Summary Table

* RCSI reads the latest committed version, not a point-in-time snapshot, so non-repeatable reads are possible within the same transaction.

READ UNCOMMITTED (NOLOCK)

The weakest isolation level. Readers acquire no shared locks and ignore exclusive locks held by writers. This means reads never block writes and writes never block reads — but at the cost of dirty reads.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- Or per table:
SELECT * FROM dbo.Orders WITH (NOLOCK);

READ COMMITTED (Default)

The SQL Server default. Shared locks are acquired when reading and released immediately after the row is read (not held until end of transaction). This prevents dirty reads — a row cannot be read while another session holds an X lock on it.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  -- default; usually not needed explicitly

The "released immediately" behaviour means non-repeatable reads are possible: a value read at the start of a long transaction may be different if re-read later in the same transaction. For most OLTP workloads this is acceptable.

REPEATABLE READ

Shared locks are held until the end of the transaction, preventing other sessions from modifying any row you have read. This eliminates non-repeatable reads — if you re-read a row in the same transaction, it will have the same value.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRAN;
SELECT Balance FROM dbo.Accounts WHERE ID = 1;  -- S lock held
-- ... other work ...
SELECT Balance FROM dbo.Accounts WHERE ID = 1;  -- guaranteed same value
COMMIT;

Phantom reads are still possible — another session can INSERT new rows matching your WHERE clause. Only SERIALIZABLE prevents phantoms.

SERIALIZABLE

The strongest lock-based isolation level. In addition to holding S locks to end of transaction, SQL Server also acquires key-range locks that prevent new rows being inserted into any range you have queried. This prevents phantom reads.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRAN;
SELECT * FROM dbo.Orders WHERE OrderDate = '2026-06-11';
-- Key-range locks on the 2026-06-11 gap; no other session can insert for this date
COMMIT;

Serializable means all transactions appear to execute in serial order — the gold standard for correctness. But it produces the most blocking and the most deadlocks. Use it only when:

• You genuinely need to prevent phantom reads (financial summaries, audit trails)
• The transaction is short and the locked range is narrow
• You cannot use Snapshot Isolation for this workload

Read Committed Snapshot Isolation (RCSI)

RCSI is a database-level setting that changes the behaviour of the READ COMMITTED isolation level. Instead of taking S locks, readers read the last committed version of each row from TempDB's version store.

What RCSI provides:

• No dirty reads (versions are committed values only)
• No reader-writer blocking
• Statement-level consistency (each statement sees a snapshot of committed data at the moment the statement started)

What RCSI does NOT provide:

• Transaction-level consistency — within the same transaction, two SELECT statements may see different data if another transaction committed between them

-- Enable RCSI
ALTER DATABASE YourDB SET READ_COMMITTED_SNAPSHOT ON;

-- All READ COMMITTED sessions now use row versions automatically
-- No application changes needed

Snapshot Isolation (SI)

Snapshot Isolation is a session-level isolation level (unlike RCSI which is database-wide). It gives each transaction a transaction-consistent snapshot of the database as it existed when the transaction started. All reads within the transaction see the same version of data regardless of when in the transaction they execute.

-- Enable at database level (opt-in for individual sessions)
ALTER DATABASE YourDB SET ALLOW_SNAPSHOT_ISOLATION ON;

-- Use in a session
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN;
SELECT Balance FROM dbo.Accounts WHERE ID = 1;  -- snapshot of transaction start time
-- ... 30 seconds later ...
SELECT Balance FROM dbo.Accounts WHERE ID = 1;  -- still same value (transaction-consistent)
COMMIT;

SI prevents dirty reads, non-repeatable reads, and phantom reads — matching Serializable consistency — but without range locks. Writers still take X locks and writers block writers, but readers never block.

RCSI vs SI: Key Differences

Setting Isolation Level

-- Session level
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;  -- requires ALLOW_SNAPSHOT_ISOLATION ON

-- Query level (hint overrides session setting for that query)
SELECT * FROM dbo.Orders WITH (READUNCOMMITTED);  -- = READ UNCOMMITTED
SELECT * FROM dbo.Orders WITH (READCOMMITTED);
SELECT * FROM dbo.Orders WITH (REPEATABLEREAD);
SELECT * FROM dbo.Orders WITH (SERIALIZABLE);
SELECT * FROM dbo.Orders WITH (SNAPSHOT);

-- Check current session isolation level
SELECT transaction_isolation_level,
       CASE transaction_isolation_level
         WHEN 0 THEN 'Unspecified'
         WHEN 1 THEN 'ReadUncommitted'
         WHEN 2 THEN 'ReadCommitted'
         WHEN 3 THEN 'RepeatableRead'
         WHEN 4 THEN 'Serializable'
         WHEN 5 THEN 'Snapshot'
       END AS isolation_name
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID;

Decision Guide: Which Isolation Level to Use

Read Next

• Locking: Lock Types, Escalation and RCSI
• Blocking and Deadlocks: Detection and Resolution
• TempDB Internals: Version Store and Optimization
• Transaction Log Internals
• All SQL Server Articles