Two doctors can go off call only if at least one remains. Both check the rule at the same moment, both see two doctors on call, and both go off call. What isolation problem is this, and what actually fixes it?

Short answer

This is write skew. Two transactions read the same set, each updates a different row, and the pair commits a state that violates an invariant neither would have broken alone. Read committed permits it because there is no write-write conflict to detect — the rows differ. The fix is serializable isolation, a lock on something both transactions must touch, or remodelling the invariant so it lives on one row.

Why no constraint can catch this

The first thing worth saying out loud is that the rule is not a property of any row. "At least one doctor is on call" is a property of the set of rows for that shift. A CHECK constraint sees one row at a time and cannot count its siblings. A UNIQUE constraint enforces that two rows do not collide, which is the opposite shape of what you need.

That is why write skew is a category of its own rather than a variation on something more familiar. A lost update is two transactions writing the same row, one silently overwriting the other; the engine can see that conflict because the rows collide. A dirty read is reading uncommitted data, which every isolation level above read uncommitted already prevents. Here neither applies. Each transaction touches its own doctor's row, commits cleanly, and the invariant dies in the gap between them — with no error, no conflict, and nothing in the logs to suggest anything went wrong.

What read committed actually promises

Read committed guarantees that every statement sees only data committed before that statement began. It does not promise that what you read stays true until you commit. That gap is the entire vulnerability, and candidates who can state the guarantee precisely usually find the bug without being led to it.

-- session A                              -- session B
BEGIN;                                    BEGIN;
SELECT count(*) FROM shifts               SELECT count(*) FROM shifts
  WHERE on_call = true;   -- 2              WHERE on_call = true;   -- 2

-- both read 2, both conclude "safe for me to leave"

UPDATE shifts SET on_call = false         UPDATE shifts SET on_call = false
  WHERE doctor = 'alice';                   WHERE doctor = 'bob';
-- different row, no lock contention      -- different row, no lock contention
COMMIT;                                   COMMIT;

-- invariant violated: zero doctors on call, and no error was raised

There is no moment in that schedule where the database could reasonably have complained. Both reads were legal. Both writes were legal. Neither transaction ever observed the other. Every individual operation is correct and the combination is wrong, which is what makes this a genuinely instructive failure rather than a coding mistake.

Repeatable read does not save you

The common wrong answer is to raise the level one notch. Repeatable read gives each transaction a stable snapshot, so the count it read at the start stays readable throughout. That sounds protective and is not: it makes both transactions more confident in a count that was already stale by the time they acted on it. Postgres will not raise a serialisation error here, because neither transaction wrote a row the other read a version of.

Saying this explicitly is worth marks, because it shows the candidate understands that isolation levels are not a severity dial where higher always means safer against every anomaly. Snapshot isolation prevents dirty reads, non-repeatable reads and phantom reads, and still permits write skew by design. That gap is exactly why serializable snapshot isolation was invented as a separate thing.

Serializable, and what it costs the caller

Serializable isolation is the direct fix. Postgres implements it as Serializable Snapshot Isolation, tracking read-write dependencies between concurrent transactions and aborting one when the pair cannot be ordered into any equivalent serial schedule. Here no serial order produces both updates — run A then B and B sees one doctor and refuses — so one transaction fails with SQLSTATE 40001, serialization_failure.

The cost is the part candidates skip and the part interviewers listen for. Serializable does not make the code correct by itself. It converts a silent data corruption into a runtime error, and something must catch that error and retry the whole transaction:

for (int attempt = 0; attempt < MAX_RETRIES; attempt++) {
    try (var tx = db.begin(SERIALIZABLE)) {
        if (tx.countOnCall(shiftId) <= 1) throw new LastDoctorException();
        tx.setOnCall(doctor, false);
        tx.commit();
        return;
    } catch (SQLException e) {
        if (!"40001".equals(e.getSQLState())) throw e;   // not a serialisation failure
        // retry: the transaction is re-executed from the start, re-reading the count
    }
}
throw new TooMuchContentionException();

The retry must re-run the reads, not just the writes — a retry that reuses the stale count reintroduces the bug it was meant to fix. If your data access layer maps 40001 to a generic 500, you have traded a rare wrong answer for a routine user-visible failure. Throughput is the second cost: SSI keeps predicate lock information and aborts more often as contention rises, so a hot table can spend real capacity on doomed work.

Materialising the conflict with a lock

If you cannot move the system to serializable, the alternative is to manufacture the conflict the database was missing. The instinct is to lock the row you are about to write, and that is precisely the move that fails: Alice locks Alice's row, Bob locks Bob's row, and they never contend.

The lock must sit on something both transactions are forced to acquire — the parent shift row, since both doctors belong to one shift:

BEGIN;
SELECT id FROM shifts_meta
  WHERE shift_id = 42
  FOR UPDATE;              -- both transactions serialise here, on the same row

SELECT count(*) FROM shifts WHERE shift_id = 42 AND on_call = true;
-- this count is now trustworthy for the remainder of the transaction

UPDATE shifts SET on_call = false WHERE doctor = 'alice';
COMMIT;

This is coarser than serializable, because it serialises every change to the shift including ones that could safely have run concurrently. In exchange it is predictable, needs no retry path, and works at any isolation level. A Postgres advisory lock keyed on the shift id achieves the same effect when there is no natural parent row to lock.

Remodelling so the invariant fits on one row

The third option changes the schema rather than the concurrency control. Keep an on_call_count on the shift row with CHECK (on_call_count >= 1). The invariant is now a single-row property, both transactions update that same row, and the second one either blocks on the row lock or fails the constraint.

What you give up is that the counter is derived state which can drift from the rows it summarises. Every path that changes on_call must go through the counter, and a reconciliation job should periodically prove the two still agree. That is a real and permanent maintenance cost, worth naming rather than presenting the counter as a free win.

Recognising the shape elsewhere

The doctors are a stand-in, and the strongest answers say so. The same failure appears whenever a rule constrains a set while transactions write individual members of it: two bookings claiming the same meeting room, two withdrawals that each pass a spending limit the pair exceeds, two deletions that each leave "at least one admin" true, two allocations that each fit within remaining inventory. Naming the general shape and one other instance of it is usually what separates an answer that understood the mechanism from one that only remembered the term.

---

© 2026 Preptima. Originally published at preptima.com.