Snapshot isolation architecture, in depth: how snapshots are built, the commit check, write skew, SSI and distributed timestamps

The definition, from first principles

Snapshot isolation was named and defined in the 1995 paper A Critique of ANSI SQL Isolation Levels by Berenson, Bernstein, Gray, Melton and the O'Neils. The definition has two rules.

1. Snapshot reads. A transaction reads data from the snapshot of committed data as of its start timestamp. Its own writes are also visible to it. Writes committed by others after the start are invisible, no matter how long the transaction runs.
2. First-committer-wins. A transaction T may commit only if no other transaction that committed in the interval between T's start and T's commit wrote any item that T also wrote. Otherwise T aborts.

Rule 2 compares write sets against write sets; reads are never checked. That is where both the performance and the anomalies of SI come from: readers cost nothing to coordinate, and the database cannot tell when a decision was based on data someone else was changing.

How a snapshot is represented

A snapshot has to answer one question cheaply for every row version a query touches: was the transaction that created this version committed before my snapshot? Engines answer it in two broad ways.

Transaction-id snapshots (PostgreSQL). PostgreSQL assigns a transaction id (txid) to each writing transaction and stamps every tuple with xmin (the txid that created it) and xmax (the txid that deleted or superseded it, or zero). A snapshot is three things: the snapshot's xmin (the oldest txid still running), its xmax (the first txid not yet assigned) and the list of txids that were in progress when the snapshot was taken. You can look at one directly:

SELECT pg_current_snapshot();
--  pg_current_snapshot
-- ----------------------
--  7841:7849:7841,7845
-- txids below 7841 are finished; 7849 and above had not started;
-- 7841 and 7845 were in progress and must be treated as invisible.

A tuple version is visible to the snapshot when its creating txid committed, is below the snapshot's xmax and is not in the in-progress list, and when its deleting txid is zero, aborted, or itself not visible under the same rule. Commit status comes from the commit log (pg_xact), with hint bits cached on the tuple so the lookup is usually free. (pg_current_snapshot() exists from PostgreSQL 13; older releases use txid_current_snapshot().)

Timestamp snapshots (most distributed engines). Each commit gets a monotonically increasing timestamp and each version is stored under key plus timestamp. A snapshot is one number, the start timestamp; a read returns the newest version at or below it. Simple and portable between nodes, but it needs a timestamp source every participant agrees on.

Timing matters too. In PostgreSQL REPEATABLE READ the snapshot is taken at the first statement, not at BEGIN; READ COMMITTED takes a fresh snapshot per statement. SQL Server's READ_COMMITTED_SNAPSHOT option gives that statement-level behaviour, while its SNAPSHOT level is transaction-level SI.

The commit check: first-committer-wins versus first-updater-wins

The textbook rule checks at commit. Real engines often check earlier, at the conflicting write, when the information is cheapest and the wasted work smallest.

• First-committer-wins. Writes are buffered; at commit the engine checks whether any written key has a committed version newer than the start timestamp. The loser learns of the conflict only after doing all its work.
• First-updater-wins. A writer takes a row lock. A second updater of the same row waits; if the holder commits, the waiter aborts, and if the holder aborts, the waiter proceeds. PostgreSQL reports could not serialize access due to concurrent update (SQLSTATE 40001), Oracle ORA-08177, SQL Server error 3960.

Both forbid lost updates: two transactions cannot both read a balance, add to it and write it back. That is the main practical difference from READ COMMITTED.

MySQL's InnoDB REPEATABLE READ differs: plain SELECT uses a snapshot, but UPDATE, DELETE and locking reads act on the latest committed row, without a write-conflict abort by default. A read-then-write cycle can lose an update there. Treat it as a different level sharing a name, and test your exact version.

What escapes: write skew

Because the commit check compares only write sets, two transactions that read overlapping data and write disjoint data both commit. The canonical example is an on-call rota with the invariant that at least one doctor must be on call.

-- Both run concurrently under SI, both start with alice and bob on call.
-- T1 (alice)                                  -- T2 (bob)
BEGIN ISOLATION LEVEL REPEATABLE READ;          BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM oncall                      SELECT count(*) FROM oncall
 WHERE shift = 7 AND on_call;   -- 2              WHERE shift = 7 AND on_call;   -- 2
UPDATE oncall SET on_call = false                UPDATE oncall SET on_call = false
 WHERE shift = 7 AND doctor = 'alice';            WHERE shift = 7 AND doctor = 'bob';
COMMIT;  -- ok                                   COMMIT;  -- ok: different row
-- Now nobody is on call, a state no serial order could produce.

Each checked the invariant in its own snapshot, and the writes touched different rows. Double-booking, app-level uniqueness and quota checks share this shape. The fixes turn the hidden read dependency into a write conflict or lock:

• Lock what you read with SELECT ... FOR UPDATE. It does not stop a phantom insert.
• Materialise the conflict: a row representing the protected thing that every writer updates.
• Use a constraint. Unique and exclusion constraints check the latest data, not the snapshot.
• Run at SERIALIZABLE on an SSI engine and retry.

The less famous one: the read-only anomaly

Fekete, O'Neil and O'Neil showed in 2004 that under SI even a transaction that only reads can observe a state inconsistent with every serial order. Take a checking balance X and savings balance Y, both 0. T2 withdraws 10 from checking and charges a 1 overdraft fee if X + Y would go negative. T1 deposits 20 into savings. T3 is a read-only report.

1. T2 starts and reads X = 0, Y = 0.
2. T1 writes Y = 20 and commits.
3. T3 starts, reads X = 0 and Y = 20, commits and prints the report.
4. T2, using its old snapshot, computes 0 + 0 - 10 < 0, writes X = -11 and commits. No write-write conflict: T1 wrote Y, T2 wrote X.

