CSE Roadmap

Specialist tier · about 2 hours · 5 parts

Part 4 of 5 · 2 min read

The shared root cause

Each transaction only ever sees its own frozen snapshot, never the other transaction's concurrent, not-yet-committed changes.

The shared root cause

Think of two separate inspectors, each examining their own photograph of the same room, taken moments apart. Neither photograph shows what the other inspector is about to change in the actual, real room.

In plain words: both write skew and the phantom-under-snapshot-isolation case share the same underlying cause: each transaction's decision is based entirely on its own fixed snapshot, taken at its own start time, which by definition cannot reflect a concurrent transaction's changes, whether those changes are an update to an existing row or an entirely new row being inserted. Neither anomaly involves any individual constraint check actually failing; each transaction's own check genuinely passes against its own snapshot. The problem only appears once both transactions' separately-valid decisions are combined.

A worked example. In both the doctors scenario and the reservations scenario, each transaction's own query against its own snapshot returns a perfectly accurate, correct answer for that snapshot; the anomaly only emerges once both transactions' independently correct decisions are combined after both commit.

The key lines. No new SQL; this is the conceptual link between the two anomalies covered on this page, both traceable to the same snapshot-isolation limitation.

Snapshot blindness

Snapshot blindness describes the shared cause behind write skew and snapshot-isolation phantoms: each transaction's fixed snapshot cannot reflect a concurrent transaction's changes, so two individually valid decisions can still combine into an invalid result.

Example: two inspectors each working from their own photograph of a room, neither showing what the other is about to change, mirrors snapshot blindness.

Remember this

Neither write skew nor a snapshot-isolation phantom involves a failed individual check; both involve two individually correct decisions combining into a collectively wrong result. The next page covers how a database achieves true serializability despite this limitation.

Quick notes

  • Write skew happens when two transactions each check a cross-row condition against their own snapshot and both write in a way that together violates it.
  • Write skew passes every individual constraint check; the violation only appears once both writes are combined.
  • A phantom can still occur under snapshot isolation when a transaction's filter cannot reflect a concurrent, not-yet-committed insert.
  • Both anomalies share one root cause: a transaction's fixed snapshot cannot reflect a concurrent transaction's changes.
  • Neither anomaly is a sign of a bug in snapshot isolation; both are inherent limits of what a fixed snapshot alone can guarantee.