Databases
Part 3 of 6 · MVCC & isolationSSI vs Snapshot Isolation
Snapshot Isolation stops lost updates on the same row but allows write skew. Postgres SERIALIZABLE (SSI) tracks rw-conflicts and aborts one txn with SQLSTATE 40001; retry the whole transaction.
- 1Gist
- 2Maps
- 3Q&A
- 4Sandbox
Voice readout needs Web Speech Synthesis in this browser.
Overview
Snapshot Isolation is the isolation most “REPEATABLE READ” stories actually mean: every statement in the transaction sees the same committed prefix. It is cheap, readers do not block writers, and same-row lost updates cannot sneak through. It is not serializable.
Serializable Snapshot Isolation (SSI) is SI plus a conflict detector. PostgreSQL SERIALIZABLE is SSI (since 9.1). Ordinary reads stay non-blocking; the engine records what you read, and at commit it aborts you (or a peer) if the history is not serializable. The abort is SQLSTATE 40001. The application’s job is to retry the whole transaction.
This page is the mechanism. ANSI labels and engine matrices live on isolation levels. Versions: MVCC hub.
Why SSI wins for multi-row invariants
SI is the wrong default the moment the rule spans rows or “no row exists yet.” Locking every read is the other extreme. SSI is the middle path: keep SI’s non-blocking reads, pay CPU and occasional aborts.
| Path | Reads | Write skew | App duty |
|---|---|---|---|
| SI only (Postgres RR) | Non-blocking snapshot | Commits | You own the hole |
| FOR UPDATE on the read set | Block concurrent writers | Closed for those rows | Deadlock order, lock waits |
| UNIQUE / EXCLUDE / guard row | Ordinary | Closed if the invariant is physical | Schema design |
| SSI (winner when many multi-row rules, modest contention) | Non-blocking + SIREAD | Dangerous rw-cycle aborts | Retry 40001 |
- 1
first statement → one snapshot
Stay on SI (Postgres REPEATABLE READ)
Same-row lost updates abort. Two doctors both go off call. Two empty-shift reads both INSERT. First-committer-wins never fires because the writes are disjoint.
- 2
SIREAD / predicate locks → abort SQLSTATE 40001
Winner for many multi-row rules: SSI plus retry
The engine tracks rw-edges. A dangerous structure at commit rolls one txn back. The app restarts from BEGIN with backoff. No hand-rolled lock order for every invariant.
- ?
Locks or constraints when the key set is tiny and hot
High abort storms: serialize the read set with FOR UPDATE, or materialize the rule (guard row, EXCLUDE, CHECK). SSI is not cheaper than a single hot row lock.
Snapshot Isolation
At BEGIN (or first statement), the transaction gets a snapshot: a horizon of committed XIDs plus the in-progress list.
- Reads see the latest version whose inserter committed before the snapshot (and whose deleter had not).
- Write-write: two concurrent txns may not both commit updates to the same row. First committer wins; the loser errors (Postgres:
could not serialize access due to concurrent updateeven under RR). - Write skew: each txn reads a row (or predicate) the other will write, then writes a different row. No ww-conflict. Both commit. History is not serial.
SI is the right mental model for Postgres REPEATABLE READ. It is not MySQL RR (gap locks). See isolation levels.
Serializable Snapshot Isolation
SSI keeps SI’s snapshot and ww-rule, then watches read-write dependencies.
An rw-conflict (anti-dependency): T1 reads a version of X; T2 writes X (new version or insert into a predicate T1 read) and those txns overlap. T1 ran “too early” relative to T2’s write.
A dangerous structure is the pattern the literature cares about: overlapping transactions with two adjacent rw-edges, often drawn T1 →rw T2 →rw T3 with T1 and T3 concurrent. If committing you would close that shape, SSI aborts one participant rather than produce a non-serializable history.
Commit-time check. Writes are not blocked at read time. On COMMIT, the engine inspects the lock/dependency table. Clean → commit. Conflict → abort with 40001 (serialization_failure).
Read-only optimization. A transaction that never writes cannot create an rw-edge out of itself as a writer. Pure readers are much less likely to be aborted; Postgres also offers READ ONLY DEFERRABLE — wait for a snapshot that is known conflict-free, then run the report without SSI false positives. Use that for long reads, not for money writes.
Postgres is not 2PL SERIALIZABLE. InnoDB SERIALIZABLE turns SELECTs into locking reads. Do not port retry lore blindly.
Sequence
- 1
Client
Step 1 – BEGIN assigns snapshot T_start
- 2
Client → Engine SSI
BEGIN ISOLATION LEVEL SERIALIZABLE
- 3
Engine SSI → Client
snapshot
- 4
Client
Step 2 – read records a SIREAD lock (no wait)
- 5
Client → Engine SSI
SELECT rows matching predicate
- 6
Engine SSI → Client
versions as of snapshot
- 7
Client
Step 3 – write intent, still no reader-writer block
- 8
Client → Engine SSI
UPDATE or INSERT
- 9
Client
Step 4 – COMMIT scans for a dangerous rw-structure
- 10
Client → Engine SSI
COMMIT
- 11
Engine SSI
Step 5a – commit OK
- 12
Engine SSI → Client
COMMIT
- 13
Engine SSI
Step 5b – abort SQLSTATE 40001
- 14
Engine SSI → Client
ABORT serialization_failure
Lesson map
SSI vs Snapshot Isolation
Snapshot Isolation stops lost updates on the same row but allows write skew. Postgres SERIALIZABLE (SSI) tracks rw-conflicts and aborts one txn with SQLSTATE 40001; retry the whole transaction.
Architecture. Architecture
Select a node to see why it exists, or an edge to see the protocol, direction, effect, and consequence.
Mermaid export
flowchart TB c["Client"] db["Engine SSI"] c -->|BEGIN ISOLATION| db db -->|snapshot| c c -->|SELECT rows| db db -->|versions as of| c c -->|UPDATE or INSERT| db c -->|COMMIT| db
Conflict detection in PostgreSQL
SIREAD locks. A read of a row records a non-blocking SIREAD lock: bookkeeping, not a tuple exclusive lock. Another txn can still write; that write is exactly the rw-edge SSI needs to see.
Predicate locks. A range scan (WHERE shift = $1, WHERE on_call = true) cannot list rows that do not exist yet. SSI takes a predicate / page / relation lock so a later INSERT that would have matched is an rw-conflict with the reader. Coarse locks (page or table) raise false positive abort rates. Keep transactions short and predicates selective.
Who aborts. If a later writer touches a row (or predicate) an overlapping reader saw, commit-time logic may abort the reader or the writer — whichever closes the dangerous structure. Treat either 40001 as “restart the unit of work.”
Long-running SERIALIZABLE transactions also pin snapshots and fight VACUUM’s horizon. SSI and bloat share an enemy: idle-in-transaction.
| Layer | SI-only | SSI-augmented |
|---|---|---|
| Transaction manager | Snapshot, first-committer-wins | Same plus dangerous-structure check at commit |
| Lock table | Ordinary tuple locks if you asked | SIREAD + predicate locks (non-blocking) |
| Executor | Read snapshot, write new versions | Same; commit may abort |
| Application | May ignore serialization errors | Must catch 40001 and retry the whole body |
WAL still only means durability. Isolation lives in snapshots plus (for SSI) the dependency table. Recovery replays committed writes; it does not invent serializability after the fact.
Doctors: two write-skew shapes
At least one stays on (hub classic). Alice and Bob both on. T1 and T2 each read both rows, each turns a different doctor off. SI commits both. SSI sees each txn read the row the other wrote → dangerous rw-cycle → one 40001.
At most one takes an empty shift. T1 and T2 both SELECT FROM oncall WHERE shift = $s and see empty, both INSERT. SI: two rows, invariant dead (this is write skew with a phantom insert). SSI: the predicate lock on “rows for this shift” conflicts with the peer’s INSERT. UNIQUE(shift) also closes this particular shape — use a constraint when the rule is “at most one row with this key.”
Constraints do not express “at least one of these two rows stays true.” That needs SSI, FOR UPDATE on both rows, or a guard row with CHECK (on_call_count >= 1).
Constraints vs FOR UPDATE vs SSI
| Tool | Closes | Cost |
|---|---|---|
| UNIQUE / PK | Duplicate keys, “at most one row for this shift” | Tiny; use it |
| EXCLUDE / GiST | Non-overlapping ranges, “no two on-call in this dept” | Index maintenance; see phantoms vs write skew |
SELECT … FOR UPDATE | Decision on existing rows; writers wait | Blocking, deadlocks; row locks page |
| SSI | Dangerous rw-cycles including many phantoms | Aborts, false positives, retry loop |
SELECT … FOR SHARE is a shared tuple lock. It does not replace SSI. Optional extra blocking; the SIREAD bookkeeping is what SSI needs.
Retry loops
Rules that fail interviews:
- Retry only the failing statement inside an open txn
- Commit partial work, then retry
- Side effects outside the DB (charge the card, send email) inside the SSI body without an idempotency key
The body must be safe to run N times. Catch 40001 / SerializationFailure, ROLLBACK, sleep base * 2^attempt + jitter, BEGIN ISOLATION LEVEL SERIALIZABLE again, cap attempts. Log abort rate: if it climbs, the workload wants locks or a constraint, not more retries.
Decisions
- 1
Step 1 BEGIN SERIALIZABLE
- nextStep 2 read plus write
- 2
Step 2 read plus write
- nextStep 3 COMMIT
- 3
Step 3 COMMIT
- nextdangerous structure?
- ?
dangerous structure?
- nodone
- 40001Step 4 ROLLBACK
- 5
done
- 6
Step 4 ROLLBACK
- nextStep 5 backoff plus jitter
- 7
Step 5 backoff plus jitter
- nextattempts left?
- ?
attempts left?
- yesStep 1 BEGIN SERIALIZABLE
- nofail the request
- 9
fail the request
In-memory SSI (run this)
Simulate SIREAD sets and a dangerous rw-cycle. I/O contract: two txns’ read-sets and write-sets in; whether SI would commit both and whether SSI aborts.
Press Run. Snippets must be self-contained — no network, files, or native modules.
Deep dive · Layers: xact, predicate locks, WAL, app retry
The transaction manager still assigns XIDs and snapshots the way SI does. SSI adds a lock table of SIREAD and predicate locks (predicate.c in Postgres lore) that do not block ordinary writers. At commit, dangerous-structure detection consults that table. WAL remains durability of the writes; it is not the isolation mechanism. The application layer is part of the protocol: ignoring 40001 turns SSI into “randomly dropped transactions.” Ports and Grittner (VLDB 2012) and Cahill’s SSI paper are the canonical write-ups; InterDB’s Postgres chapter is the readable walk through SIREAD edges.
Interview Q&A
What does Snapshot Isolation guarantee, and what does it not?
Answer
One consistent committed snapshot; no dirty reads; no non-repeatable reads of rows that existed at snapshot time; first-committer-wins on the same row. It does not guarantee a serial history. Write skew (disjoint writes after overlapping reads) commits. Postgres REPEATABLE READ is SI. Saying “SI is serializable” is the trap.
What is an rw-conflict and what is a dangerous structure?
Answer
rw: T1 read a version (or predicate) that T2 wrote while they overlapped — T1’s read would be invalid if T2 were ordered first. wr is the ordinary write-then-read dependency. A dangerous structure is overlapping txns with two adjacent rw-edges (the SSI abort trigger). SSI does not wait; it aborts at commit.
What is a SIREAD lock? Does it block?
Answer
Bookkeeping that T_serializable read this row (or this page/predicate). It does not block writers. The writer still creates a new version; SSI uses the SIREAD entry to notice the rw-edge. Confusing SIREAD with FOR SHARE is a common mix-up — FOR SHARE is a real shared tuple lock.
Walk the empty-shift INSERT write skew and how SSI fixes it.
Answer
Invariant: at most one doctor on a shift. T1 and T2 both select where shift = am, both see empty, both insert, both commit under SI. UNIQUE(shift) would also reject the second insert. SSI’s predicate lock on that shift conflicts with the peer INSERT and aborts one with 40001. Constraints are better when they fully encode the rule; SSI still wins for “at least one of these rows stays true.”
What is SQLSTATE 40001 and what must the app retry?
Answer
Serialization failure (SSI), or in some engines a deadlock mapped the same way. ROLLBACK, backoff with jitter, new BEGIN ISOLATION LEVEL SERIALIZABLE, re-run every read and write of the logical unit. Never retry one statement inside a half-done txn. Cap attempts; treat a high rate as a design smell.
When do you pick FOR UPDATE or a constraint instead of SSI?
Answer
Tiny hot key set (two doctors, one guard row): row locks have less abort storm. Rule is “at most one row with this key”: UNIQUE. Overlapping ranges: EXCLUDE. Many scattered multi-row invariants with modest contention: SSI + retry is less code than a lock plan per use case. See SELECT FOR UPDATE.
Are read-only SERIALIZABLE transactions aborted?
Answer
They cannot be the writer in an rw-edge. Postgres still may abort them in some conflict graphs; READ ONLY DEFERRABLE waits for a snapshot that will not be. Use it for reports. Do not use a 30-minute interactive txn as SERIALIZABLE — you pin the vacuum horizon and collect SIREAD locks.
Why do predicate locks cause false positives?
Answer
SSI cannot track every possible future row cheaply, so it locks a page or relation. An unrelated INSERT on the same page can look like a phantom conflict. Aborts that “should not have happened” are expected. Selective predicates, short txns, and indexes that make the scan precise keep the rate down.
Does MySQL InnoDB implement SSI when you SET SERIALIZABLE?
Answer
No. InnoDB SERIALIZABLE makes SELECTs locking reads (2PL-ish). Postgres SERIALIZABLE is SSI. MariaDB has moved toward stricter modes; still do not assume 40001 retry lore ports. Quote the engine. Matrix: isolation levels.
How do you keep SSI retry idempotent when the txn sends an email?
Answer
Keep the SERIALIZABLE body inside the database. Side effects go after a successful commit, keyed by an idempotency token, or use outbox rows committed in the same txn. Charging a card inside the retry loop double-charges on a success-then-timeout. The playground retry helper is the control flow; the outbox is the safety.
Pitfalls
Draw two overlapping txns for both doctor shapes (at-least-one UPDATE vs empty-shift INSERT). Mark ww-edges (none) and rw-edges (each read the other’s write / predicate). Circle the dangerous structure. Write a 6-line retry loop in the language you interview in: catch 40001, rollback, jitter, restart from BEGIN. List one invariant you would rather put in UNIQUE than in SSI.
Go Deeper
- PostgreSQL — Serializable
- InterDB — SSI in PostgreSQL
- Ports and Grittner — Serializable Snapshot Isolation in PostgreSQL (VLDB 2012)
- Cahill, Röhm, Fekete — SSI
Cluster: MVCC hub · Isolation levels · FOR UPDATE · VACUUM · Phantoms vs write skew
One-line takeaway: SI is a snapshot plus same-row first-committer-wins; SSI adds rw-conflict detection and 40001 — retry the whole txn, or lock/constrain the hot invariant instead.