Write skew and snapshot isolation

What it is

Write skew is the anomaly where two transactions each read an overlapping set of rows, each make a decision based on what they read, and each write to disjoint rows. Neither writes what the other read, so no write conflict is detected, both commit, and together they violate an invariant that neither violated alone.

It is the anomaly that snapshot isolation does not prevent, and it matters because PostgreSQL's REPEATABLE READ is snapshot isolation. A developer who reads the SQL standard, sees that REPEATABLE READ prevents non-repeatable reads and phantoms, and concludes their invariant is safe, is wrong in a way the database will never tell them about.

It is commonly confused with lost update, where two transactions read the same row, modify it, and one overwrites the other. Snapshot isolation does catch lost updates through first-updater-wins conflict detection. Write skew slips through precisely because the writes touch different rows.

The problem it solves

Databases give you isolation levels because full serialisability is expensive. Snapshot isolation was a good bargain: readers never block writers, writers never block readers, and you get a consistent point-in-time view for free from MVCC. Most anomalies disappear.

The remaining hole is small and sharp. The invariant that breaks is always of the form "at least one of these rows must satisfy P" or "the sum of these rows must stay under N", a constraint over a set rather than a row. Those constraints are common in exactly the places where correctness matters most: on-call schedules, seat inventory, account balances, meeting-room bookings, financial limits.

Mechanics

The canonical example

The invariant: at least one doctor must remain on call. Two doctors, both on call, both try to go off call at the same instant.

-- Setup
CREATE TABLE doctors (id int PRIMARY KEY, name text, on_call boolean);
INSERT INTO doctors VALUES (1, 'Alice', true), (2, 'Bob', true);

-- Transaction A                          -- Transaction B
BEGIN ISOLATION LEVEL REPEATABLE READ;    BEGIN ISOLATION LEVEL REPEATABLE READ;

SELECT count(*) FROM doctors              SELECT count(*) FROM doctors
  WHERE on_call = true;   -- 2              WHERE on_call = true;   -- 2
-- "2 >= 2, safe to go off call"          -- "2 >= 2, safe to go off call"

UPDATE doctors SET on_call = false        UPDATE doctors SET on_call = false
  WHERE id = 1;                             WHERE id = 2;

COMMIT;  -- succeeds                      COMMIT;  -- succeeds

-- Zero doctors on call. Both transactions were individually correct.
-- No error. No warning. The invariant is silently gone.

Snapshot isolation detects write-write conflicts: two transactions updating the same row. Here A updates row 1 and B updates row 2. No overlap, no conflict, both commit. Each read the other's row but neither wrote it, and reads are not tracked.

Serialisability would forbid this, because there is no serial order producing the outcome: run A then B and B sees one doctor on call and refuses; run B then A and A refuses.

The general shape

Recognising it in a design review matters more than the doctors example. The pattern is:

1. Read a set of rows and compute an aggregate or a predicate over it.
2. Decide based on that aggregate.
3. Write rows that are not the ones whose values determined the decision.

Instances you will meet: booking the last seat when two requests both count availability; a bank enforcing "combined balance across accounts must stay positive" with withdrawals from different accounts; claiming a username by checking a uniqueness query then inserting; two schedulers each checking "is any worker idle" and both assigning to the same one; enforcing a per-team quota where each member's row is separate.

The tell is an invariant expressed over a set, enforced in application code, with writes to individual members.

The three fixes, in order of preference

1. Move the invariant into a constraint the database can enforce.

-- Nothing to skew: the invariant is now a row the database serialises on.
CREATE TABLE on_call_count (
    id           int PRIMARY KEY DEFAULT 1,
    count        int NOT NULL CHECK (count >= 1)   -- <- the invariant
);

BEGIN;
UPDATE on_call_count SET count = count - 1;  -- write-write conflict if concurrent
UPDATE doctors SET on_call = false WHERE id = 1;
COMMIT;  -- CHECK fires if this would take the count to zero

