Notestransactions🌱 seedling

Write Skew

Also: Write Skew Anomaly · G2-item · A5B · Check-Then-Act Race

Two transactions read the same data, make a decision from it, and then write different rows, each invalidating the premise of the other. No row is written twice, so snapshot isolation sees no conflict, yet the result matches no serial order.

Why it exists

Many invariants span several rows: "checking + savings ≥ 0", "at least one engineer stays on call", "no two bookings overlap". Each transaction preserves the invariant alone; interleaved, they break it.

How it works

  1. A SELECT checks a condition across some rows.
  2. The application decides based on the result.
  3. It writes a row, which changes the answer the other transaction got in step 1.
  • Each transaction has a read-write dependency on the other (it changed what the other read), forming a cycle. That cycle is exactly what Serializability forbids.
  • A Lost Update is the special case where both write the same row.

Trade-offs

Fix Cost
Serializable isolation aborts and retries, or blocking
SELECT … FOR UPDATE on every row the check read blocking; only works if those rows exist
a constraint (unique, check, exclusion) only for rules a constraint can express
materialize the conflict as a lockable row concurrency control leaks into the schema

In practice

  • Snapshot isolation everywhere allows it: Postgres Repeatable Read, Oracle Serializable, SQL Server SNAPSHOT.
  • Postgres Serializable detects it with Serializable Snapshot Isolation and aborts one transaction with SQLSTATE 40001.

Pitfalls

  • Locking only the row you write. The danger is in the rows you read.
  • When the check is about rows that don't exist yet, there is nothing to lock: that's a Phantom Read.

Recall

These cards are also in your review deck, scheduled with spaced repetition.