Snapshot Isolation — Complete Guide
Snapshot Isolation — Complete Guide: free step-by-step lesson with examples, common mistakes, and interview tips — part of SQL Server Tutorial on Toolliyo Academy.
On this page
SQL Server Tutorial · Lesson 56 of 100
Snapshot Isolation
SQL basics ✓ → Queries → Advanced
Queries · 2 — JOINs · ~10 min · SQL — Transactions & Concurrency
What is this?
Snapshot isolation reads a row version store so readers do not take shared locks on the same rows writers update. Database options control versions.
Why should you care?
Readers stop blocking writers (and vice versa for many cases) — great for mixed OLTP + reporting.
See it live — copy this example
Run in SQL Server Management Studio (SSMS) or Azure Data Studio.
ALTER DATABASE DataVerse SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE DataVerse SET READ_COMMITTED_SNAPSHOT ON; -- RCSI
USE DataVerse;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN;
SELECT Balance FROM dbo.Accounts WHERE AccountId = 1;
COMMIT;What happened?
- ALLOW_SNAPSHOT_ISOLATION enables SNAPSHOT level.
- READ_COMMITTED_SNAPSHOT (RCSI) makes ordinary READ COMMITTED use versions.
- tempdb holds versions — watch its health.
Practice next
- Enable the options on your database in lab.
- Run a long UPDATE in session A.
- SELECT in session B under RCSI — should not block.
- Toggle RCSI off and reproduce reader blocking.
- Compare with SET TRANSACTION ISOLATION LEVEL SNAPSHOT explicitly.
Remember
Snapshots read versions, not locked rows. RCSI changes default read behavior. Watch tempdb.
RCSI on DataVerse OLTP
Reporting SELECTs no longer block checkout UPDATEs.
Outcome: P99 checkout latency stabilizes at peak.
Interview prep for this lesson
Practice these questions aloud after reading—each links to a full structured answer.
Sign in to ask a question or upvote helpful answers.
No questions yet — be the first to ask!
