vault

Snapshot Isolation

12 min read

Snapshot Isolation

Snapshot Isolation (SI) is the isolation level where every transaction reads from a consistent snapshot of the database taken at its start — seeing exactly the data committed before it began, and none of the changes made by transactions that overlap it — while write-write conflicts are resolved by a first-committer-wins rule. Cahill, Röhm & Fekete describe it precisely: “In SI, a transaction T sees the database state as produced by all the transactions that committed before T starts, but no effects are seen from transactions that overlap with T … reads are never delayed because of concurrent transactions’ writes, nor do reads cause delays in a writing transaction” (Serializable Isolation for Snapshot Databases, SIGMOD 2008). SI is fast, intuitive, and prevents every anomaly the ANSI SQL standard names — dirty reads, non-repeatable reads, phantoms, lost updates. But it is not serializable: it permits write skew and a read-only transaction anomaly. That gap — SI feels serializable but isn’t — is the single most important and most commonly misunderstood fact in transaction isolation, and the reason SSI exists.

Uncertain

Verify: the mapping “PostgreSQL Repeatable Read = SI; Oracle SERIALIZABLE = SI (not true serializability); InnoDB Repeatable Read = MVCC snapshot + next-key locks.” Reason: isolation-level names are engine-specific and routinely conflated; the underlying facts are pinned to PostgreSQL current docs, MySQL 8.4 docs, and the Cahill/Berenson papers as of 2026-07, but Oracle’s behavior is cited from the SI literature rather than a fetched Oracle primary. To resolve: confirm against Oracle’s own concurrency documentation for the target release. uncertain

Mental Model: A Photograph of the Database

Think of SI as handing each transaction a photograph of the database taken the instant it began. The transaction reads only from its photograph — never seeing later commits, never blocked by them, never blocking them. When it writes, it works on its own copy and, at commit, the system enforces one rule: if any concurrent transaction already committed a write to the same item, this transaction is aborted. That is first-committer-wins (equivalently first-updater-wins). Because reads come from a frozen photograph, two successive reads always agree (no non-repeatable read), a range query re-run sees the same rows (no phantom in the reader’s snapshot), and you never read uncommitted data (no dirty read).

flowchart TB
  T1["T1 starts @ snapshot S1<br/>reads x=50, y=50"]
  T2["T2 starts @ snapshot S2<br/>reads x=50, y=50"]
  T1 -->|"writes x = -20"| C1["T1 commits"]
  T2 -->|"writes y = -30"| C2["T2 commits"]
  C1 -.->|"different items:<br/>NO write-write conflict"| OK["both commit"]
  C2 -.-> OK
  OK --> SKEW["Result: x=-20, y=-30<br/>invariant x+y&gt;0 VIOLATED<br/>= WRITE SKEW"]

Two transactions under SI reading the same snapshot and writing different items. What it shows: because T1 writes x and T2 writes y, first-committer-wins finds no conflict (it only checks same-item writes), so both commit. The insight: each transaction individually preserved the invariant x + y > 0 based on the snapshot it read, but the concurrent combination violates it. SI’s write-write check is blind to conflicts that span different items read-then-written — that blind spot is exactly write skew, and it is why SI is not serializable.

First-Committer-Wins: The Only Write Conflict SI Catches

SI does prevent the lost update anomaly that plagues Read Committed. Cahill et al.: “In order to prevent Lost Update anomalies, SI does abort a transaction T when a concurrent transaction commits a modification to an item that T wishes to update. This is called the ‘First-Committer-Wins’ rule.” Concretely, if T1 and T2 both read balance=100 and both try to write balance=90, SI lets only the first committer through; the second, discovering that a concurrent transaction already modified the row, aborts. In PostgreSQL this surfaces as a first-updater-wins abort: “the repeatable read transaction will be rolled back with the message ERROR: could not serialize access due to concurrent update” (PostgreSQL docs), carrying SQLSTATE 40001. The application is expected to “abort the current transaction and retry the whole transaction from the beginning.” Read-only transactions never hit this — “only updating transactions might need to be retried.”

The subtlety is the scope of “conflict”: first-committer-wins checks only whether two transactions wrote the same item. It says nothing about a transaction that reads one item and writes another. That is the hole.

Write Skew: The Canonical Hole

The textbook example, from the SSI paper itself, is a doctors on-call roster. A table Duties(DoctorId, Shift, Status) records each doctor as on duty or reserve for a shift, with an “undeclared invariant that … there must be at least one doctor on duty” in every shift. An application lets a doctor go off duty only if someone else is still on:

