Reading and Fixing a SQL Server Deadlock and Troubleshoot SQL Server Blocking both point at isolation level as part of the fix without covering what the levels actually are. This post fills that gap: what each isolation level trades off, what Read Committed Snapshot Isolation (RCSI) actually changes, and a real check of where this instance’s own databases stand.
The Levels, What Each One Actually Trades
- READ UNCOMMITTED, the
NOLOCKhint’s isolation level. No locks taken on reads, dirty reads possible (you can read a row another transaction hasn’t committed yet, and might roll back). Fastest, least correct. - READ COMMITTED, the default on every SQL Server install unless changed. Readers take short-lived shared locks (or, under RCSI, read a committed row version instead), guaranteeing you never see uncommitted data, but a value can still change between two reads in the same transaction.
- REPEATABLE READ, holds shared locks for the duration of the transaction on every row read, so a value you’ve read can’t change underneath you. Locks a wider surface for longer, more blocking.
- SERIALIZABLE, the strictest traditional level. Range locks prevent other transactions from inserting new rows into a range you’ve queried, not just protecting rows you’ve already touched. The widest lock footprint, the highest blocking risk.
- SNAPSHOT, row-versioning based. A transaction sees a consistent point-in-time view of the database for its whole duration, writers don’t block readers and readers don’t block writers, at the cost of tempdb version-store overhead and the possibility of a write-write conflict error if two transactions try to modify the same row.
RCSI: The One Most DBAs Actually Want
Read Committed Snapshot Isolation changes what happens under READ COMMITTED specifically, without touching application code or requiring SET TRANSACTION ISOLATION LEVEL anywhere. With RCSI on, READ COMMITTED readers use row versioning (from tempdb) instead of taking shared locks, so readers stop blocking writers and writers stop blocking readers, the single most common cause of “everything’s slow” blocking chains.
It’s a database-level setting, not a session or query-level one:
ALTER DATABASE YourDatabase SET READ_COMMITTED_SNAPSHOT ON;
This requires exclusive database access to switch (briefly, kill or wait out other connections), it’s not something you flip mid-day on a busy production database without a maintenance window.
What This Instance’s Own Databases Actually Show
Checked directly rather than assumed:
SELECT name, is_read_committed_snapshot_on, snapshot_isolation_state_desc
FROM sys.databases
WHERE name IN ('master', 'tempdb', 'DBAMonitor');
| name | is_read_committed_snapshot_on | snapshot_isolation_state_desc |
|---|---|---|
| master | False | ON |
| tempdb | False | OFF |
| DBAMonitor | False | OFF |
Two things worth reading carefully here. First, RCSI is off everywhere on this instance, the SQL Server default, and it stays off unless someone deliberately turns it on, it’s never implied by anything else. Second, master shows snapshot_isolation_state_desc = ON while is_read_committed_snapshot_on is still False, these are two genuinely separate settings (RCSI changes READ COMMITTED’s default behavior; full SNAPSHOT isolation is an opt-in level a transaction requests explicitly with SET TRANSACTION ISOLATION LEVEL SNAPSHOT), easy to conflate, independently switched, and this instance is a real example of one being on while the other is off.
Common Causes and Fixes
- Blocking chains that resolve as soon as the blocking session commits, the classic READ COMMITTED symptom. RCSI is usually the fix, once you’ve confirmed the workload can tolerate the tempdb version-store cost.
- A report or dashboard needing a consistent snapshot across several queries, not just one, is a genuine case for explicit SNAPSHOT isolation on that connection, not a database-wide RCSI change.
- Silent serializable promotion from ORM defaults, MSDTC-enlisted distributed transactions, or an explicit
HOLDLOCKhint, producing far more locking than anyone intended. Check the actual isolation level in use (sys.dm_exec_sessions.transaction_isolation_level) before assuming READ COMMITTED is really what’s running. - Write-write conflict errors after enabling SNAPSHOT or RCSI aren’t a bug, they’re the engine correctly detecting two transactions tried to modify the same row under a version they both read as current. The application needs a retry path for this error, the same way it should already retry on deadlock.
Best Practices
- Check
is_read_committed_snapshot_onandsnapshot_isolation_state_descseparately, as this post’s own real output shows, one doesn’t imply the other. - Before enabling RCSI on a busy database, confirm tempdb has headroom, the version store lives there and a heavy update workload under RCSI can grow it substantially. Check with Get Temp DB Hotspots.
- Never reach for
NOLOCKas a blanket fix for blocking, it trades correctness (dirty reads) for speed; RCSI gets you the speed without that trade for the READ COMMITTED case specifically. - Confirm the isolation level actually in effect on a connection before debugging further, ORMs and connection pool defaults sometimes aren’t what the team assumes.
Related
- Reading and Fixing a SQL Server Deadlock, RCSI eliminates a real category of reader/writer deadlocks
- Troubleshoot SQL Server Blocking, Step by Step
- Get Temp DB Hotspots, to check version-store impact before enabling RCSI
- LCK_M_X, LCK_M_S, and LCK_M_U Wait Types
Leave a Reply