30-Second Glance
- Each txn reads from a consistent snapshot taken at start (MVCC + txids); concurrent commits are invisible mid-transaction — no read skew, repeatable reads free.
- Writers still check for write-write conflicts: concurrent modifications to the same row abort one side (first-committer-wins).
- Called
REPEATABLE READin PostgreSQL/MySQL andSERIALIZABLEin Oracle — know the naming trap. - Kills lost update and read skew but NOT write skew or phantoms; long snapshots also inflate bloat/VACUUM pressure.
Mechanism: MVCC Snapshots
Every row version carries creator/deleter transaction IDs (xmin/xmax in Postgres). At BEGIN, the transaction records the set of in-flight txids; a row version is visible iff its creator committed before the snapshot and its deleter hasn't (committed delete before snapshot hides it). All statements in the transaction share this snapshot — unlike Read Committed Isolation's per-statement snapshots — so re-reads are stable and multi-statement reads (backup, transfer validation) see a frozen instant.
Writes create new row versions stamped with the writer's txid, invisible to everyone until commit. On commit, the engine checks: did a concurrent transaction (overlapping lifetime) already commit a newer version of any row I wrote? If yes → write-write conflict → abort with serialization/retryable error (Postgres 40001). This first-committer-wins rule is what auto-prevents the lost-update anomaly that read committed allows.
Concrete Numbers
| Quantity | Typical value | Why it matters |
|---|---|---|
| Snapshot cost | O(#in-flight txns) txid list, microseconds | Cheap to start; never a reason to avoid the level |
| Version overhead | ~16–28 bytes/row (xmin/xmax/cid) + old versions retained | Long-running snapshot pins dead tuples → table bloat |
| Postgres bloat signal | pg_stat_user_tables.n_dead_tup growing; autovacuum can't clean past oldest snapshot |
One forgotten open txn can stall VACUUM for hours |
| Conflict abort rate | <1% at low contention; 10–50%+ on hot-row write storms | Retry with backoff; hot counters need atomic UPDATE or partitioning instead |
| Naming | PG/MySQL REPEATABLE READ = snapshot; Oracle SERIALIZABLE = snapshot (not true serializable!) |
Favorite interview trap — state the mapping explicitly |
What It Fixes and What It Doesn't
| Anomaly | Snapshot isolation? | Notes |
|---|---|---|
| Dirty read / dirty write | Prevented | Inherited from read committed |
| Non-repeatable read / read skew | Prevented | Whole-txn snapshot |
| Lost update | Prevented (abort) | First-committer-wins on same row |
| Write skew (predicate-based) | Allowed | Different rows → no write-write conflict; see Write Skew and Phantom Writes |
| Phantoms | Allowed | Concurrent inserts matching your earlier predicate are invisible but real at commit |
The pattern: snapshot isolation detects conflicts on rows actually written, not on predicates read. Anything where the decision depends on "absence of rows" or "sum over rows I didn't write" slips through — which is exactly the write-skew family.
Index and Lock Interaction
Snapshot readers take no locks (pure MVCC visibility check), so read scalability matches read committed. Writers take row locks to commit; the conflict check needs an index-efficient "find concurrent newer version" probe — unindexed hot updates degrade into scans plus lock waits. DDL (e.g. concurrent ALTER) can still block; snapshot isolation is about row data, not schema.
Trade-offs
| Approach | Pros | Cons | Use when |
|---|---|---|---|
| Snapshot isolation | Stable reads; auto lost-update protection; lock-free readers; low abort rates at normal contention | Write skew + phantoms; snapshot-pinned bloat; retry logic needed for 40001 |
Default for correctness-sensitive OLTP (PG repeatable read, InnoDB repeatable read) |
| Read committed | Slightly cheaper; fresher per-statement data; fewer aborts | Caller handles lost update + skew manually | Analytics on live data, low-stakes reads |
| Serializable (SSI/2PL) | Closes write skew + phantoms | Abort/lock overhead; tuning | Invariants over predicates (rosters, bookings, uniqueness-by-rule) |
Interview One-Liners
- Q: Snapshot isolation in one sentence? A: Every txn reads a frozen committed snapshot from BEGIN, and concurrent writes to the same rows trigger first-committer-wins aborts.
- Q: Why does snapshot isolation prevent lost update but allow write skew? A: Lost update touches the same row (write-write conflict detected); write skew touches different rows satisfying a shared predicate (no conflict detected).
- Q: Oracle
SERIALIZABLEvs real serializable? A: Oracle's label is actually snapshot isolation — it still permits write skew; true serializability needs 2PL/SSI/serial execution. - Q: What ops pain do long snapshots cause in Postgres? A: They pin dead tuples against VACUUM, bloating tables/indexes and slowing sequential scans until the snapshot closes.
- Q: How should apps handle
40001serialization aborts? A: Retry the whole transaction with capped exponential backoff + jitter; keep bodies idempotent and side-effect-free until commit. - Q: When is snapshot isolation insufficient? A: Any invariant over a predicate or aggregate (≥1 doctor on call, no double booking, balance ≥ 0 across rows) — escalate to serializable or explicit locking/constraints.