Write skew explained: how two doctors went off call at once

Tonight only two doctors are on call at a hospital, Alice and Bob. Both are feeling sick, and at the exact same second both tap “go off call” in the scheduling app. The app says yes to Alice and yes to Bob. Now nobody is on call, and nobody answers an emergency.

The app was not careless. It had one rule, at least one doctor must stay on call, and it checked that rule inside a transaction before every change:

BEGIN;
  SELECT count(*) FROM doctors
    WHERE on_call = true AND shift_id = 1234;
  -- app: if count >= 2, it's safe to go off call
  UPDATE doctors SET on_call = false
    WHERE name = 'Alice' AND shift_id = 1234;
COMMIT;

Two or more on call: safe. Only one: refuse. Both requests ran this code. Both passed. So how did the rule break?

Most databases don't run transactions one at a time; that would be far too slow. Instead, many (PostgreSQL, MySQL's InnoDB, Oracle, SQL Server with snapshot isolation enabled) give each transaction a snapshot: a frozen, consistent copy of the data as it was when the transaction started. Other transactions can commit changes in the meantime, but you keep reading your own snapshot.

Snapshot isolation is great for most things. Long reports see consistent numbers, and readers never block writers. It also stops two transactions from overwriting the same row; one of them is rejected or has to wait.

Alice's and Bob's requests start at the same moment, so both snapshots show two doctors on call. Alice's transaction counts two, decides it's safe, and takes Alice off. Bob's transaction counts two, decides it's safe, and takes Bob off.

The key detail: Alice's transaction changed Alice's row, and Bob's changed Bob's row. They never wrote to the same row, so the database sees no conflict, and both commit. Each one was correct on its own. Together they broke the rule.

This anomaly is called write skew. The pattern always looks the same:

• A transaction reads some rows and checks a condition (at least one doctor on call).
• Based on the answer, it decides to go ahead.
• It writes, and that write changes the answer to the check it just made.
• Another transaction does the same thing concurrently, on different rows.

The same bug shows up in lots of apps: two users claiming the same username, two people booking the same meeting room, two players moving to the same square in a game, or two withdrawals each checking the balance and together spending more than is there.

It gets sneakier when the check is about something not existing. “Is this meeting room free at noon?” means “are there zero bookings for this room at noon?”.

SELECT count(*) FROM bookings
  WHERE room_id = 7 AND start_time < '13:00' AND end_time > '12:00';
-- 0: go ahead
INSERT INTO bookings (room_id, start_time, end_time, user_id)
  VALUES (7, '12:00', '13:00', 42);

If the answer is zero, there is no row to lock. Two people run this at the same time, both see zero, both insert, and the room is double-booked. A write in one transaction that changes the result of a search in another is called a phantom.

When the check reads existing rows, as in the doctors example, you can tell the database you intend to change based on them. Add FOR UPDATE to the SELECT:

SELECT * FROM doctors
  WHERE on_call = true AND shift_id = 1234
  FOR UPDATE;

This locks every row the query returns. Bob's transaction now has to wait until Alice's commits. When it continues, it counts again, sees only one doctor, and refuses.

FOR UPDATE does not help with phantoms, because there are no rows to lock. One workaround is to materialize the conflict: create a table of time slots for each room ahead of time, and lock the slot row before booking. It works, but it leaks concurrency control into your data model, so treat it as a last resort.

The cleaner fix is to ask the database for the strongest guarantee: serializable isolation. It promises that the result is the same as if transactions had run one after another, so write skew and phantoms cannot happen.

Modern implementations like PostgreSQL's serializable snapshot isolation still use snapshots, but they also track what each transaction read. If something it read has changed by the time it commits, the transaction is aborted. Your app retries it, and the second time it sees the truth: one doctor left, refuse.

BEGIN ISOLATION LEVEL SERIALIZABLE;
  -- same SELECT + UPDATE as before
COMMIT;  -- may fail with a serialization error: retry the whole transaction

The cost is occasional aborts and the need for retry logic, which is usually a fair trade for correctness.

If your code reads, decides, then writes, a snapshot alone isn't enough. Either lock what you read, or run the transaction as serializable and retry on conflict. And don't assume your database's default isolation level protects you; check what it actually is.