Two Doctors Withdraw and the Night Shift Empties
A hospital roster service runs on PostgreSQL 15 at the default READ COMMITTED isolation. The table on_call(shift_id, doctor_id, on_duty boolean) has a primary key on (shift_id, doctor_id), and the business rule is that a night shift must never drop below two doctors on duty. Each withdrawal executes as: SELECT count(*) FROM on_call WHERE shift_id='N1' AND on_duty; the application checks that the count is at least 2; then UPDATE on_call SET on_duty=false WHERE shift_id='N1' AND doctor_id=$1; then COMMIT. At 22:00:00.100 two requests for different doctors started, both read a count of 3, both passed the check and both committed; by 22:00:00.400 the count was 1. Row-level locks never conflicted because the two UPDATE statements touched different rows. The team is deciding between raising the isolation level to SERIALIZABLE and adding SELECT ... FOR UPDATE to the counting query.
1. Name the anomaly, explain the mechanism, and say why the row locks did not prevent it.
Read the worked answer
Write skew - a serializability violation in which each transaction reads a predicate over a set of rows and then writes a different row of that same set.
The mechanism: both transactions read the aggregate (count = 3) before either wrote, both computed a valid-looking decision, and both wrote disjoint rows. There is no read-write or write-write overlap on a single row, so no lock and no commit-time conflict check ever fires. The result is a state that no serial order of the two transactions could have produced: serially, the second transaction would have read count = 2 and been forced to fail.
The row locks do not help because an UPDATE only locks the tuples it modifies, and the two doctors are different tuples; the predicate 'on_duty = true for shift N1' is what actually needed protecting, and READ COMMITTED takes fresh per-statement snapshots but never locks the rows a SELECT reads. It is also worth saying in the review: REPEATABLE READ would not have prevented this either - PostgreSQL's snapshot isolation checks write-write conflicts only, so write skew survives the upgrade, which is exactly the trap the team is walking toward.
2. Compare raising the isolation level to SERIALIZABLE against adding SELECT ... FOR UPDATE to the counting query. Recommend one for this workload.
Read the worked answer
SERIALIZABLE (SSI). PostgreSQL tracks read-write dependencies and aborts one transaction with SQLSTATE 40001, serialization_failure, at commit. Correct for arbitrary predicates, including ones the developer did not think of. Price: every rw-antidependency becomes a possible abort, so all write paths need retry loops, and even read-only transactions can be aborted (a DEFERRABLE read-only transaction avoids this).
SELECT ... FOR UPDATE. The counting query locks all on_duty rows of shift N1; the second transaction blocks until the first commits, then, under READ COMMITTED, re-evaluates and sees count = 2, so it refuses. The predicate is materialised explicitly. Price: all edits to a shift serialise - acceptable when it is six rows, pathological if it were a global predicate.
Recommendation: FOR UPDATE here, because the lock footprint is per-shift and tiny, and it keeps the invariant visible in the code. Two details decide whether it is safe: (a) lock deterministically - ORDER BY doctor_id, or better take a single lock on a shifts row or pg_advisory_xact_lock(hash of shift) - otherwise two transactions locking the same six rows in different orders deadlock; (b) keep the covering index on (shift_id, on_duty) so the locking read is not a table scan. Choose SSI instead if predicates are large or unknown.
3. The service is switched to SERIALIZABLE. What must the retry layer handle, and what must remain in application code?
Read the worked answer
Retry layer.
- Catch SQLSTATE 40001 and 40P01 (deadlock_detected) and retry the entire transaction, not the failing statement - the aborted transaction's snapshot and any earlier writes are gone.
- Exponential backoff with jitter and a bounded attempt count (e.g. 5 tries, 5–100 ms), then a 409 to the caller rather than an infinite loop. Contention-driven aborts cluster, so jitter matters.
- Retried bodies must be idempotent with no external side effects: do not page the doctor before commit; publish via an outbox row committed in the same transaction. A retry that has already sent a notification duplicates it.
- Log 40001 rates as an SLI; a rising rate means the transaction is too wide, not that the database is broken.
What stays in the application. SSI guarantees an equivalent serial order, not that the state satisfies the business rule. If the count >= 2 check were dropped, the transaction would still be serializable and still empty the shift. On a 40001 the withdrawal is not applied - the caller must present that honestly rather than reporting success. Finally, add a defence in depth: a per-shift duty counter with a CHECK (on_duty_count >= 2), or an after-commit audit that alerts if any shift drops below two.