BEGIN TRANSACTION;
UPDATE Duties SET Status = 'reserve'
 WHERE DoctorId = :D AND Shift = :S AND Status = 'on duty';
SELECT COUNT(DISTINCT DoctorId) INTO tmp
  FROM Duties WHERE Shift = :S AND Status = 'on duty';
IF (tmp = 0) THEN ROLLBACK ELSE COMMIT;   -- refuse to leave the shift empty

This program is individually correct: run alone it preserves the invariant. But “suppose there are exactly two doctors D1 and D2 who are on duty in shift S. If we run two concurrent transactions, which run this program for parameters (D1, S) and (D2, S) respectively, we see that using SI as concurrency control will allow both to commit (as each will see the other doctor’s status for shift S as still unchanged, at ‘on duty’). However, the final database state has no doctor on duty in shift S, violating the integrity constraint” (Cahill et al. 2008). Each transaction reads the other doctor (seeing them on duty in its snapshot) and writes itself off duty. They write different rows, so first-committer-wins sees no conflict; both commit; the invariant breaks. This is write skew — formally, two concurrent transactions read overlapping data and write disjoint data in a way whose combination has no serial equivalent.

The equivalent bank example: accounts x=50, y=50, invariant x + y > 0. T1 withdraws 70 from x (reads both, sees 100 total, allows it), T2 withdraws 80 from y (same), both commit; x + y = −50. As Berenson et al. named it in “A Critique of ANSI SQL Isolation Levels” (SIGMOD 1995), this is the anomaly the phenomenological ANSI definitions fail to capture — precisely why the paper argues those definitions are ambiguous and why “Snapshot Isolation” needed a separate, operational definition.

The Read-Only Transaction Anomaly

Even more surprising: SI can produce a non-serializable result observed by a read-only transaction, discovered by Fekete, O’Neil & O’Neil (2004) and reproduced in the SSI paper. Consider three transactions on items x (a bank balance) and y (a savings balance):

  • T0: r(y) w(x) — applies a batch that depends on y, writes x.
  • T1: w(y) w(z) — deposits into y.
  • TN: r(x) r(z) — a read-only report reading x and z.

There exist interleavings where “TN, a read-only transaction, sees a state that could never have existed had T0 and T1 executed serially. If TN is omitted, T0 and T1 are serializable because there is only a single anti-dependency from T0 to T1.” The read-only observer is what creates the anomaly. The teaching point is jarring: adding a transaction that only reads can turn an otherwise-serializable SI schedule non-serializable — proof that SI’s guarantee genuinely differs from serializability, not just in edge cases involving writes.

SI in Real Engines

SI is the de facto meaning of “Repeatable Read” in the MVCC engines. PostgreSQL is explicit: its Repeatable Read “is implemented using a technique known in academic database literature and in some other database products as Snapshot Isolation,” and it takes “a snapshot as of the start of the first non-transaction-control statement in the transaction.” InnoDB’s default Repeatable Read is MVCC snapshot reads where “consistent reads within the same transaction read the snapshot established by the first read” (InnoDB docs) — though InnoDB additionally takes next-key locks on locking reads (FOR UPDATE), which pushes it slightly beyond textbook SI toward preventing some write skew via gap locks. Oracle’s “SERIALIZABLE” level has historically been snapshot isolation, not true serializability — a famous naming trap. The Cahill paper lists the SI adopters: “it has been implemented by the Oracle RDBMS, PostgreSQL, SQL Server 2005, and Oracle Berkeley DB.”

A mechanistic subtlety worth pinning down: PostgreSQL and InnoDB enforce the same first-committer-wins rule by different means, with different developer-visible behavior. PostgreSQL is abort-based: an updater that finds its target row was changed by a concurrent committed transaction is immediately rolled back with 40001 (“could not serialize access due to concurrent update”), and the application retries. InnoDB is lock-based: because its Repeatable Read takes exclusive next-key locks on the rows it updates, a second writer blocks on the first writer’s lock rather than aborting outright. Per the InnoDB docs, “the repeatable read transaction will wait for the first updating transaction to commit or roll back” — and only then decides whether it has a conflict. The consequence: identical write-write contention produces an instant retry error on PostgreSQL but a lock wait (up to innodb_lock_wait_timeout, default 50 s) followed by possible success or deadlock on InnoDB. This is the same isolation semantics delivered by opposite concurrency-control mechanisms — abort-and-retry (optimistic) versus block-and-wait (pessimistic) — and it explains why porting write-heavy code between the two engines changes its failure profile even at the “same” isolation level.

