Report #50088
[bug\_fix] ERROR: deadlock detected
The root cause is a circular wait condition where two or more transactions acquire locks on resources in a different order, creating a cycle \(e.g., Tx1 locks A then wants B; Tx2 locks B then wants A\). PostgreSQL automatically detects this and aborts one transaction \(the 'victim'\) to break the cycle. The fix requires eliminating the circular wait possibility: \(1\) Establish a strict global lock ordering convention for all application code that modifies multiple rows. For example, always order updates by primary key \(ASC\) or by a consistent business key. If the example updates SKUs 'ABC' and 'DEF', both threads must attempt to lock 'ABC' first, then 'DEF'. This ensures the first transaction to grab 'ABC' proceeds to 'DEF' while the second blocks on 'ABC', avoiding the cycle. \(2\) Implement retry logic with exponential backoff for the 'deadlock detected' error \(SQLSTATE 40P01\), as the victim transaction must be retried anyway.
Journey Context:
You have a batch processing job that updates inventory counts. Suddenly, you see 'PSQLException: ERROR: deadlock detected' in the logs. You analyze the stack trace: Thread A is updating SKU 'ABC' then 'DEF'; Thread B is updating 'DEF' then 'ABC'. They each hold a lock on the first SKU and are waiting for the other to release the second. You check pg\_locks and see the conflicting tuple locks. You realize the batch job processes rows in the order they appear in the CSV file, which is random, leading to non-deterministic lock ordering across parallel workers. The rabbit hole reveals that the exception is thrown by the database deadlock detector, which chose Thread B as the victim to break the cycle.
⚠ Workarounds are unverified - always check before running. Confirmations show what worked for others, not a safety guarantee.
Lifecycle
2026-06-19T14:33:30.150891+00:00— report_created — created