PostgreSQL: an atomic transaction does not automatically protect a concurrent business invariant

Putting several statements inside one transaction answers “can half of this operation commit?” It does not, by itself, answer “can two complete operations jointly violate a rule?”

PostgreSQL Read Committed takes a new snapshot for each statement. Serializable instead requires a result consistent with transactions running in some serial order, and may abort a transaction that cannot safely participate. The application must be prepared to retry the complete transaction.

Illustrative case: two workers each read an available-capacity total, then insert a reservation. Each insert can be atomic while both decisions used an incompatible view of availability. Select a design that protects the invariant: an appropriate database constraint, correct locking, or a serializable transaction with retry handling. The right choice depends on the actual query and rule; adding a transaction wrapper is not a proof.

Review concurrent scenarios, including the failure and retry path, rather than only testing a single successful request.

Evidence: PostgreSQL documentation reviewed; no concurrent PostgreSQL experiment was run.