Now both transactions write the same row, snapshot isolation's own conflict detection applies, and the constraint is enforced by the engine rather than by hope. This is the strongest fix, and it is the one candidates rarely mention.

Exclusion constraints do the same for range invariants, which is the clean answer to the double-booking problem:

-- Two overlapping bookings for the same room cannot both exist.
CREATE EXTENSION btree_gist;
ALTER TABLE bookings ADD CONSTRAINT no_overlap
  EXCLUDE USING gist (room_id WITH =, during WITH &&);

2. Materialise the conflict with SELECT ... FOR UPDATE.

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctors WHERE on_call = true FOR UPDATE;  -- locks the rows read
UPDATE doctors SET on_call = false WHERE id = 1;
COMMIT;

Taking a lock on the rows you read turns the read into something the conflict detector can see. It works, and it costs you the readers-do-not-block-writers property that made snapshot isolation attractive. Note it only covers rows that exist; for "no row satisfies P" invariants there is nothing to lock, and you need a lock on a parent row or an advisory lock instead.

3. Raise to SERIALIZABLE.

BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM doctors WHERE on_call = true;
UPDATE doctors SET on_call = false WHERE id = 1;
COMMIT;  -- may raise 40001 serialization_failure

PostgreSQL's Serializable Snapshot Isolation (SSI) tracks read dependencies and aborts a transaction when it detects a "dangerous structure": a cycle of read-write antidependencies that could not arise in any serial order. It is optimistic, so it does not block, but it means:

Every serialisable system requires application-level retry logic. If a candidate proposes SERIALIZABLE without mentioning retries, they have not run it.

# The retry is not optional. Without it, SERIALIZABLE converts a silent
# correctness bug into a visible availability bug, which is an improvement
# but not a fix.
def with_retry(fn, attempts=5):
    for i in range(attempts):
        try:
            with conn.transaction(isolation="serializable"):
                return fn()
        except SerializationFailure:          # SQLSTATE 40001
            if i == attempts - 1:
                raise
            time.sleep((2 ** i) * 0.01 * random.random())   # backoff + jitter

Two operational notes. SSI's tracking uses predicate locks in a fixed-size shared memory area; under pressure it escalates from tuple to page to relation granularity, which increases false positives (aborts of transactions that were actually fine). And SSI only protects transactions that are themselves serialisable: one READ COMMITTED transaction in the mix can violate the invariant without triggering anything, so the isolation level has to be applied consistently to every writer of that invariant.

Where the isolation levels stand

The two rows worth knowing precisely: PostgreSQL's REPEATABLE READ prevents phantoms, which the SQL standard does not require, because snapshot isolation gives a consistent snapshot for free. And write skew is the one anomaly only SERIALIZABLE prevents. That table is the answer to the isolation-levels question and it takes twenty seconds to draw.

Note also that engines differ. MySQL's InnoDB REPEATABLE READ uses next-key locking, which prevents phantoms by a different mechanism and has different behaviour again. "It depends on the engine" is correct here and should be followed immediately by which engine you mean.

A worked example: the seat-booking bug

A ticketing service. seats has one row per seat with a booked boolean. Capacity per event is 100. The rule: overbooking is forbidden.

# The bug. Ships, passes review, works in staging, fails on sale day.
def book(event_id, user_id):
    with conn.transaction(isolation="repeatable read"):
        sold = query("SELECT count(*) FROM seats WHERE event_id=%s AND booked", event_id)
        if sold >= 100:
            raise SoldOut()
        seat = query("SELECT id FROM seats WHERE event_id=%s AND NOT booked LIMIT 1", event_id)
        execute("UPDATE seats SET booked=true, user_id=%s WHERE id=%s", user_id, seat)

At low concurrency this is correct. On sale day, 40 requests arrive within the same few milliseconds when 99 seats are sold. All 40 read sold = 99, all 40 pass the check, and they update 40 different seat rows. No write-write conflict. 139 seats sold on a 100-seat event.

