Write Skew and Phantoms

Scenario

Example app: a hospital rota system requires at least one doctor on call per shift. Separately, a meeting-room booker must never allow overlapping bookings for the same room.

What you see in production

Twice in one month a shift ended up with zero doctors on call — two doctors who were both feeling unwell went off call at the same moment. And the 10:00 board meeting was booked twice in Room 4 by two assistants clicking "Book" within the same second. Row-level locking was already in place for other features.

The naive code

Each transaction checks a condition across several rows (or rows that don't exist yet), then writes a different row:

def go_off_call(doctor_id, shift_id):
    with Session(engine) as s, s.begin():
        on_call = s.scalar(select(func.count()).where(
            Doctor.shift_id == shift_id, Doctor.on_call.is_(True)))
        if on_call < 2:
            raise CannotLeave("you are the last doctor on call")
        s.execute(update(Doctor).where(Doctor.id == doctor_id).values(on_call=False))

def book(room_id, start, end):
    with Session(engine) as s, s.begin():
        clash = s.scalar(select(func.count()).where(
            Booking.room_id == room_id, Booking.start < end, Booking.end > start))
        if clash:
            raise RoomTaken()
        s.add(Booking(room_id=room_id, start=start, end=end))     # inserts a NEW row

@Transactional
public void book(long roomId, Instant start, Instant end) {
    if (bookings.countOverlapping(roomId, start, end) > 0) throw new RoomTakenException();
    bookings.save(new Booking(roomId, start, end));   // the conflicting row doesn't exist yet: nothing to lock
}

Reproduce it

Under snapshot isolation (Postgres Repeatable Read, or MySQL with plain reads) both transactions read the same snapshot, both pass the check, and both write different rows — so no write-write conflict is detected:

committed = {"alice": True, "bob": True}          # both on call
snapshot = dict(committed)                         # both transactions start here

def go_off_call(name):
    on_call = sum(snapshot.values())               # reads the same snapshot
    yield f"{name}: sees {on_call} doctors on call -> allowed to leave"
    if on_call >= 2:
        committed[name] = False                    # writes ONLY its own row
        yield f"{name}: committed (no conflict: different rows)"

a, b = go_off_call("alice"), go_off_call("bob")
for step in (a, b, a, b):
    print(next(step))
print("doctors on call now:", sum(committed.values()))

alice: sees 2 doctors on call -> allowed to leave
bob: sees 2 doctors on call -> allowed to leave
alice: committed (no conflict: different rows)
bob: committed (no conflict: different rows)
doctors on call now: 0

Root cause

Write skew: two transactions read an overlapping set of rows, make a decision based on it, then write different rows. Row locks and snapshot isolation only detect conflicts on the same row, so nothing stops them. The booking case is a phantom: the conflicting row is one that would match the query but doesn't exist yet, so there is no row to lock.

The fix

Make the conflict concrete. Pick the option that matches the rule:

-- Doctors: lock all rows the rule depends on, so the second transaction waits and re-checks.
BEGIN;
SELECT id FROM doctors WHERE shift_id = 7 AND on_call FOR UPDATE;
-- count the returned rows in the app, then:
UPDATE doctors SET on_call = false WHERE id = 42;
COMMIT;

-- Bookings: serialize per room by locking the room row first (it always exists).
BEGIN;
SELECT id FROM rooms WHERE id = 4 FOR UPDATE;
SELECT count(*) FROM bookings WHERE room_id = 4 AND start_at < $end AND end_at > $start;
INSERT INTO bookings (room_id, start_at, end_at) VALUES (4, $start, $end);
COMMIT;

CREATE EXTENSION IF NOT EXISTS btree_gist;
ALTER TABLE bookings ADD CONSTRAINT no_overlap
  EXCLUDE USING gist (room_id WITH =, tstzrange(start_at, end_at) WITH &&);
-- The second overlapping INSERT fails with SQLSTATE 23P01 (exclusion_violation).

BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM doctors WHERE shift_id = 7 AND on_call;
UPDATE doctors SET on_call = false WHERE id = 42;
COMMIT;   -- one of two conflicting transactions fails with SQLSTATE 40001: retry it

with Session(engine) as s, s.begin():
    s.execute(select(Room.id).where(Room.id == room_id).with_for_update())   # parent-row lock
    if s.scalar(select(func.count()).where(Booking.room_id == room_id,
                                           Booking.start < end, Booking.end > start)):
        raise RoomTaken()
    s.add(Booking(room_id=room_id, start=start, end=end))

The same simulation, but each transaction locks the rows it reads before deciding:

committed = {"alice": True, "bob": True}
lock_holder = None

def go_off_call(name):
    global lock_holder
    while lock_holder not in (None, name):
        yield f"{name}: waiting for row locks held by {lock_holder}"
    lock_holder = name                              # SELECT … FOR UPDATE on all on-call rows
    on_call = sum(committed.values())               # reads CURRENT data after waiting
    if on_call >= 2:
        committed[name] = False
        message = f"{name}: saw {on_call}, went off call, COMMIT (locks released)"
    else:
        message = f"{name}: saw {on_call}, refused (last doctor on call)"
    lock_holder = None                              # COMMIT / ROLLBACK releases the locks
    yield message

a, b = go_off_call("alice"), go_off_call("bob")
lock_holder = "alice"                               # alice locked first; bob arrives while she decides
print(next(b))
lock_holder = None
print(next(a))
print(next(b))
print("doctors on call now:", sum(committed.values()))

bob: waiting for row locks held by alice
alice: saw 2, went off call, COMMIT (locks released)
bob: saw 1, refused (last doctor on call)
doctors on call now: 1

Trade-offs

• Locking what you read works when the set of rows is small and known; it serializes every change for that shift.
• A parent-row lock is simple and robust for "per room / per account / per tenant" rules, but makes that parent a hot spot.
• Constraints (unique, exclusion) are the strongest guarantee — they hold for every code path, including manual SQL — but only express certain rules.
• SERIALIZABLE catches every anomaly without thinking about locks, at the cost of retries; every write path must be retry-safe.

Detect it in production

• Invariant queries on a schedule: shifts with zero on-call doctors, overlapping bookings via a self-join.
• Postgres logs SQLSTATE 23P01 (exclusion violation) and 40001 (serialization failure) — count them per endpoint.
• Concurrency tests: run the two conflicting actions in parallel 1,000 times and assert the invariant.

Key points

• Write skew: decisions based on several rows, writes to different rows; snapshot isolation doesn't stop it.
• Phantoms: the conflicting row doesn't exist yet, so there's nothing to lock.
• Fix by locking what you read, locking a parent row, using a constraint, or running SERIALIZABLE with retries.