Architecture Summary : During concurrent checkout spikes, concurrent transactions updating inventory and inserting into order_items in non-deterministic order lock each other in a circular dependency, forcing PostgreSQL to abort transactions with ERROR: deadlock detected . Sorting product IDs before locking ( SELECT ... FOR UPDATE ), setting strict deadlock_timeout values, and isolating reservation locks into transaction-level advisory locks completely removes deadlock crashes.

Production Symptoms in High-Traffic E-Commerce

ERROR: deadlock detected (SQLSTATE 40P01) in PostgreSQL logs during flash sale checkout surges.

Client checkouts failing with HTTP 500 / 504 while shoppers' payment cards have already been pre-authorized.

Cascading connection spikes in PgBouncer, eventually leading to backend connection pool exhaustion.

Root Cause: Non-Deterministic Lock Acquisition Order

Transaction A updates Product 101 then Product 204. Transaction B updates Product 204 then Product 101.…