SQL Server Isolation Levels Explained

Why Isolation Levels Exist

Isolation level is the "I" in ACID. It controls how much one transaction is allowed to see of another transaction's in-flight changes. Full isolation would mean every transaction runs as if it were alone on the server, which is safe but kills concurrency. No isolation would mean transactions constantly read each other's half-finished work, which is fast but unsafe.

SQL Server lets you pick a point on that trade-off per session, per query, or per database. The default, READ COMMITTED, is a compromise most people never think about until a report shows numbers that don't add up, or a batch job deadlocks at 2 AM.

The Four Read Phenomena

Isolation levels are defined by which of these anomalies they allow:

Dirty read: reading a row that another transaction has modified but not yet committed. If that transaction rolls back, you read data that never existed.
Non-repeatable read: reading the same row twice in one transaction and getting different values, because another transaction committed a change in between.
Phantom read: re-running the same range query and getting new rows that weren't there the first time, because another transaction inserted matching rows in between.
Lost update: two transactions both read a value, both compute a new value based on it, and one overwrite silently discards the other's change.

The Isolation Levels

Level	Dirty reads	Non-repeatable reads	Phantom reads	Mechanism
`READ UNCOMMITTED`	Possible	Possible	Possible	No shared locks taken, ignores locks held by others
`READ COMMITTED` (default)	Prevented	Possible	Possible	Shared locks released after each statement
`REPEATABLE READ`	Prevented	Prevented	Possible	Shared locks held until end of transaction
`SERIALIZABLE`	Prevented	Prevented	Prevented	Range locks held until end of transaction
`SNAPSHOT`	Prevented	Prevented	Prevented	Row versioning, no read locks at all
`READ COMMITTED SNAPSHOT` (RCSI)	Prevented	Possible	Possible	Row versioning per statement, no read locks

READ UNCOMMITTED

Also known as NOLOCK. Reads whatever is on the page right now, committed or not, and takes no shared locks itself.

SELECT * FROM Orders WITH (NOLOCK) WHERE Status = 'Open';
-- or session-wide
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

The part people forget: NOLOCK doesn't just risk dirty reads. Under page splits it can skip rows entirely or read the same row twice, and it can throw "could not continue scan with NOLOCK due to data movement" errors. It is not a free performance button, it is a correctness trade you're making without realizing it.

READ COMMITTED (the default)

Guarantees you never see uncommitted data. In the locking implementation, a shared lock is taken to read a row and released immediately after, so the next statement in the same transaction can see a different value.

BEGIN TRANSACTION;
SELECT Balance FROM Accounts WHERE AccountID = 1;  -- reads 100
-- another session commits an UPDATE here, Balance becomes 150
SELECT Balance FROM Accounts WHERE AccountID = 1;  -- reads 150
COMMIT;

That's a non-repeatable read, and it's allowed by design at this level. Most OLTP workloads accept it because holding locks any longer would hurt concurrency more than it's worth.

REPEATABLE READ

Once a row is read, its shared lock is held until the transaction ends, so nobody else can modify or delete it. Reading the same row twice always returns the same value.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
SELECT Balance FROM Accounts WHERE AccountID = 1;  -- lock held
-- ... other logic ...
SELECT Balance FROM Accounts WHERE AccountID = 1;  -- guaranteed same value
COMMIT;

It does not stop new rows from appearing in a range. If you re-run a WHERE Status = 'Open' query, new rows inserted by someone else can still show up. That's the phantom that this level doesn't cover.

SERIALIZABLE

The strictest locking level. Range locks prevent both changes to existing rows and inserts of new rows that would match your query's predicate.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
SELECT COUNT(*) FROM Orders WHERE CustomerID = 42;  -- range locked
-- a concurrent INSERT INTO Orders (CustomerID = 42, ...) will block here
COMMIT;

Correct, but expensive. Wide range locks mean more blocking and a much higher chance of deadlocks under concurrent write load. Use it when correctness genuinely requires it, not as a default.

SNAPSHOT

A different strategy entirely: instead of blocking readers and writers against each other, SQL Server keeps versions of rows in tempdb and gives each transaction a consistent view as of when it started.

ALTER DATABASE MyDb SET ALLOW_SNAPSHOT_ISOLATION ON;

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
SELECT Balance FROM Accounts WHERE AccountID = 1;
-- sees the data exactly as it was at BEGIN TRANSACTION,
-- regardless of what other sessions commit afterward
COMMIT;

Readers never block writers and writers never block readers. The cost moves from locking to tempdb version store growth, and to write-write conflicts: if your transaction tries to update a row that changed since your snapshot started, you get error 3960 and have to retry.

READ COMMITTED SNAPSHOT (RCSI)

The database-level setting most people actually want. It changes what READ COMMITTED means, from "take and release a shared lock" to "read the last committed version as of the start of the statement," using the same version store as SNAPSHOT.

ALTER DATABASE MyDb SET READ_COMMITTED_SNAPSHOT ON;

Why this is the pragmatic default for OLTP: no application code changes, no explicit isolation level to set, readers stop blocking writers and vice versa, and the semantics stay "read committed" (per-statement consistency, not per-transaction). It's a database-level flag, requires an exclusive lock to enable, and adds row-versioning overhead comparable to SNAPSHOT.

SNAPSHOT vs. RCSI, the Distinction That Trips People Up

Both use row versioning and both eliminate reader/writer blocking, but they answer different questions:

RCSI gives each statement a consistent snapshot, taken at the start of that statement. Two SELECTs in the same transaction can see different data, same as ordinary READ COMMITTED.
SNAPSHOT gives the whole transaction one consistent snapshot, taken at BEGIN TRANSACTION. Every SELECT in that transaction sees the same point in time.

RCSI is a database setting that changes default behavior transparently. SNAPSHOT is an isolation level you opt into explicitly per session, and it requires ALLOW_SNAPSHOT_ISOLATION ON at the database level plus SET TRANSACTION ISOLATION LEVEL SNAPSHOT in the session.

How to Check What's Enabled

-- Database-level settings
SELECT name, is_read_committed_snapshot_on, snapshot_isolation_state_desc
FROM sys.databases
WHERE name = 'MyDb';

-- Current session's isolation level
DBCC USEROPTIONS;

Picking the Right Level

Scenario	Recommendation
Standard OLTP application	RCSI. Removes most blocking with zero code changes.
Reporting queries against a live OLTP database	RCSI, or SNAPSHOT if the report needs a stable multi-query view.
Financial calculations, transfers, anything with a read-modify-write step	REPEATABLE READ or SERIALIZABLE, or use `UPDLOCK` hints to avoid the lost-update race explicitly.
Approximate counts on a dashboard, non-critical monitoring	READ UNCOMMITTED, accepted knowingly, not as a default habit.
"I don't know, just make it fast"	Not a valid input. Identify the actual anomaly you can tolerate first.

The Verdict

Isolation level is a correctness decision, not a performance knob. Know which anomaly you can tolerate before you pick one. For most OLTP systems, enabling RCSI is the single highest-value change: it keeps READ COMMITTED semantics, requires no application rewrite, and removes the reader/writer blocking that NOLOCK hints were papering over.

If your instinct when queries are slow is to sprinkle NOLOCK everywhere, stop and check whether RCSI is enabled first. It solves the same blocking problem without trading away correctness.

← Back to Blog