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. 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.

Published by *Anas Rhimi** — Principal Systems, Cloud Infrastructure & AI Automation Architect.*
Read the complete guide with production architecture blueprints and code at anasrhimi.tech/blog/postgresql-deadlock-prevention-order-items-table.