Write Skew and Phantom Reads

The race that snapshot isolation lets through: two transactions check the same rule, write different rows, and break the rule together. How phantoms cause it and the four ways to stop it.

Overview

The check-then-write pattern

Many business rules are checked the same way: read some rows, decide whether the action is allowed, then write. Under snapshot isolation two transactions can run this pattern at once, both see the rule satisfied, both write, and the rule breaks. This is write skew.

It slips through because the two transactions write different rows. Snapshot isolation only notices two writes to the same row, so nothing conflicts.

Linked checking and savings accounts — Together they stay at or above zero — Each withdraws from a different account after checking the total
Hotel room booking — No two stays overlap for one room — Each checks the dates are free, then inserts its own booking
Choosing a username — Names are unique — Each checks the name is free, then inserts it

Lost update versus write skew

Write skew is a wider version of the lost update. In a lost update both transactions write the same row, which snapshot isolation can detect. In write skew they write different rows, which it cannot.

Rows written — The same row — Different rows
Caught by PostgreSQL repeatable read? — Yes: the second writer fails with SQLSTATE 40001 — No
Fixed by an atomic UPDATE? — Yes — No: the rule spans several rows
Needs — Atomic update, lock or compare-and-set — Serializable, a lock on what was read, or a constraint

Phantoms: when the rows to lock do not exist yet

A phantom is a row that appears in, or disappears from, the result of a search because another transaction inserted or deleted it. It matters here because in many write skews the check looks for the absence of rows: 'no booking overlaps these dates'. There is nothing to lock, because the row that would conflict has not been written yet.

SELECT ... FOR UPDATE does not help here: it locks rows the search found, and the search found none. The ways out: lock the searched range of an index (what lock-based serializable levels, such as InnoDB's, do), track the search and abort on conflict (PostgreSQL's serializable level), turn the missing rows into real ones, or let a constraint reject the overlap.

Fix 1: lock the rows the decision reads

If the rule depends on rows that already exist, lock them before deciding. The second transaction waits on the lock, then reads the updated data.

The inner query takes the locks (FOR UPDATE cannot be combined with sum directly), and ORDER BY id makes every transaction lock in the same order, which avoids deadlocks. A second withdrawal would wait, then see a total of 50 and refuse.

Fix 2: materialize the conflict

When the conflicting row does not exist yet, create rows that stand for the thing being claimed, and let a lock or a unique constraint work on them. For a hotel, one row per room per night does it.

2026-05-05

May 4 is refused because booking 1 holds it. In real code, drop ON CONFLICT DO NOTHING so the whole booking fails with a unique violation and rolls back. This is safe at any isolation level, because the primary key is checked by the database, not by a search.

The cost is a less natural schema: rows that exist only to be locked or claimed. Use it when the set of things to claim is small and known, like nights, seats or slots.

Fix 3: a constraint that knows the rule

Some rules can be written as constraints, and then the database enforces them whatever the isolation level.

Usernames are unique — UNIQUE (username)
One claim per room per night — PRIMARY KEY (room_id, night) as above
Ranges must not overlap — PostgreSQL exclusion constraint: EXCLUDE USING GIST (during WITH &&)

In PostgreSQL an exclusion constraint on a range column rejects any two rows whose ranges overlap. To combine it with equality on a plain column (overlap only for the same room), the docs use the btree_gist extension: EXCLUDE USING GIST (room WITH =, during WITH &&).

Fix 4: serializable isolation

Serializable isolation catches write skew without any change to the queries. PostgreSQL's serializable level records what each transaction read. When a concurrent write would have changed a read, and the pattern could produce a result no serial order allows, it aborts one transaction with SQLSTATE 40001.

On its own this transaction commits (the total is 50, enough for 10). If two such transactions ran at once, one of them would fail and must be retried from the start, including the read that led to the decision.

Lock the rows read — The rows already exist — Waiting; deadlocks if lock order varies
Materialize the conflict — The claimable things are few and known — Extra rows and schema
Constraint — The rule fits a unique or exclusion constraint — Only some rules fit
Serializable — Any rule — Aborts and a retry loop in every client

What you will be able to do

Recognize the check-then-write pattern that leads to write skew.
Tell a lost update from write skew and pick the right fix for each.
Explain why phantoms make SELECT ... FOR UPDATE useless when the conflicting row does not exist.
Stop double booking with a lock, a materialized conflict, a constraint or serializable isolation.

Key concepts

Write skew: Two concurrent transactions read the same data, each writes a different row based on what it read, and together they break a rule that each one checked.
Lost update: The same-row case: two read-modify-write cycles on one row, where the later write erases the earlier one.
Phantom: A row that appears in or disappears from a search result because another transaction inserted or deleted it.
SELECT ... FOR UPDATE: Locks the rows a query returns until the transaction ends, so other writers and lockers of those rows wait.
Materializing conflicts: Adding rows that stand for the thing being claimed (a seat, a night) so that a lock or unique key can guard something that would otherwise be absent.
Exclusion constraint: A PostgreSQL constraint that rejects any two rows for which an operator is true, such as overlapping ranges.
Index-range lock: A lock on a range of an index instead of individual rows, so inserts into that range must wait. It approximates locking a search condition.

When to use it

Designing booking, reservation or scheduling systems where two requests can claim the same thing.
Reviewing any code that reads, checks a rule and then writes, inside one transaction.
Explaining why a transaction at repeatable read still produced invalid data.
Choosing between locks, constraints and serializable isolation for a multi-row rule.

Interview questions

How do you prevent double booking in a reservation system under concurrent load?
What is the difference between a lost update and write skew?
Why does SELECT ... FOR UPDATE fix some write skews but not others?
How does serializable isolation catch write skew in PostgreSQL?