SQL Server Isolation Levels Explained (with NOLOCK and RCSI)

Isolation levels decide what a transaction is allowed to see while other transactions are changing the same data. They are the reason a report can block an order screen, or why NOLOCK sometimes returns rows twice. Understanding them helps you fix blocking without trading it for wrong results.

The three problems isolation levels prevent

• Dirty read: you read a change another transaction has not committed yet, and it may later be rolled back.
• Non-repeatable read: you read the same row twice in one transaction and get different values, because someone updated it in between.
• Phantom read: you run the same query twice and new rows appear, because someone inserted rows that match your filter.

The isolation levels

READ UNCOMMITTED (and NOLOCK)

Readers take no shared locks, so they are never blocked by writers. The WITH (NOLOCK) table hint does the same thing for one table. The cost is more than "slightly stale data": you can read uncommitted changes that are later rolled back, and because pages can move during a scan, a query can skip rows or read the same row twice. Use it only where approximate answers are acceptable, never for money or anything you write back.

READ COMMITTED (the default)

You only see committed data. In its classic, locking form, a reader takes a short shared lock on each row it reads. If a writer holds an exclusive lock on that row, the reader waits. This is the most common source of readers and writers blocking each other.

READ COMMITTED SNAPSHOT (RCSI)

RCSI is a database option that changes how READ COMMITTED works. Instead of waiting on locks, readers see the last committed version of each row, kept in a version store in tempdb. Readers no longer block writers, and writers no longer block readers. It is enabled by default in Azure SQL Database.

ALTER DATABASE [YourDb]
SET READ_COMMITTED_SNAPSHOT ON
WITH ROLLBACK IMMEDIATE;   -- needs a moment with no other active sessions

Test before enabling on SQL Server: it adds tempdb load, and code that relied on readers waiting for writers (for example, a "read the latest value then update" pattern without proper locking) can behave differently.

REPEATABLE READ

Shared locks on rows you read are held until the transaction ends, so no one can change those rows under you. New rows can still appear (phantoms). Blocking increases because locks are held longer.

SNAPSHOT

The whole transaction sees the database as it was when the transaction started, using row versions, without taking shared locks. It must be allowed at database level first:

ALTER DATABASE [YourDb] SET ALLOW_SNAPSHOT_ISOLATION ON;

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN;
    -- every read here sees the same consistent point in time
COMMIT;

If two snapshot transactions update the same row, the second one fails with an update conflict (error 3960) and must be retried. That makes it ideal for consistent reports and less suited to busy update workloads.

SERIALIZABLE

The strictest level: it also locks the ranges you read, so no matching rows can be inserted until you finish. Results are fully consistent, but it causes the most blocking and is a common source of deadlocks. Use it only for short, specific operations that truly need it.

Quick comparison

Level	Dirty reads	Non-repeatable reads	Phantoms	Readers block writers?
Read Uncommitted	Possible	Possible	Possible	No
Read Committed (locking)	No	Possible	Possible	Briefly
Read Committed Snapshot	No	Possible	Possible	No
Repeatable Read	No	No	Possible	Yes
Snapshot	No	No	No	No
Serializable	No	No	No	Yes

Check what you are using

-- Database-level row versioning settings
SELECT name, is_read_committed_snapshot_on, snapshot_isolation_state_desc
FROM sys.databases
WHERE name = DB_NAME();

-- Isolation level of current sessions
SELECT session_id, transaction_isolation_level
FROM sys.dm_exec_sessions
WHERE is_user_process = 1;

In transaction_isolation_level, 1 = Read Uncommitted, 2 = Read Committed, 3 = Repeatable Read, 4 = Serializable and 5 = Snapshot.

Practical advice

• Replace blanket NOLOCK hints with RCSI; you get non-blocking reads without dirty or duplicated rows.
• Use Snapshot for long reports that need one consistent point in time.
• Keep Repeatable Read and Serializable for short, specific transactions.
• Watch out for libraries that silently raise isolation. For example, System.Transactions.TransactionScope in .NET defaults to Serializable unless you set the isolation level explicitly.

Related reading:

• How to Fix SQL Server Deadlocks
• How to Delete or Update Millions of Rows Safely
• How to Find Slow Queries in SQL Server