The Cahill paper also notes why vendors love SI despite its hole: “SI has become popular with DBMS vendors. It often gives much higher throughput than strict two-phase locking, especially in read-heavy workloads, and it also provides users with transaction semantics that are easy to understand.” SI is the sweet spot of performance and intuitiveness — which is exactly why its non-serializability is so dangerous: it is almost right, and the failures are subtle multi-row invariant violations rather than obvious corruption.

Detecting and Fixing Write Skew

Before SSI, the standard fix was to manually introduce a write-write conflict so that first-committer-wins would catch the skew. Techniques:

-- (1) Materialize the conflict: make each transaction WRITE a shared "guard" row
--     so two concurrent runs collide under first-committer-wins.
UPDATE shift_guard SET touched = touched + 1 WHERE shift = :S;   -- now both writers conflict
 
-- (2) Promote the read to a lock (SELECT ... FOR UPDATE): take an explicit X lock on
--     the rows the invariant depends on, forcing serialization on them.
SELECT COUNT(*) FROM Duties WHERE Shift = :S AND Status = 'on duty' FOR UPDATE;
 
-- (3) Use true serializable isolation and let the engine detect the anomaly (PostgreSQL SSI).
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Technique (1) and (2) are what Fekete et al.’s earlier static-analysis work recommended — “modify the applications by introducing artificial locking or update conflicts, following careful analysis of conflicts between all pairs of transactions” — which the SSI paper criticizes as fragile: “it relies on an education campaign so that application developers are aware of SI anomalies, and it is unable to cope with ad-hoc transactions … every minor change in the application requires renewed analysis.” Technique (3) is the modern answer: let the database detect the dangerous structure automatically.

Failure Modes and Misunderstandings

  • “SI prevents all ANSI anomalies, therefore SI is serializable” — false. SI prevents dirty/non-repeatable/phantom reads and lost updates, but not write skew or the read-only anomaly. The ANSI phenomena are an incomplete list; serializability is strictly stronger.
  • Write skew hides in multi-row invariants. Any invariant spanning rows that a transaction reads but does not write is at risk: on-call rosters, double-booking, balance-across-accounts, unique-count constraints enforced in application code. Single-row invariants are safe (first-committer-wins covers them).
  • First-committer-wins only checks same-item writes. Developers assume “SI serializes conflicting transactions”; it only serializes write-write conflicts on the same item.
  • Aborts require retry logic. SI updaters can fail with 40001; the application must retry. Code that assumes commit always succeeds will silently drop work.
  • Oracle/PostgreSQL “Repeatable Read/Serializable” names lie. Always reason in terms of anomalies (see Read Phenomena and Concurrency Anomalies), not level names.

Alternatives and When to Choose Them

Below SI, Read Committed is cheaper (fresh snapshot per statement) but permits non-repeatable reads and lost updates — fine for simple CRUD, dangerous for read-modify-write. Above SI, SSI adds runtime detection of the dangerous rw-dependency structures that cause write skew, delivering true Serializability “at close to snapshot-isolation cost” — the reason PostgreSQL 9.1+ could make its Serializable level genuinely serializable without reverting to 2PL. Classic serializable 2PL also forbids write skew but blocks readers, sacrificing SI’s headline benefit. Choose SI when read throughput matters and your invariants are single-row or you can tolerate/handle skew; choose SSI when you have multi-row invariants and can’t afford to hand-audit every transaction; choose Read Committed only when you understand you’re giving up repeatable reads. For most applications the pragmatic path is: run SI (Repeatable Read), and promote the specific transactions that guard multi-row invariants to Serializable.

Production Notes

The most cited real-world write-skew incidents come from financial and booking systems where an application-enforced invariant (“account never goes negative,” “no double-booking of a seat,” “at least one admin remains”) was checked with a SELECT and then acted on with an UPDATE on a different row — exactly the skew pattern. Under SI these pass code review and unit tests (each transaction is individually correct) and fail only under concurrency, making them nasty production bugs. The durable guidance from the SSI authors and the Berenson critique is: never trust that “Repeatable Read” or even a vendor’s “Serializable” gives serializability — verify the engine and version. PostgreSQL Serializable (SSI, since 9.1) does; Oracle “Serializable” historically does not; InnoDB Repeatable Read prevents phantoms via gap locks but is still SI at heart. As of 2026-07 these mappings hold for current PostgreSQL and MySQL 8.4 LTS, but they are precisely the version-and-vendor-specific facts that must be re-pinned per deployment.

See Also

Graph View