Transaction isolation levels in SQL Server, from READ UNCOMMITTED to SNAPSHOT

Isolation levels decide what a transaction is allowed to see while other transactions are changing data. Pick too weak a level and you read garbage; pick too strong a level and everyone waits on locks.

The anomalies we're guarding against

• Dirty read — reading data another transaction hasn't committed (and might roll back).
• Non-repeatable read — reading the same row twice and getting different values.
• Phantom — running the same range query twice and getting new rows.

The levels at a glance

Isolation level	Dirty reads	Non-repeatable reads	Phantoms	Mechanism
READ UNCOMMITTED	Possible	Possible	Possible	No shared locks
READ COMMITTED (default)	Prevented	Possible	Possible	Short shared locks or row versions (RCSI)
REPEATABLE READ	Prevented	Prevented	Possible	Shared locks held to end of transaction
SERIALIZABLE	Prevented	Prevented	Prevented	Key-range locks
SNAPSHOT	Prevented	Prevented	Prevented	Row versioning, transaction-level snapshot

Set it per session:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRAN;
-- ...
COMMIT;

READ UNCOMMITTED and NOLOCK

WITH (NOLOCK) is the same as READ UNCOMMITTED for that table. It's popular because it "makes blocking go away" — but beyond dirty reads it can also skip rows or read the same row twice when pages split during a scan. It is rarely the right fix for reporting queries in an OLTP system.

READ COMMITTED vs. READ COMMITTED SNAPSHOT (RCSI)

Under the default locking READ COMMITTED, readers take short shared locks and wait for writers. Turning on RCSI changes the behavior of READ COMMITTED for the whole database: readers see the last committed version of each row, read from the version store, and don't block or get blocked by writers.

ALTER DATABASE Shop SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

Things to know before flipping the switch:

• Row versions are stored in tempdb (or in the user database's persistent version store when Accelerated Database Recovery is enabled), so plan for the extra load.
• Code that relied on readers blocking — for example, "read a value, then update it" without UPDLOCK — can behave differently. Review queue-style and counter patterns.
• Azure SQL Database has RCSI enabled by default.

SNAPSHOT isolation

SNAPSHOT gives a consistent view of the database as of the start of the transaction — every statement sees the same data. It must be allowed at the database level, then requested per session:

ALTER DATABASE Shop SET ALLOW_SNAPSHOT_ISOLATION ON;

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN;
SELECT SUM(Balance) FROM dbo.Accounts;  -- consistent
SELECT COUNT(*)    FROM dbo.Accounts;   -- same snapshot
COMMIT;

If two SNAPSHOT transactions update the same row, the later one fails with an update conflict (error 3960) instead of silently overwriting — your code must retry.

SERIALIZABLE

SERIALIZABLE takes key-range locks so no other transaction can insert into a range you've read. It's the safest for "check then insert" logic, but it's also the most prone to blocking and deadlocks. Use it narrowly, for example with a table hint on a single statement:

BEGIN TRAN;
IF NOT EXISTS (SELECT 1 FROM dbo.Users WITH (UPDLOCK, HOLDLOCK) WHERE Email = @Email)
    INSERT dbo.Users (Email) VALUES (@Email);
COMMIT;

A practical default

For most OLTP applications: enable RCSI, use explicit locking hints (UPDLOCK, HOLDLOCK) for the few read-modify-write paths that need them, and reserve SNAPSHOT for reports that must be internally consistent.

Key takeaways

1. Know which anomaly you're trying to prevent before picking a level.
2. Avoid NOLOCK as a performance fix — it returns incorrect results.
3. RCSI removes most reader/writer blocking at the cost of version-store overhead.
4. SNAPSHOT and SERIALIZABLE are powerful but require retry logic.