Read the schedule before selecting isolation

Concurrency bugs become easier to explain when you write the order of reads and writes. A transaction is not a promise that every business rule is safe. The isolation level controls which interleavings the database permits. Constraints protect the rules they express. Application code must connect those mechanisms to the actual invariant.

Consider two editors adjusting a stock count. Both read ten units. One sells three and writes seven. The other sells four and writes six. The final six ignores the first sale. A direct subtraction in a conditional database update can avoid this read-compute-write race. The application asks the database to subtract only when enough units remain, then checks whether a row changed. It does not trust a previous read.

A different problem crosses rows. Two on-call engineers each see that the other is available and remove themselves from duty. Both updates can target different rows, so a row-level uniqueness check cannot express the rule that at least one person must remain. An application can serialize decisions through one shared schedule row, or use an isolation strategy that detects the conflicting reads and retries an aborted transaction. The choice depends on contention and the transaction's shape.

PostgreSQL documents differences between Read Committed, Repeatable Read, and Serializable, including serialization failures. Do not translate that into a claim that every database uses identical semantics. Serializable work can require retries. A retry must repeat the whole transaction against fresh state. Replaying only the failed final statement can preserve decisions made from an invalid earlier read.

The fictional inventory row is quantity 5. Buyer A requests 4. Buyer B requests 3. Each issues a conditional subtraction against the same row: decrease quantity by requested amount only where quantity is at least that amount. If A wins, quantity becomes 1. B changes zero rows and receives out-of-stock. If B wins, quantity becomes 2 and A changes zero rows. Either valid result preserves nonnegative stock. Neither result promises fairness.

For the on-call example, use PostgreSQL Read Committed and choose a schedule guard row. Acquire the guard before the count query, and require every transaction that changes membership to follow this same guard protocol. Both transactions lock the same guard before inspecting members. A sees two members and removes itself. B subsequently sees one and rejects its own removal. Locking different member rows would not provide this ordering.

This PostgreSQL teaching statement moves the decision into one database operation. The application supplies a positive requested quantity and checks the returned row. Input validation must reject zero or negative quantities before this statement. A production schema should also enforce the appropriate numeric bounds.

1UPDATE inventory2SET quantity = quantity - :requested3WHERE sku = :sku4  AND quantity >= :requested5RETURNING sku, quantity;

A returned row means this statement performed the subtraction. No returned row can mean insufficient quantity or an unknown SKU, depending on the API's chosen error contract. The application should not announce success merely because executing the statement raised no exception. In this example, a zero-row result is a normal losing outcome.

The operation still needs a transaction boundary if a sale record must commit with the inventory decrement. Otherwise a crash after decrement but before recording the sale can reduce stock without an auditable purchase. Put the decrement and sale creation in one transaction and include an operation key when callers can retry. The conditional write solves one race; it does not solve every part of the workflow.

Use this schedule to test the on-call rule. Both member rows begin active. The business invariant says at least one active member must remain.

Each transaction changes a different row. A lock only on the row being removed does not make their earlier shared decision safe. The losing result has zero active members. A guard row for this schedule creates a common decision point. Alternatively, a correctly implemented serializable transaction can detect an invalid dependency and abort one participant, with the application retrying the whole decision.

The retry is semantically important. Suppose A commits and B receives a serialization failure. B must begin again, read the new state, see only one active member, and decline removal. Retrying only B's final update would reuse the conclusion from the old count and reproduce the problem. A retry loop should also be bounded and should preserve request identity when the surrounding operation has side effects.

A single global guard for every schedule is safe for the count rule but can cause unrelated teams to wait on each other. Scope the guard to the resource whose invariant spans the member rows, such as one schedule. If a transfer touches two resources, choose a deterministic lock order. For example, acquire the lower schedule ID first, then the higher. This reduces a common deadlock pattern in which two transactions each hold one lock and wait for the other.

Deadlocks and serialization failures are different mechanisms, but both can require transaction retry. Do not hide all database errors behind the same automatic retry. A unique constraint violation caused by an invalid duplicate request, or a malformed value, may need a different outcome. The application must classify the error and repeat only work whose contract permits it.

The first misconception is that placing BEGIN and COMMIT around a read and write automatically protects every invariant. Transactions provide defined atomicity and isolation behavior, but the chosen isolation level may still permit the harmful schedule. State which anomaly the design prevents.

A guard lock does not refresh a snapshot that a transaction already established under Repeatable Read. The guard example therefore assumes Read Committed, with the count executed after the guard is acquired. If the isolation or read order changes, prove the schedule again rather than assuming that waiting for a lock refreshes every later read.

The second misconception is that increasing isolation removes the need to handle conflicts. Stronger isolation can deliberately reject work that cannot be serialized. That rejection is how the guarantee is preserved. An application that treats every abort as an unexplained server failure can provide poor user behavior even while the database remains correct.

Start with quantity 8. A requests 5, B requests 4, and C requests 3. Give two valid schedules. A then C yields two successes and zero remaining, with B rejected. B then C yields two successes and one remaining, with A rejected. The exact winning set depends on order. Award one point for each valid result and one for explaining why correctness does not imply fairness. A fair reservation policy requires an additional admission or ordering contract.

Three workers read a quota of 6 and each tries to spend 3. Determine the permitted result under a conditional decrement. Exactly two can succeed and one must fail, assuming no other changes. Explain what the application must inspect. It must inspect affected rows or an explicit returned result, not a cached balance. Award two points for the resulting quota zero and one for explaining why a separate earlier check is unsafe.

When would you avoid a global guard lock? A strong answer discusses unrelated customers blocking one another and scopes the lock to the business resource. Follow up with lock ordering across two resources. A weak answer chooses Serializable without mentioning abort handling. Draw the interleaving first. Select the smallest control that protects the stated invariant, then test the losing path as carefully as the winning path.