What are the SQL Server transaction isolation levels?
Quick Answer
Isolation levels trade consistency for concurrency by controlling which anomalies you can see: Read Uncommitted (dirty reads), Read Committed (default — no dirty reads, but non-repeatable reads possible), Repeatable Read (rows you read stay stable; phantoms possible), Serializable (full isolation via range locks), and Snapshot (row-versioned consistent view, readers don't block writers).
Isolation levels decide which concurrency anomalies a transaction can see. Read Uncommitted lets you see other transactions' uncommitted changes — dirty reads. Read Committed, the default, stops that but you can still get non-repeatable reads: read a row twice, get different values. Repeatable Read locks the rows you've read so they don't change, but new rows matching your filter can still appear — phantoms. Serializable stops everything with range locks but at the cost of heavy blocking. Snapshot isolation uses row versioning so readers see a consistent point-in-time view and don't block writers — for a read-heavy system that's often the right choice. And NOLOCK is just Read Uncommitted on one table, so I keep it away from anything that has to be accurate.
| Level | Dirty read | Non-repeatable | Phantom |
|---|---|---|---|
| Read Uncommitted | Yes | Yes | Yes |
| Read Committed (default) | No | Yes | Yes |
| Repeatable Read | No | No | Yes |
| Serializable | No | No | No |
| Snapshot | No | No | No |
- Higher levels take more/longer locks → more blocking and deadlocks.
- Snapshot / Read Committed Snapshot Isolation (RCSI) uses row versions in tempdb so readers get a consistent snapshot without blocking writers — often the best default for read-heavy OLTP.
WITH (NOLOCK)is Read Uncommitted for one table — it can return dirty, duplicated or missing rows. Avoid it on data that must be correct.
- Sprinkling
WITH (NOLOCK)everywhere to "fix blocking" — it can return rows twice, skip rows, or read uncommitted data. - Assuming the default is Serializable — it's Read Committed.
- Using Serializable broadly and then being surprised by deadlocks.
- Not knowing whether the database has RCSI enabled, which changes Read Committed's behaviour.