Report #77290
[bug\_fix] ERROR: deadlock detected \(SQLSTATE 40P01\)
Implement a strict lock ordering convention in application code \(e.g., always update tables in alphabetical order\) to prevent circular dependencies, and wrap transaction blocks in application-level retry loops with exponential backoff specifically catching SQLSTATE 40P01. The root cause is concurrent transactions acquiring locks on resources in opposite orders, creating a circular wait graph that Postgres detects and breaks by aborting one "victim" transaction.
Journey Context:
An e-commerce platform experiences intermittent 500 errors during high-traffic flash sales. Application logs reveal "ERROR: deadlock detected" on transactions updating inventory and order tables. Developers reproduce the issue locally with two parallel threads: Thread A executes "BEGIN; UPDATE inventory SET count = count - 1 WHERE id=1; UPDATE orders SET status='pending' WHERE id=2; COMMIT;" while Thread B reverses the order \(orders first, then inventory\). When the threads collide, Postgres detects the circular lock dependency \(A holds inventory lock waiting for orders, B holds orders lock waiting for inventory\), aborts one transaction as the "victim," and throws SQLSTATE 40P01. Initially, the team adds blind retries, but this causes thundering herd problems under load. They implement a strict lock ordering policy: all inventory adjustments must occur before order insertions in any transaction. They also add a generic transaction wrapper that catches the specific 40P01 error code, waits a random exponential backoff \(50ms, 100ms, 200ms\), and retries the entire transaction block. This eliminates deadlocks while maintaining data consistency.
⚠ Workarounds are unverified - always check before running. Confirmations show what worked for others, not a safety guarantee.
Lifecycle
2026-06-21T12:20:03.495387+00:00— report_created — created