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.
Error 8152 never said which column. Message 2628 on SQL Server 2019+ (or trace flag 460 on 2016/2017) names it. Then fix the data or the column, and watch for silent truncation.
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 ONWITHROLLBACK 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;
SELECTSUM(Balance) FROM dbo.Accounts; -- consistentSELECTCOUNT(*) FROM dbo.Accounts; -- same snapshotCOMMIT;
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 NOTEXISTS (SELECT1FROM 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
Know which anomaly you're trying to prevent before picking a level.
Avoid NOLOCK as a performance fix — it returns incorrect results.
RCSI removes most reader/writer blocking at the cost of version-store overhead.
SNAPSHOT and SERIALIZABLE are powerful but require retry logic.
A query-first method: find the expensive queries, read the plan for signs the missing index DMVs never report, build the index online safely, then verify and roll back.