The measured consequence is not a rounding error: it is 39 customers with a confirmation email and no seat, which is a refunds-and-apologies incident rather than a bug report.

Three fixes, and I would ship the first:

1. Constraint. An event_capacity row with sold int CHECK (sold <= 100), incremented in the same transaction. Every booking now writes the same row, so snapshot isolation's conflict detection serialises them and the CHECK enforces the ceiling. Throughput on that row becomes the limit, which for 100 seats is irrelevant, and for a million-item inventory would push you toward sharded counters.
2. SERIALIZABLE plus retry. Correct, and under 40-way contention on sale day the abort rate will be high, so the retry loop becomes the hot path and you have converted a correctness problem into a latency problem.
3. Claim the seat first, then validate. UPDATE seats SET booked=true WHERE id = (SELECT id FROM seats WHERE event_id=? AND NOT booked LIMIT 1 FOR UPDATE SKIP LOCKED) RETURNING id. Now the write is the check: if no row comes back, the event is sold out. SKIP LOCKED lets concurrent bookers take different seats without waiting. This is often the best answer for inventory specifically, because it removes the read-then-write pattern entirely.

Production evidence

PostgreSQL's SSI implementation is described in Ports and Grittner, "Serializable Snapshot Isolation in PostgreSQL" (VLDB 2012), which documents the dangerous-structure detection and the predicate-lock escalation behaviour. The PostgreSQL manual's transaction isolation chapter states plainly that REPEATABLE READ does not prevent write skew and gives essentially the doctors example.

Berenson et al., "A Critique of ANSI SQL Isolation Levels" (SIGMOD 1995) is where snapshot isolation and write skew were named and where the inadequacy of the ANSI anomaly-based definitions was established. It is the origin citation and naming it is a strong signal.

CockroachDB defaults to serialisable isolation across the whole cluster precisely to avoid this class of bug, and documents the required client-side retry loop as a first-class part of using it. Their engineering blog has written about why they chose to make retries the application's problem rather than offering a weaker default.

Kleppmann's Designing Data-Intensive Applications, chapter 7 is the best prose treatment and the one most interviewers will have read, which makes its vocabulary (write skew, phantoms, materialising conflicts) the shared language for this conversation.

The debate

The alternative to serialisable isolation is designing the invariant out. Instead of enforcing "at least one doctor on call" across rows, keep a counter row the database can constrain. Instead of "seats sold must not exceed capacity", claim the seat with the write itself. Instead of read-check-write, use an atomic conditional update.

The case for this: it is faster, it does not need retries, and the invariant is enforced by the engine rather than by every code path that touches the table remembering to use the right isolation level. That last point is the strongest one, because isolation level is a property of the transaction and any new code path can quietly get it wrong.

The case for SERIALIZABLE: it is correct by construction for invariants you have not thought of yet, which matters in a large codebase with many writers. SSI is optimistic and does not block, so on low-contention workloads the cost is close to zero.

My position: put the invariant in a database constraint wherever it can be expressed as one, because that is the only fix that cannot be bypassed by a future code path. Use SERIALIZABLE with retries as the default isolation level for transactional workloads where contention is low and the invariants are many. Use SELECT ... FOR UPDATE when you need a targeted fix in an existing system without changing everything.

SERIALIZABLE is the wrong choice under heavy contention on a hot key, where the abort rate makes the retry loop the dominant cost; in a distributed database where serialisable transactions require cross-node coordination on every commit; and when the real problem is a missing constraint, because a serialisable transaction that computes the invariant in application code is still one refactor away from being wrong.

Follow-up Q&A

"What happens under PostgreSQL REPEATABLE READ with write skew, and how do you prevent it?" REPEATABLE READ in PostgreSQL is snapshot isolation. Two transactions read overlapping rows, decide, and write disjoint rows; there is no write-write conflict so both commit and the invariant breaks silently. Prevention, best first: express the invariant as a database constraint (a counter row with a CHECK, or an exclusion constraint for ranges); materialise the conflict with SELECT ... FOR UPDATE on the rows you read; or use SERIALIZABLE, which detects the read-write antidependency cycle and aborts one transaction with SQLSTATE 40001. That last one requires application retry logic, always.

