Database Isolation and Lost Updates

Putting a read and a write inside a transaction does not automatically preserve every business rule. Two transactions can read the same old value, compute independently, and each write a replacement. One result disappears even though both transactions committed.

Imagine an inventory row with quantity 10. Two buyers each read 10 and compute 9. If both write quantity = 9, the database records one decrement instead of two. The bug is the read-compute-replace operation under the chosen concurrency behavior, not the absence of transaction syntax.

Prefer an atomic expression when it matches the rule

UPDATE inventory
SET quantity = quantity - 1
WHERE item_id = 7 AND quantity > 0
RETURNING quantity;

This PostgreSQL example makes the decrement and eligibility condition part of one statement. The application must check whether a row was returned. No row means the precondition did not hold; it must not report a successful purchase anyway. Keep related order creation in the same appropriately designed transaction.

For an editable document, an optimistic revision check may be a better fit. Update only where the stored revision matches the one the editor read, then advance the revision. If no row changes, show the conflict and reload or merge deliberately. Removing the check to make the update succeed defeats the protection.

Protect invariants spanning several rows

An atomic update to one row does not solve every invariant. Two on-call doctors might each read that the other is available and independently go off call. If they update separate rows, a snapshot can still permit the pair of changes even though the rule requires at least one doctor to remain.

Use a concurrency strategy that covers the invariant: a suitable lock scope, a redesigned representation, or serializable isolation with a retry policy. Isolation levels have engine-specific behavior. PostgreSQL Read Uncommitted behaves as Read Committed, and its Repeatable Read provides guarantees beyond the SQL minimum in some respects.

Retry the transaction, not the final line

A serialization failure means the transaction’s decisions may need to be made again from fresh state. Retry the whole unit within a bounded budget. Ensure external side effects are not duplicated when a transaction is retried; an outbox or explicit operation identity may be needed.

Write a concurrency test with two connections and controlled interleaving. Assert the final business invariant and both callers’ reported outcomes. A sequential unit test that never overlaps the reads cannot establish that this class of bug is fixed.

Reference tables to keep nearby

• Database Isolation and Concurrency Anomalies — Concurrency anomalies, example interleavings, and mitigation choices with PostgreSQL-specific caveats.

Primary sources

These are the specifications and project documentation behind the examples. Check the relevant implementation and version before applying a pattern.

• PostgreSQL transaction isolation
• PostgreSQL explicit locking