Two support engineers share a shift; at least one must remain. Both ask to leave simultaneously. Each request checks the roster, sees the other engineer and proceeds. Both transactions update their own records and commit. Nobody is left on call.
Every individual update in this example is complete. The broken rule connects several records. That makes isolation a product decision, not merely a database setting.
Correct data can still be an unsafe basis for a decision
Weaker isolation need not mean reading corrupt or uncommitted data. Even committed information can be an unsafe basis for a decision when another transaction is changing the relevant state.
Each roster request was reasonable given what it saw. The failure arose from combining the two decisions. This is an example of write skew: transactions update different records but jointly violate an invariant. A version check on one engineer's own record will not detect that the other engineer's record also formed part of the decision.
A better requirement is explicit: after any roster change, at least one engineer remains on the shift. “We use transactions” does not identify the concurrent outcome that must be prevented.
What the isolation setting changes
Read Committed prevents reads of other transactions' uncommitted changes. However, successive reads within the same operation may see different committed states. A report could read the roster and then separately count available staff after another transaction has changed it. Both queries can be individually correct without representing one common snapshot.
Implementation details matter. In PostgreSQL, an ordinary Read Committed query uses a statement-level snapshot. Repeatable Read uses a stable snapshot established by the transaction's first substantive query. That supports consistent reporting, but the roster anomaly can still occur.
Shared level names do not make database implementations interchangeable. PostgreSQL and MySQL's InnoDB differ in locking and reading behaviour at Repeatable Read; check the actual version. Also establish whether reads use the primary or a lagging replica. Isolation on the primary does not remove replication lag elsewhere.
Serializable does not mean physically running everything one at a time
A serializable outcome must be explainable by some serial order of the same transactions. If the engineers had genuinely left the shift one after another, the first request could succeed, while the second would see that nobody else remained. A final roster with neither engineer cannot result from that sequence.
Databases implement this guarantee in different ways. PostgreSQL's Serializable mode adds monitoring for dangerous dependencies and may reject a transaction with a serialization failure. It need not put every operation into one shared queue. Detection, discarded work and retries still have a cost.
The rejected transaction must run again in full: read the roster, check coverage and then decide whether to write. Repeating only the last UPDATE with the old conclusion that another person remains simply repeats the application's mistake.
The guarantee has a scope. An administrative script bypassing roster checks can undermine the rule. Every participating read and write needs a compatible protection strategy.
Sometimes the rule is better expressed another way
Increasing isolation is not the only option. For a unique request number, a database uniqueness constraint is usually more direct than a preliminary query that says the number does not exist yet. A quota represented by one counter may be protected with an atomic conditional update that reduces it only when enough capacity remains.
The roster could instead have one shared shift record and an explicit protocol: every membership change first protects that record, then checks the current membership and writes within a short transaction. Every writer must follow the protocol. Locking only the engineer's own record does not coordinate with another engineer updating a different row.
Nor can every multi-row rule be expressed as an ordinary CHECK constraint. PostgreSQL CHECK constraints are not intended to inspect arbitrary other rows. Choosing among a different data model, explicit locking and Serializable requires understanding the invariant, rather than selecting the strongest-sounding setting.
A useful acceptance test
A single request tells us little. Use two independent database sessions; make both read the roster before either writes, then let them finish. Assert that coverage remains, not merely that response status codes look correct.
With a correct design, a request may wait, fail or retry and reach a different decision. The interface needs to explain the meaningful outcome: “You are now the only engineer on this shift.” A generic error followed by an endless loading indicator is not adequate conflict handling. Automated retries need time and attempt limits.
Measure the strategy through lock waiting, aborted transactions, retries and the time users wait for a completed action. A faster response that leaves the shift uncovered is not an acceptable performance improvement.