SQL Server’s isolation levels control how much one transaction can see of another’s uncommitted changes. The default READ COMMITTED prevents dirty reads but allows non-repeatable reads. Read Committed Snapshot Isolation (RCSI) is the recommended setting for most production applications — it provides READ COMMITTED semantics but uses row versioning so readers never block writers and writers never block readers. Enabling RCSI on the BlogApp database eliminates most blocking issues without changing application code.
Isolation Levels
-- ── Enable RCSI — recommended for web applications ────────────────────────
ALTER DATABASE BlogApp SET READ_COMMITTED_SNAPSHOT ON;
-- All connections using READ COMMITTED (the default) now get snapshot-based reads
-- Readers see the last committed version of each row (from tempdb version store)
-- No more read/write blocking — readers don't block writers, writers don't block readers
-- ── Set isolation level for a session ─────────────────────────────────────
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- default
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- dirty reads allowed (dangerous)
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- prevents non-repeatable reads
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- prevents phantom reads
SET TRANSACTION ISOLATION LEVEL SNAPSHOT; -- transaction-level snapshot
-- ── Demonstrate the concurrency anomalies ─────────────────────────────────
-- 1. Dirty read (READ UNCOMMITTED):
-- Session A: BEGIN TRAN; UPDATE Posts SET ViewCount = 9999 WHERE Id = 1
-- Session B: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- SELECT ViewCount FROM Posts WHERE Id = 1 -- reads 9999 (uncommitted!)
-- Session A: ROLLBACK -- 9999 never existed; B read phantom data
-- 2. Non-repeatable read (READ COMMITTED without RCSI):
-- Session A: BEGIN TRAN; SELECT ViewCount FROM Posts WHERE Id = 1 -- gets 100
-- Session B: UPDATE Posts SET ViewCount = 200 WHERE Id = 1; COMMIT
-- Session A: SELECT ViewCount FROM Posts WHERE Id = 1 -- gets 200 (different!)
-- 3. Phantom read (REPEATABLE READ):
-- Session A: BEGIN TRAN; SELECT COUNT(*) FROM Posts WHERE CategoryId = 1 -- gets 10
-- Session B: INSERT INTO Posts (..., CategoryId = 1, ...); COMMIT
-- Session A: SELECT COUNT(*) FROM Posts WHERE CategoryId = 1 -- gets 11 (phantom!)
-- ── SNAPSHOT isolation — transaction-level consistency ────────────────────
-- Each transaction sees a consistent snapshot of the database as of its start time
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
-- All reads in this transaction see the DB state from when the transaction started
SELECT Id, Title FROM dbo.Posts WHERE IsPublished = 1;
-- Even if other transactions commit changes, this transaction sees the same snapshot
COMMIT TRANSACTION;
-- ── Check current isolation levels in use ────────────────────────────────
SELECT
r.session_id,
r.transaction_isolation_level,
CASE r.transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'Read Uncommitted'
WHEN 2 THEN 'Read Committed'
WHEN 3 THEN 'Repeatable Read'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
END AS IsolationLevelName,
r.status,
r.wait_type,
r.blocking_session_id
FROM sys.dm_exec_requests r
WHERE r.session_id > 50; -- user sessions (system sessions have low IDs)Note: When RCSI is enabled,
READ COMMITTED queries use row versioning from tempdb rather than shared locks. SQL Server maintains previous versions of rows in tempdb when they are updated — readers can access the last committed version without waiting for the writer to commit or rollback. This eliminates the most common blocking scenario in web applications (many concurrent reads blocking behind a single write). The trade-off: tempdb usage increases with the volume of active writes. Monitor tempdb free space when enabling RCSI on write-heavy databases.Tip: Use
SNAPSHOT isolation (not RCSI) for long-running reporting queries that need a consistent view of the database across many tables. RCSI gives per-statement consistency (each statement sees the latest committed data at the moment it starts). SNAPSHOT isolation gives per-transaction consistency (the entire transaction sees the database as it was when the transaction began). Use SNAPSHOT for reports that join multiple tables and need all reads to reflect the same point in time — otherwise you may see inconsistent aggregates across tables.Warning:
READ UNCOMMITTED (or its hint equivalent WITH (NOLOCK)) should almost never be used in production. It allows reading uncommitted data that may be rolled back — reading data that “never existed”. It also allows reading partially written rows during a page split (physically inconsistent data). The only legitimate use case is approximate counts on very high-traffic tables where a slightly inaccurate count is acceptable and you understand the risks. Never use NOLOCK for financial data, user data, or any data where consistency matters.Isolation Level Comparison
| Level | Dirty Read | Non-Repeatable | Phantom | Blocking |
|---|---|---|---|---|
| READ UNCOMMITTED | ✅ Possible | ✅ Possible | ✅ Possible | None |
| READ COMMITTED | ❌ Prevented | ✅ Possible | ✅ Possible | Read/Write |
| RCSI | ❌ Prevented | ✅ Possible | ✅ Possible | None (versioning) |
| REPEATABLE READ | ❌ Prevented | ❌ Prevented | ✅ Possible | High |
| SERIALIZABLE | ❌ Prevented | ❌ Prevented | ❌ Prevented | Very High |
| SNAPSHOT | ❌ Prevented | ❌ Prevented | ❌ Prevented | None (versioning) |
Common Mistakes
Mistake 1 — Using WITH (NOLOCK) everywhere to avoid blocking (dirty reads)
❌ Wrong — SELECT * FROM Posts WITH (NOLOCK); reads uncommitted data and partially written pages.
✅ Correct — enable RCSI on the database; all READ COMMITTED queries become non-blocking without NOLOCK’s risks.
Mistake 2 — Using SERIALIZABLE for all transactions (extreme blocking)
❌ Wrong — all transactions at SERIALIZABLE; range locks held throughout; severe blocking on concurrent access.
✅ Correct — use RCSI for most queries; only escalate to SERIALIZABLE for the specific operations that require strict phantom prevention.