A hospital requires at least one doctor on call for every night shift. At 8 p.m., doctors Mira and Sam are both assigned. Each opens the scheduling app and chooses to go off call. The app checks that another doctor remains before saving the change.
Each transaction checks the rule. Each commits successfully. The shift ends up with nobody assigned.
The interview question. Reconstruct this failure under PostgreSQL REPEATABLE READ, which uses snapshot isolation. Explain why making reads repeatable does not preserve the rule. Design at least two ways to enforce the invariant and discuss the concurrency cost.
Think first
A support escalation rotation requires at least one active responder per shift. Two responders use PostgreSQL REPEATABLE READ and each turn off their own assignment after confirming two responders are active. Both operations commit. Explain why the rule breaks, prove that no valid serial order produces the result, and design two safe enforcement mechanisms.
Take a few minutes to form an approach, then compare it with the worked explanation.
Reveal the worked explanation
Nobody wrote over anybody else's row
Start with the data as two assignments:
Shift S4
Mira: ON_CALL
Sam: ON_CALL
Rule: at least one ON_CALL assignment must remain
Both transactions read the same two-person snapshot. Mira checks that Sam is on call and updates only Mira's row. Sam checks that Mira is on call and updates only Sam's row.
Transaction M Transaction S
Read Mira=ON, Sam=ON Read Mira=ON, Sam=ON
Decide Sam will remain Decide Mira will remain
Update Mira -> OFF Update Sam -> OFF
Commit Commit
Two snapshot-isolated transactions each read two on-call doctors, update a different assignment row, and commit a result violating the at-least-one-on-call rule
Unlike the lost-update case, no two transactions are writing the same row. A database can detect simultaneous modifications to a single record and still accept this execution. Each transaction is consistent with its own snapshot. The combined outcome is not equivalent to any correct serial execution of both decisions.
Imagine Mira's operation fully finished before Sam began. Sam would see only one doctor remaining and would be denied. Reverse the order and Mira would be denied. The final state with zero doctors cannot arise from either valid sequential ordering. This is write skew, a serialization anomaly allowed by PostgreSQL's REPEATABLE READ. PostgreSQL isolation levels
The important distinction is between a consistent read and a protected invariant. A stable snapshot protects a transaction from seeing a changing picture. It does not make another concurrent transaction respect the decision based on that picture.
Decide what resource actually owns the rule
The invariant belongs to the entire shift, not to Mira's assignment row or Sam's assignment row. That tells us why locking or conditionally updating only the row being changed is insufficient.
One practical design is to create an explicit shift guard row. In a short READ COMMITTED transaction, lock that guard first, query the currently committed assignments, then update the selected doctor only if the count will remain positive.
BEGIN
Lock guard row for shift S4
Count current ON_CALL assignments for S4
If the count is 1, reject the request
Otherwise update this doctor's assignment
COMMIT
All operations that could reduce the shift's on-call coverage must follow that same protocol. Mira acquires the guard, updates her assignment and commits. Sam's transaction waits for the guard. Once it acquires the guard, a new READ COMMITTED statement sees the updated assignments and rejects the second removal.
Why specify the isolation level here? A snapshot established before waiting for the guard under REPEATABLE READ is not simply refreshed afterward. A correct lock-based design has to account for that rule, perhaps through a shared version row that is modified and causes a conflicting transaction to abort, or by selecting an appropriate isolation strategy. Do not copy the pseudocode across isolation levels without revisiting the visibility assumptions.
A second option is to run the scheduling decisions at PostgreSQL SERIALIZABLE isolation and retry an aborted transaction from the beginning. PostgreSQL monitors read/write dependencies that could make successfully committed transactions impossible to arrange in one serial order. In this example, it can reject one of the conflicting commits. The rejected operation must repeat the read and decision using a fresh transaction, at which point the final remaining assignment prevents the change.
That is a different operational contract from locking the guard. The guard makes contenders wait, while serializable snapshot isolation can let them proceed optimistically and abort work when a dangerous conflict is detected. Neither creates free concurrency when many people must decide against one shared invariant.
Could a database constraint protect it?
A simple CHECK (on_call = true) on each row would protect the wrong thing. Off-call assignments are valid individually. The rule is about the count of rows belonging to one shift.
You can restructure the model so the critical decision is expressed by one authoritative row, such as a per-shift coverage record updated transactionally with the assignment. That may make a conditional update enforce the lower bound efficiently. But it also introduces an obligation to keep the coverage record synchronized with the assignments through every write path. It is not enough to cache a count and hope that no other service edits the table directly.
This is a common design review moment. If the rule is global across several rows, ask where all conflicting operations meet. It may be a lock, a serializable transaction, a conditional update of shared state, or a different partition of ownership. The right choice follows the invariant and expected contention.
Try it with a different product
Two support agents may independently remove themselves from an escalation rotation. Two replicas may independently retire the last healthy copy of a dataset. Two account administrators may each revoke the other's emergency access after observing two authorized administrators.
These examples differ in product semantics, but the engineering question is the same: did the transactions read one shared condition and write disjoint records without coordination?
For an interview, draw the two snapshots and both writes. Then show why no valid serial order produces the result. Only after that should you name the solution. Otherwise "use SERIALIZABLE" sounds like an incantation rather than a justified mechanism.
There is another cost to consider. Explicitly locking shared records can make a correct system wait. If two transactions need several locks and take them in opposite orders, they can both wait forever unless the database detects the cycle.
Go deeper
The boundary of serializability matters
A database transaction cannot protect a rule that some writers enforce outside it. If one application updates the assignments through a serializable transaction and another bulk-import job ignores the shift guard, the business still needs a coherent protocol covering both.
PostgreSQL's serializable snapshot isolation can detect dangerous structures through read dependencies and report SQLSTATE 40001. That is a safety mechanism, not a promise that a transaction never aborts. The application must rerun the decision, not blindly resubmit a precomputed OFF update.
Under guard-row locking, ensure every relevant transition takes the guard. Adding a different endpoint that updates an assignment without taking it reintroduces the anomaly. Test concurrent removal attempts with synchronization barriers and assert that at least one remains. Run the same test when one transaction waits on the guard for a long time.
For an actually distributed schedule spanning multiple databases, the local protocol is not enough. You would need to move authority for this invariant to a common system or adopt a coordination protocol that covers all writers. A protocol confined to one database cannot establish that cross-database guarantee.
Check your reasoning
Draw the two snapshots and disjoint writes, identifying write skew rather than lost update.
State the exact shared invariant and demonstrate that either valid serial order would reject the second removal.
Propose a shared shift-guard or authoritative conditional transition that all reducing writers must use.
Describe PostgreSQL SERIALIZABLE abort behavior and a complete transaction retry.
Name the visibility and lock-order assumptions, including why a stale Repeatable Read snapshot cannot simply refresh after lock acquisition.
Common mistake: Treating stable snapshots as serializable, or locking only each doctor's own row without protecting the shift-wide predicate.
Read the answer explanation
Write skew. — Each transaction sees a consistent snapshot but writes a disjoint row, so a multi-row invariant can be violated without a direct write/write conflict.
Apply it elsewhere
Two automated repair services each remove a different replica because they both see two healthy copies. How would you enforce a minimum of one healthy copy?
Compare your approach
Locate one authority for the replica-set invariant, not only per-replica conditions. Coordinate retirement or use a serializable/conditional shared decision with retry.
One more constraint
What changes if the invariant spans regions and the common guard cannot be reached?
Quick revision
Write skew happens when concurrent transactions read the same shared condition and update different rows. Under PostgreSQL REPEATABLE READ, both doctors can see two on-call assignments, each remove their own row, and both commit. No ordinary same-row write conflict occurs. A valid serial ordering would reject the second removal. Protect the whole-shift invariant with a shared guard and fresh read under a suitable isolation level, a restructured authoritative conditional update, or PostgreSQL SERIALIZABLE with complete transaction retries. Name the exact enforcement boundary and make every writer follow it.
