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.