Write Skew Explained: The Anomaly That Requires Serializable Isolation

Scenario context

Write skew is the hardest concurrency anomaly to reason about: two concurrent transactions each read a shared condition, decide they can safely proceed, and then write to different rows. No individual operation is wrong. No row was overwritten. Yet together, their writes violate the invariant both checked. Repeatable Read does not prevent write skew — only Serializable isolation (or explicit predicate locking) can…

This walkthrough focuses on Why Snapshot Isolation and Repeatable Read Are Not Enough.

Before making a design choice, clarify:

• Snapshot Isolation is widely praised — and for good reason.
• It eliminates dirty reads, non-repeatable reads, and phantom reads in a single stroke by giving every transaction a consistent point-in-time view of the database.
• It does this without locking read rows, which gives it excellent throughput characteristics.

Concepts in play: database-isolation-levels-anomalies, caching, consistency-replication, rate-limiting

Stage 1 — The On-Call Disaster: When Two Correct Decisions Leave Zero Doctors Available

• The hospital enforces a simple invariant: at all times, at least one doctor must have status = 'active' in the on call table.
• The rule is checked by application logic before any doctor is allowed to go off-call.
• Here is the timeline that breaks everything: - 14:00:00 — Alice opens the staff portal and clicks "Go Off Call." - 14:00:00 — Bob, in a separate browser tab, clicks "Go Off Call" simultaneously.
• • 14:00:01 — Alice's transaction reads: SELECT COUNT( ) FROM on call WHERE status = 'active' → returns 2 (Alice and Bob are both active).

Observable outcome: - 14:00:01 — Alice's transaction reads: SELECT COUNT( ) FROM on call WHERE status = 'active' → returns 2 (Alice and Bob are both active).

Stage 2 — What Makes Write Skew Different from Lost Updates and Dirty Reads

• Before going deeper, it is worth pinning down exactly why write skew is its own category — distinct from every other anomaly you may already know.
• Write skew is uniquely dangerous because: 1.
• Both writes succeed and survive.
• The data is not corrupted in any conventional sense — both the alice row and the bob row correctly reflect their owners' actions.

Observable outcome: The data is not corrupted in any conventional sense — both the alice row and the bob row correctly reflect their owners' actions.

Stage 3 — Step-by-Step: How Two Transactions Jointly Break a Shared Invariant

• The sequence diagram below traces the exact interleaving that produces write skew.
• Both transactions are running under Snapshot Isolation (the default in PostgreSQL's REPEATABLE READ and many "snapshot" modes) — each sees a perfectly consistent point-in-time view of the database, which is precisely wh…
• Read the diagram from top to bottom.
• Both transactions start by reading the full active count from the same snapshot — both see 2.

sequenceDiagram
    participant T1 as T1 - Alice
    participant DB as on_call table
    participant T2 as T2 - Bob

    T1->>DB: BEGIN; SELECT COUNT(*) WHERE status=active
    DB-->>T1: 2 (alice=active, bob=active)
    T2->>DB: BEGIN; SELECT COUNT(*) WHERE status=active
    DB-->>T2: 2 (alice=active, bob=active)
    Note over T1: count=2 > 1, safe to go off call
    Note over T2: count=2 > 1, safe to go off call
    T1->>DB: UPDATE on_call SET status=off_call WHERE doctor=alice
    T2->>DB: UPDATE on_call SET status=off_call WHERE doctor=bob
    T1->>DB: COMMIT
    T2->>DB: COMMIT
    Note over DB: 0 active doctors. Invariant violated.

Observable outcome: Both transactions start by reading the full active count from the same snapshot — both see 2.

Stage 4 — Deep Dive: How Serializable Snapshot Isolation Detects Write Skew

• Serializable Snapshot Isolation (SSI), implemented in PostgreSQL since version 9.1, detects write skew by tracking read-write anti-dependencies between concurrent transactions.
• Understanding the internals of this mechanism clarifies why snapshot isolation misses the anomaly and why SSI catches it.
• The Internals: rw-Anti-Dependency Tracking Inside SSI An rw-anti-dependency is formed when one transaction reads a version of a row that another concurrent transaction subsequently writes.
• The reading transaction depended on the "old" version — a version that no longer exists once the writing transaction commits.

graph TD
    T1[T1 reads all on_call rows including bob row]
    T2[T2 reads all on_call rows including alice row]
    W1[T1 writes alice row to off_call]
    W2[T2 writes bob row to off_call]

    T1 -->|rw-anti-dep: T1 read bob row that T2 will overwrite| W2
    T2 -->|rw-anti-dep: T2 read alice row that T1 will overwrite| W1
    W1 --> CYCLE[Dangerous cycle - SSI aborts one transaction]
    W2 --> CYCLE

Observable outcome: The reading transaction depended on the "old" version — a version that no longer exists once the writing transaction commits.

Stage 5 — From HTTP Request to Empty On-Call Roster: The Full System Architecture View

• Write skew does not only exist in toy examples.
• In a production on-call management system, the anomaly can propagate through multiple layers before causing visible harm.
• The architecture diagram below shows where write skew injects itself into the request flow.
• The write skew injection point is the SVC → DB edge.

graph TD
    HTTP1[Alice - HTTP go-off-call request]
    HTTP2[Bob - HTTP go-off-call request]
    SVC[On-Call Availability Service]
    DB[(on_call table - PostgreSQL)]
    SCHED[Emergency Scheduler - polls on-call roster]
    DISP[Emergency Dispatch System]
    ERR[Patient routed with zero on-call doctors]

    HTTP1 --> SVC
    HTTP2 --> SVC
    SVC -->|check + update under Snapshot Isolation| DB
    DB --> SCHED
    SCHED --> DISP
    DISP -->|empty roster query result| ERR

Observable outcome: The write skew injection point is the SVC → DB edge.

Stage 6 — Isolation Level Prevention Table: Choosing the Right Defense

• The table below maps each standard isolation level to the anomalies it prevents.
• Read this as a capability matrix — a ✅ means the anomaly is prevented at this level; a ❌ means it can still occur.
• \ PostgreSQL's REPEATABLE READ (implemented as Snapshot Isolation) prevents phantom reads.
• Standard SQL's REPEATABLE READ (as in MySQL InnoDB) does not.

Observable outcome: Standard SQL's REPEATABLE READ (as in MySQL InnoDB) does not.

End-to-end outcome

After working through the stages, you should be able to explain:

• Repeatable Read is not a safety guarantee for multi-row invariants.
• REPEATABLE READ guarantees that individual rows you have read will not change if you read them again within your transaction.
• Write skew is the anomaly that exposes the gap between "individually correct" and "collectively consistent." The five things to carry away: - Write skew requires two conditions: a shared predicate read by multiple concu…
• If either condition is absent, you have a different anomaly.

Alternatives considered

The source focuses on one primary design. A second architecture is not compared, so no alternative is invented here.

Failure and degraded modes

The source does not isolate a separate failure mode. Limits that matter for the scenario are called out in the stage decisions above.