Write Skew (Snapshot Isolation Anomaly)

Snapshot Isolation guarantees that a transaction reads from a consistent snapshot taken at its start time and that its writes do not conflict at the row-version level with concurrent writes (write-write conflicts abort via first-committer-wins). Critically, SI does not detect read-write conflicts between transactions that never touch the same rows for writing. This is the exact gap Write Skew exploits: two transactions T1 and T2 each read a shared set of rows R, derive a decision based on the aggregate state of R, and then each write to a different row that individually satisfies a constraint but collectively violates it.

The canonical example is the on-call doctor problem: an invariant requires at least one doctor on-call at all times. T1 reads that Doctor A and Doctor B are both on-call and decides it is safe for A to go off-call. Concurrently, T2 reads the same snapshot and decides it is safe for B to go off-call. Neither transaction’s write conflicts with the other at the storage engine’s MVCC version-chain level — they touch disjoint rows — so both commit under standard SI, leaving zero doctors on-call. The underlying invariant was never encoded as a row-level constraint the database could enforce.

• Detection mechanism: Preventing Write Skew requires either explicit locking (SELECT ... FOR UPDATE on the read set to force a write-write conflict), materializing a conflict via a dummy row update, or upgrading to Serializable Snapshot Isolation (SSI), which tracks rw-antidependencies (a read that could be invalidated by a later write) between concurrent transactions and aborts one side when a dangerous structure of two rw-antidependencies forms a cycle.
• Cost tradeoff: SSI implementations (e.g., PostgreSQL’s SERIALIZABLE level) impose overhead via SIREAD lock tracking and increased abort rates under contention, so many systems deliberately run at SI for throughput and accept the anomaly risk, pushing invariant enforcement into application-level predicate locks or constraint triggers instead.
• Distributed amplification: In sharded or geo-replicated OLTP systems where snapshots are coordinated via a global timestamp oracle (e.g., TrueTime-style or HLC-derived snapshots), Write Skew becomes harder to reason about because the ‘concurrent’ window widens with replication lag, increasing the probability of overlapping snapshot reads across shards that each locally believe their write is safe.

Architecturally, the presence of Write Skew risk is a signal that an invariant spans multiple rows or aggregates without a single serialization point. Common mitigations at scale include collapsing the invariant into a single row (a counter or a materialized constraint row that every transaction must write to, forcing a genuine write-write conflict), using explicit advisory locks scoped to the invariant’s logical key, or accepting eventual repair via asynchronous invariant-checking jobs when strict prevention is too costly for the workload’s throughput requirements.