Preventing PostgreSQL Deadlocks on Order Items Tables Under High-Concurrency E-Commerce Loads
BLUF: 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. Because both transactions run concurrently, Transaction A holds Lock 101 waiting for 204, while Transaction B holds Lock 204 waiting for 101. Neither can proceed, resulting in a deadlock cycle.
Production-Grade SQL and Application Architecture
-- 1. Configure aggressive fail-fast timeouts
SET LOCAL deadlock_timeout = '100ms';
SET LOCAL statement_timeout = '2500ms';
-- 2. ALWAYS acquire row locks in deterministic ascending primary key order
-- Sort items in application layer before running query:
SELECT id, stock_quantity
FROM inventory
WHERE id = ANY($1::bigint[])
ORDER BY id ASC
FOR UPDATE;
-- 3. Execute bulk order items insert
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES ($1, $2, $3, $4);
Operational Verification Metrics
pg_stat_database_deadlocks == 0: Total deadlock occurrences per minute must remain zero.pg_stat_activity_waiting_locks < 3: Transactions held in lock wait states must clear in under 20ms.checkout_p99_latency_seconds < 0.20: End-to-end checkout database execution under 200ms.