Snapshot isolation gives every transaction a consistent view of the database and never lets two concurrent transactions overwrite the same row, yet a workload run entirely under snapshot isolation can still end in a state that no serial order of those same transactions could produce. Explain how snapshot isolation works and why that gap exists.
answer
- snapshot at start + write-write check only
- anti-dependency edges invisible to SI
- two consecutive rw edges = pivot
- write skew: read a set, write another row
- read-only transactions can also see anomalies
basics
~20 sSnapshot isolation reads a consistent snapshot fixed at transaction start and checks only write-write conflicts at commit. Two transactions that read overlapping data but write different rows never conflict, so read-write dependencies go unchecked. That gap allows write skew.
solid answer
~50 sUnder snapshot isolation (SI), a transaction reads a version-consistent snapshot of the database as of its start; writes create new row versions, and the single conflict rule is on writes — if two concurrent transactions update the same row, one aborts. That eliminates dirty reads, non-repeatable reads, and lost updates, which is why SI feels serializable. But serializability means the outcome must equal *some* serial execution, and SI polices only write-write overlap. If two concurrent transactions each read data the other writes, while writing disjoint rows, SI sees no conflict and commits both. Each decided on a state the other invalidated — a read-write anti-dependency cycle that no serial order reproduces. The canonical result is write skew: an invariant spanning several rows holds in each snapshot and is broken by the union of the writes. Formally, every non-serializable SI history contains a cycle with two consecutive read-write anti-dependency edges — the insight that later produced Serializable Snapshot Isolation.
code
sql · 10 lines-- T1 (snapshot at t0) -- T2 (snapshot at t0)
SELECT count(*) FROM shift SELECT count(*) FROM shift
WHERE on_call AND day = '2026-08-12'; WHERE on_call AND day = '2026-08-12';
-- returns 2 -- returns 2
UPDATE shift SET on_call = false UPDATE shift SET on_call = false
WHERE doctor = 'alice' WHERE doctor = 'bob'
AND day = '2026-08-12'; AND day = '2026-08-12';
COMMIT; COMMIT;
-- both succeed: zero doctors on callgo deeper
Be able to state that snapshot isolation means you read a frozen consistent copy taken at transaction start, and that the engine only stops two transactions from writing the same row.
Explain the mechanism (MVCC snapshot + write-write conflict), name the anomalies it removes, and give one concrete write-skew example showing why disjoint writes escape the check.
Frame it as a dependency graph: SI breaks cycles through write-write and write-read edges but never inspects read-write anti-dependencies, and say how you would close it in production (true serializable, explicit locking of read rows, materialized conflict, or a database constraint).
Discuss it as a correctness-versus-throughput contract: which invariants you are willing to enforce in the engine versus in the schema, the cost of the abort-and-retry regime that true serializability implies, and how you keep the choice explicit rather than inherited from a default.
## What snapshot isolation actually guarantees Snapshot isolation is normally built on multi-version concurrency control (MVCC). Every write produces a new *version* of a row tagged with the writing transaction's identity, and old versions stay around until nobody can still see them. When a transaction begins (or takes its first snapshot), the engine records which transactions had committed at that instant. From then on, every read the transaction issues returns the newest version that was committed as of that instant, plus the transaction's own writes. This gives two properties people love: reads never block writes and writes never block reads, and the transaction sees a single unchanging state of the whole database, no matter how long it runs. On the write side, SI adds exactly one rule: two concurrent transactions may not both modify the same row. Whoever commits first wins; the loser aborts with a serialization/conflict error. Depending on the engine this is enforced at commit time (first-committer-wins) or at update time by blocking on the row (first-updater-wins), but the effect is the same — concurrent updates to the same row do not both survive. ## Which anomalies that removes Because reads come from a frozen snapshot, a transaction can never see uncommitted data (no dirty read), can never see a value change under it (no non-repeatable read), and a repeated range query returns the same rows (no phantoms *within the snapshot*). Because of the write rule, the classic lost update — both transactions read a counter as 10, both write 11 — cannot happen. That covers every anomaly in the original SQL isolation-level table, which is exactly why some engines historically labelled SI as REPEATABLE READ, or even as SERIALIZABLE. ## Why that is still not serializability Serializability is not defined by a checklist of anomalies. It says: the final state and everything each transaction returned must be identical to *some* order in which the transactions ran one after another with nothing in between. SI's conflict rule only detects **write-write** overlap. It is blind to the case where transaction A *reads* something that transaction B *writes*, when their write sets do not intersect. Model it as a dependency graph over concurrent transactions. Edges come in three kinds: write→read (B reads what A wrote), write→write (B overwrites what A wrote), and read→write, called an **anti-dependency** — A read a value, then B wrote a new version of it, so A must be ordered *before* B. An execution is conflict-serializable when this graph has no cycle. SI structurally prevents cycles built from write-write and write-read edges: the snapshot rule means a transaction never reads a concurrent write, and the write rule means concurrent writes to one item cannot both commit. What SI never inspects is anti-dependency edges. A cycle made of two anti-dependencies — A read what B wrote, B read what A wrote — commits happily, and no serial order matches it. Fekete et al. proved the sharp form of this: in any non-serializable execution under SI there is a cycle containing **two consecutive read-write anti-dependency edges**, with a "pivot" transaction that has both an incoming and an outgoing anti-dependency edge. That theorem is what makes runtime detection tractable, and it is the basis of Serializable Snapshot Isolation. ## The visible symptom: write skew The practical consequence is write skew. Each transaction reads a *set* of rows, checks an invariant over that set, and then writes a *different* row. Both snapshots satisfy the invariant, both writes are legal on their own, the union breaks it: two on-call doctors each check that at least one other doctor is on shift and each takes themself off; two bookings each check the room is free for the slot and each insert a reservation; two withdrawals each check that the *combined* balance of a customer's accounts stays positive and each drain one account. There is also a subtler read-only anomaly: under SI, a transaction that writes nothing at all can observe a state inconsistent with any serial order, so "it only reads" is not a safety argument. ## What to do about it Three honest options. Run true serializability (an SSI implementation, or strict two-phase locking) and handle serialization failures with retry. Keep SI and *manufacture* a write-write conflict where the real conflict is read-write — lock the rows you read explicitly, bump a shared "guard" row for the group the invariant spans, or materialize the conflict as a row per slot/resource so two transactions collide on it. Or express the invariant declaratively so the engine enforces it: a unique constraint or exclusion constraint is checked against actual committed data, not against your snapshot, so it catches the pair. What does *not* work is adding a re-read before commit, or assuming a longer/shorter transaction changes the outcome.
- If snapshot isolation prevents dirty reads, non-repeatable reads, and phantoms, why do the SQL standard's isolation levels not capture the problem?The standard defines levels by prohibiting three named phenomena that were originally described in lock-based terms. Snapshot isolation avoids all three yet still permits non-serializable histories, because those three do not enumerate every way a dependency cycle can form. This is the well-known critique of the standard: the anomaly list is not equivalent to serializability, which is why a level can satisfy the checklist and still be weaker than SERIALIZABLE.
- Does making one of the transactions read-only make snapshot isolation safe?No. Under SI a read-only transaction can observe a state that no serial order would produce — the read-only anomaly — because it can be the transaction that closes the dependency cycle. Its snapshot can reflect one of two concurrent writers but not the other in a way that is mutually inconsistent. Some engines offer a deferrable read-only mode that waits for a safe snapshot precisely to avoid this.
- Under snapshot isolation, can a transaction read a value, then have a concurrent transaction change that value, and still commit?Yes, and this is exactly the hole. The reader keeps seeing its snapshot value, the writer commits a new version, and unless the reader also wrote that same row nothing detects the overlap. The reader's decision is now based on data that is stale with respect to the committed state, but SI never revalidates reads at commit time.
Two people share a joint account with a rule that the balance must stay above zero. Each phones the bank, is told the balance, and each withdraws from a different sub-account. Neither withdrawal conflicts with the other — but the rule they both checked is now broken.
saying these in an interview costs you the question
- Saying snapshot isolation is serializable because it prevents dirty reads, non-repeatable reads, and phantoms
- Claiming write skew is just a lost update, or that first-committer-wins prevents it
- Believing that re-reading the rows just before commit closes the gap — the re-read still comes from the same snapshot
- Assuming a read-only transaction cannot participate in an anomaly
- Thinking longer or shorter transactions change whether SI is serializable rather than only how often the anomaly is hit