An isolation level is a contract about which concurrent histories a database may expose. Its name is only a starting point. I would choose it by writing down the business invariant and a conflicting schedule, then checking whether the database can admit that schedule under the selected level.
Read the engine's contract, not a generic label
PostgreSQL documents concrete behaviour for Read Committed, Repeatable Read and Serializable transactions. At Read Committed, each statement takes a new snapshot of committed data, so two queries in one transaction can observe different committed states. Repeatable Read provides a stable snapshot but can still permit serialization anomalies. These details are more useful than memorising a universal table and assuming every database implements each label identically. Even the treatment of a concurrent row update can depend on the engine's rules for waiting, rechecking and aborting.
The application consequence is that a transaction block does not automatically make every read-based decision safe. A transaction can execute several individually valid statements whose combined effect violates an invariant when another transaction runs at the same time. I would describe the invariant in terms of observable state, such as at least one operator remains on call, rather than in terms of implementation steps. Then list which rows and predicates the decision reads and which rows it writes. The gap between those sets often reveals the concurrency problem before any SQL is changed.
References: [1] PostgreSQL transaction isolation
Write skew does not require two writers on one row
Consider an illustrative table with Alice and Bob both marked on call. Each transaction checks that two operators are available and then takes its own operator off duty. Under snapshot isolation, the transactions can read the same initial state and update different rows without a direct write-write conflict. If both commit, nobody remains on call. Each transaction made a sensible decision according to its snapshot, yet the combined history violates the invariant. This is the kind of anomaly a stable snapshot alone does not rule out.
The schedule below is conceptual and assumes separate sessions with no additional constraints, locks or triggers enforcing the invariant. It is not a portable prediction for every database. In PostgreSQL, Repeatable Read permits this general class of serialization anomaly, while Serializable may abort a participant so the application can retry. A useful test deliberately synchronises the reads before allowing either update. Merely running two requests at roughly the same time is a weak test because normal scheduling may accidentally serialise them and conceal the bad interleaving.
| Step | Transaction A | Transaction B |
|---|---|---|
| 1 | Reads on-call count = 2 | |
| 2 | Reads on-call count = 2 | |
| 3 | Sets Alice off call | |
| 4 | Sets Bob off call | |
| 5 | Commits | Commits if the isolation contract permits this history |
References: [1] PostgreSQL transaction isolation
Move the invariant into a conflict the database can see
One solution at PostgreSQL Read Committed is to lock a shared roster coordination row, then check membership in a fresh statement after acquiring that lock. At Repeatable Read, merely locking an unchanged sentinel can leave a waiting transaction with a stale snapshot; actually updating the sentinel can instead force a conflicting transaction to abort and retry. Other options include a suitable declarative representation or Serializable transactions with retries. Locking only the operator row being changed does not protect the multi-row invariant. The conflict mechanism and snapshot behaviour must both match the decision's read dependency.
Explicit locks also require an ordering discipline when a transaction needs several resources. Otherwise two valid code paths can acquire them in opposite order and deadlock. PostgreSQL's locking documentation describes lock conflicts and deadlock handling; my application-level preference is to make acquisition order visible in the transaction helper rather than scattered across unrelated calls. A database detecting a deadlock is a containment mechanism, not proof that the workflow is well designed. Keep transactions short, avoid waiting on remote services while holding locks, and make abort-and-retry behaviour part of the normal contract.
References: [2] PostgreSQL explicit locking
An atomic expression can be safer than a read-modify-write loop
Suppose a client reads a balance of 100, computes 90 and later writes that literal value. Another client can perform a similar calculation from the same old value, creating a lost-update risk unless the operation detects the conflict. An in-database expression such as balance = balance - 10 gives the database a different operation to coordinate. A conditional update can also encode a local predicate, such as sufficient balance, and return whether a row changed. This does not solve every multi-row invariant, but it narrows the race for a single-row decision.
I would distinguish that local atomicity from a full business transaction. Transferring funds between accounts also needs the credit, debit, currency rules and relevant ledger state to agree. A zero-row result may mean insufficient funds, a missing account or a failed version precondition, depending on the statement. The application must interpret it deliberately. Similarly, optimistic concurrency with a version column works only when every relevant writer participates and a failed comparison triggers recomputation from current state. Retrying the same stale literal value under a new version is not conflict resolution.
References: [1] PostgreSQL transaction isolation
Retry the decision, not just the failing statement
Serializable execution can require the application to retry transactions that cannot safely commit. The retry should normally rerun the entire transaction's decision logic from a fresh snapshot, including the reads that informed its writes. Retrying only the final UPDATE preserves a conclusion drawn from an invalidated history. I would use a bounded retry policy with jitter and an overall deadline, because high contention can otherwise turn correctness enforcement into a load-amplification loop. Track retry frequency by bounded operation type to discover invariants whose contention deserves a different representation.
External effects must remain outside an automatically retried transaction body unless their semantics explicitly support repetition. Sending a confirmation email before commit can produce a message for an aborted transaction; sending a payment request can be far worse. A transaction can record an intention for later delivery as part of its durable result. This is an application boundary rather than a special property of Serializable. The same issue appears with deadlock retries and optimistic concurrency failures. Correct database state is only one component of a workflow whose surrounding effects users can observe.
References: [1] PostgreSQL transaction isolation
Stronger isolation has costs and limits
A reasonable counterargument is that choosing Serializable everywhere may introduce aborts, extra work and operational surprises that a simpler invariant-specific design could avoid. That is true. A low-contention administrative workflow may benefit from the broad protection, while a very hot counter may require a more specialised representation. The right comparison is not between correctness and performance as abstract opposites. It is between complete designs that enforce the required invariant, including the complexity of manual locking, version checks and retries that weaker isolation may push into application code.
Serializability also should not be casually expanded into every possible ordering guarantee. It means committed transactions are equivalent to some serial execution under the database's contract; real-time ordering and external observations require attention to the specific system and access path. Reads from a lagging replica, caches outside the transaction and asynchronous messages can expose older state even when the primary database's transactions are sound. I would draw the boundary around the guarantee and identify which user-visible operations cross it. An isolation setting cannot make an unrelated cache participate in the transaction.
References: [1] PostgreSQL transaction isolation
Test histories and invariants directly
The strongest tests create controlled interleavings: pause after the decision read, let another transaction commit, then resume the first. Assert the invariant over final committed state and check which outcomes the API reports. Include a transaction that aborts after another has observed related data, a retry that reaches its deadline, and a client whose connection disappears around commit. Tests should accept legitimate aborts where the contract allows them rather than treating every exception as a database bug. What matters is whether any successful history violates the promised rule.
I would keep a short concurrency explanation near each important invariant: the relevant rows or predicates, the isolation level, the conflict mechanism and the retry policy. That documentation is more durable than a comment saying the code is transactional. It helps future changes preserve the reason the workflow was safe, especially when a new writer or reporting path appears. Isolation becomes easier to reason about when it is described as behaviour that users can observe under overlap, rather than a setting selected once and then forgotten.
References: [1] PostgreSQL transaction isolation[2] PostgreSQL explicit locking
Sources and further reading
- PostgreSQL transaction isolation
Primary documentation defines PostgreSQL-specific snapshot and Serializable behaviour. The on-call scenario and application design comparisons are illustrative analysis.
- PostgreSQL explicit locking
Primary documentation explains locks and deadlocks. The essay's coordination-row and testing recommendations are application-level interpretations.