DEV Community

Anas Rhimi
Anas Rhimi

Posted on Originally published at anasrhimi.tech on

Preventing PostgreSQL Deadlocks on Order Items Tables Under High-Concurrency E-Commerce Loads

#postgres #database #devops #sql

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);
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (0)