"Is serialisability stronger than linearisability?" Neither. They are orthogonal. Linearisability is about recency on a single object: an operation appears to take effect atomically at some point between its invocation and response, consistent with real time. Serialisability is about isolation across objects: the outcome of concurrent transactions equals some serial order, with no real-time requirement at all, so a serialisable system may legitimately serve you a stale snapshot forever. Strict serialisability is both, and it is what Spanner and CockroachDB provide.

"Why does SERIALIZABLE need retries when it does not block?" Because PostgreSQL's SSI is optimistic. It lets transactions run, tracks their read and write dependencies, and when it detects a cycle that could not occur in any serial order it aborts one of them with a serialisation failure. There is no way to know in advance which transaction will lose, so the application must be prepared to re-run it. The retry should use exponential backoff with jitter, because a thundering herd of retries against a contended row makes the abort rate worse.

"How would you detect write skew in an existing system?" You cannot find it by looking for errors, because there are none. Look for the code shape instead: a read that aggregates or checks a predicate over multiple rows, a branch on that result, and a write to a different row, inside one transaction that is not serialisable. Grep for transaction blocks containing both a count(*) or EXISTS and an UPDATE. Then look for the operational fingerprint: an invariant that occasionally does not hold in production data with no corresponding error log. A periodic invariant-checking job over the data is the pragmatic detector, and it should exist regardless.

"Does this apply outside relational databases?" Yes, and often worse. Any system with read-modify-write over multiple items has it: DynamoDB without TransactWriteItems and a condition expression, MongoDB without a multi-document transaction, a Redis check-then-set without a Lua script or WATCH. The general fix is the same: make the check and the write a single atomic operation, or make them touch the same key so the store's own conflict detection applies.

Common misconceptions

The most common is that REPEATABLE READ means what its name suggests and is therefore safe for invariants. It guarantees a stable snapshot for reads; it guarantees nothing about invariants across rows.

The second is that write skew is a lost update. Lost update is two writes to the same row and snapshot isolation prevents it. Write skew is two writes to different rows and snapshot isolation does not.

The third is that isolation levels are interchangeable across engines. PostgreSQL's REPEATABLE READ prevents phantoms; the SQL standard does not require it to; MySQL's InnoDB achieves a similar effect by a different mechanism with different locking behaviour. When you name an isolation level, name the engine.

Interview delivery note

Say this: "PostgreSQL's REPEATABLE READ is snapshot isolation, which permits write skew: two transactions read overlapping rows, decide, and write disjoint rows, so there's no write-write conflict, both commit, and the invariant breaks with no error. The classic case is two doctors both going off call after each checks that two are on call. The fixes in order: put the invariant in a database constraint so there's nothing to skew, materialise the conflict with SELECT ... FOR UPDATE, or use SERIALIZABLE, which uses SSI to detect the dependency cycle and aborts with 40001. That last one needs retry logic in the application, and if someone proposes SERIALIZABLE without mentioning retries they haven't run it."

The depth signal is putting the constraint fix first. Most candidates go straight to SERIALIZABLE, which is correct and expensive; proposing that the invariant belongs in the schema shows you think about where correctness should live rather than which flag to set.

Further reading

• PostgreSQL documentation, "Transaction Isolation", which states the write-skew limitation of REPEATABLE READ explicitly and gives the canonical example.
• Berenson et al., "A Critique of ANSI SQL Isolation Levels" (SIGMOD 1995), where snapshot isolation and write skew were named.
• Ports and Grittner, "Serializable Snapshot Isolation in PostgreSQL" (VLDB 2012), for the dangerous-structure detection and predicate-lock escalation.
• Kleppmann, Designing Data-Intensive Applications, chapter 7, for the clearest prose treatment and the shared vocabulary.