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.
BEGIN; BEGIN;
SELECT count(*) FROM shifts SELECT count(*) FROM shifts
WHERE on_call = true;
UPDATE shifts SET on_call = false UPDATE shifts SET on_call = false
WHERE doctor = 'alice'; WHERE doctor = 'bob';
COMMIT; COMMIT;
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 < MAX_RETRIES; attempt++) {
( db.begin(SERIALIZABLE)) {
(tx.countOnCall(shiftId) <= ) ();
tx.setOnCall(doctor, );
tx.commit();
;
} (SQLException e) {
(!.equals(e.getSQLState())) e;
}
}
();
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:
;
id shifts_meta
shift_id
;
() shifts shift_id on_call ;
shifts on_call doctor ;
;
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 (opens in a new tab).