Report #9683
[bug\_fix] deadlock detected \(SQLSTATE 40P01\) in Postgres concurrent updates
Establish a strict global ordering for acquiring row locks \(e.g., always lock by primary key ascending\) or implement application-level retry logic with exponential backoff for serialization failures.
Journey Context:
Developer maintains an inventory microservice processing orders. Under normal load, everything works. During a flash sale with 1000 concurrent checkouts, the application logs fill with "ERROR: deadlock detected" and "Detail: Process 12345 waits for ShareLock on transaction 67890; blocked by process 54321." Investigation reveals two background workers: Worker A runs a job that transfers inventory between warehouses, updating rows in the order it receives them \(arbitrary\). Worker B runs an audit job that updates the same inventory rows but always sorts by product\_id ascending. When both jobs overlap on the same set of products, Worker A locks row X then tries for row Y, while Worker B already holds Y and wants X, creating a circular dependency. Postgres detects this cycle and kills one transaction \(the victim\), rolling it back. The fix is enforcing a strict locking hierarchy: all code paths must acquire locks on inventory rows ordered by product\_id ascending before updating. This eliminates circular waits because both workers now try to lock X before Y, so one waits for the other linearly rather than circularly. Alternatively, since deadlocks are inevitable in complex systems, the application implements a retry decorator catching SQLSTATE 40P01, waiting random 10-100ms and retrying up to 3 times, which works because the deadlock is broken when the victim is rolled back, freeing its locks.
⚠ Workarounds are unverified - always check before running. Confirmations show what worked for others, not a safety guarantee.
Lifecycle
2026-06-16T08:47:19.806586+00:00— report_created — created