The report shows the deposit applied and the withdrawal not yet made, so the deposit came first; but then no fee should have been charged. No serial order explains both. A reporting transaction is not safe just because it does not write. For audit-grade reports PostgreSQL offers SERIALIZABLE READ ONLY DEFERRABLE, which waits for a snapshot guaranteed safe and then cannot fail.

Serializable snapshot isolation: tracking reads after all

SSI, from Cahill, Rohm and Fekete (2008) and implemented in PostgreSQL 9.1 by Ports and Grittner, keeps SI's non-blocking reads and adds the minimum bookkeeping needed to reject non-serializable outcomes. The theory says every non-serializable SI execution contains a dangerous structure: two consecutive read-write antidependencies, T1 -rw-> T2 -rw-> T3, where T1 read something T2 later overwrote, T2 read something T3 overwrote, and the transactions overlapped. T2 is the pivot. (T1 and T3 may be the same transaction, as in the doctor example.)

The implementation records reads as predicate locks (PostgreSQL calls them SIRead locks) that never block anyone. When a write hits a key or range covered by a concurrent transaction's SIRead lock, the engine records an rw-conflict edge. When a pivot with both an incoming and an outgoing edge appears and the commit order makes it dangerous, one participant is aborted with SQLSTATE 40001. The application retries the whole transaction, using a loop like the one in transaction isolation architecture.

-- Rough sketch of the per-write check SSI adds (not engine source).
on write(key) by T:
    for R in transactions holding SIRead on key, concurrent with T:
        add edge R -rw-> T
        if R.has_in_edge and R.has_out_edge and dangerous_commit_order(R):
            abort(one of the three)          -- SQLSTATE 40001

SSI has false positives: tracking is coarsened (row locks are promoted to page and relation locks when memory runs short) and the test is conservative. Keep serializable transactions short, declare read-only ones READ ONLY, and make sure index scans back your predicates, since a sequential scan takes a relation-level SIRead lock that conflicts with every writer.

Distributed snapshot isolation: where timestamps come from

On one node, a counter or the transaction-id machinery provides the order. Across many nodes the database needs start and commit timestamps that every shard agrees on. Three designs dominate.

• Centralised timestamp oracle. Google's Percolator (Peng and Dabek, 2010) used one oracle handing out increasing timestamps in batches. A transaction fetches start_ts and reads at or below it, then commits with two-phase commit: prewrite places a lock and data on every key, one designated primary; then it fetches commit_ts and writes the commit record on the primary, the atomic commit point. TiKV uses this model with its Placement Driver as the oracle, which becomes a throughput and availability dependency.
• Hybrid logical clocks. CockroachDB and YugabyteDB use hybrid logical clocks, physical time plus a logical counter. Because node clocks are only loosely synchronised, a read can meet a version just above its timestamp but inside the uncertainty window, and must restart or move its timestamp forward. The maximum clock offset becomes a correctness parameter.
• Bounded-uncertainty clocks. Spanner's TrueTime exposes an explicit error interval and makes commits wait it out, trading commit latency for external consistency.

Write skew and the cost of long snapshots carry over unchanged. See two-phase commit for the commit half and clocks in distributed systems for why physical time alone cannot be trusted.

Worked example: an inventory reservation service

A shop reserves stock by reading remaining stock (stock row minus active reservations) and inserting a reservation row if enough is left. Under SI, two buyers of the last unit both read "1 remaining", both insert, and both commit: inserts are new rows, so there is no write-write conflict. Under load on a hot SKU this oversells.

Option one: materialise the conflict with a per-SKU counter (UPDATE stock SET reserved = reserved + 1 WHERE sku = $1 AND reserved < on_hand). Concurrent buyers collide on that row; the loser gets a serialization failure, retries, sees zero and fails cleanly. Hot-SKU throughput is bounded by row-lock hold time, so keep the transaction tiny.

Option two: run at SERIALIZABLE and retry. Correct without schema changes; with good indexes, aborts stay confined to real contention. If the abort rate climbs above a few percent, the counter row is cheaper. Many teams use option one on hot paths and SERIALIZABLE as the default elsewhere, because it protects invariants nobody remembered to materialise.

Operating it: long snapshots are expensive

• Old snapshots pin old versions. In PostgreSQL the oldest snapshot sets the horizon below which vacuum may remove dead tuples; one transaction open for hours bloats the whole database. Set idle_in_transaction_session_timeout, alert on the oldest backend_xmin in pg_stat_activity, and watch replication slots and hot_standby_feedback. See vacuum and bloat.
• Serialization failures are normal traffic. Retry the whole transaction on SQLSTATE 40001 and export retries per transaction type.
• Replicas. Long standby queries are cancelled by replay conflicts or, with feedback, bloat the primary. Route long reports elsewhere.
• Test your level name with the doctor and lost-update examples in CI.

Trade-offs

What to do next

1. Confirm which mechanism each database uses at the level you run, with the doctor and lost-update tests.
2. List invariants checked by reading one row and writing another; each is a write-skew candidate.
3. For each, choose a fix: a constraint, a materialised conflict row, FOR UPDATE, or SERIALIZABLE with retries.
4. Wrap every transaction in a retry loop that handles SQLSTATE 40001 and re-runs the whole unit of work.
5. Set idle_in_transaction_session_timeout and alert on the oldest snapshot age.
6. Use READ ONLY DEFERRABLE for financial or audit reports on PostgreSQL.
7. If you run a distributed SQL database, learn its timestamp source and its clock-offset limit, and monitor clock skew as a correctness signal.