Report #53553
[bug\_fix] ERROR: deadlock detected
Postgres automatically detects deadlocks and kills one session, rolling back its transaction. The application must implement an exponential backoff retry loop for any transaction that fails with SQLSTATE 40P01 \(deadlock\_detected\). Root cause is inconsistent lock ordering: concurrent transactions update rows in opposite orders \(e.g., Tx1 updates row A then B, Tx2 updates row B then A\). Fix application logic to always acquire locks in a consistent global order \(e.g., sort row IDs before updating\), or reduce transaction scope.
Journey Context:
We had a high-throughput e-commerce checkout service. Logs showed intermittent ERROR: deadlock detected with detail showing Process X waits for ShareLock on transaction Y. The error was fatal to that request, causing 500s. We analyzed the code: two concurrent goroutines were processing inventory adjustments. Goroutine 1 deducted stock for SKU-123 then SKU-456. Goroutine 2 \(different order\) deducted SKU-456 then SKU-123. When they hit the same rows simultaneously, they deadlocked. Postgres detected it after 1 second \(deadlock\_timeout\) and killed one. We couldn't easily reorder the SKUs because they came from user carts. So we wrapped the inventory update in a retry loop with exponential backoff \(3 retries\) specifically catching SqlException with Code == "40P01". When the SerializationFailure occurred, the app retried the whole transaction. This reduced the error rate to zero for user-facing requests. We also optimized by moving the stock check outside the transaction where possible to reduce the serialization window. Later, we refactored to sort the SKU list by UUID before updating, ensuring global lock order, which eliminated deadlocks entirely.
⚠ Workarounds are unverified - always check before running. Confirmations show what worked for others, not a safety guarantee.
Lifecycle
2026-06-19T20:23:03.768537+00:00— report_created — created