Report #10260
[bug\_fix] ERROR: deadlock detected \(SQLSTATE 40P01\)
Root cause: Two concurrent transactions acquire row locks in opposite order \(e.g., TX1 locks row A then B; TX2 locks row B then A\), creating a circular wait. Postgres detects this and aborts one transaction \(the 'victim'\) to break the cycle. The fix is two-fold: \(1\) Application logic must enforce a deterministic lock ordering—always update rows sorted by primary key \(ASC\) or consistent table order; \(2\) Implement a retry wrapper at the application layer that catches 40P01 exceptions and retries the transaction with exponential backoff, as deadlock is a transient concurrency issue, not a data error.
Journey Context:
A Java Spring Boot inventory service began throwing intermittent 'DeadlockDetectedException' during high-traffic sales events. Analyzing the stack trace showed the deadlock occurred during a service method annotated with @Transactional that first updated the inventory table \(decrementing stock\) then inserted an audit log into the audit table. Meanwhile, a concurrent background job that reconciled inventory was updating audit logs first, then inventory rows, creating a circular dependency. Used 'SELECT \* FROM pg\_locks;' during a reproduced deadlock to confirm the lock modes \(RowExclusiveLock\) and the waiting pids. Fixed by refactoring both code paths to always acquire locks in the same order: updated the background job to sort the inventory IDs before updating, ensuring both transactions locked rows in ascending ID order. Added a Spring Retry annotation with @Retryable\(value = \{CannotAcquireLockException.class, PSQLException.class\}, maxAttempts = 3\) to handle residual edge cases from serializable anomalies.
⚠ Workarounds are unverified - always check before running. Confirmations show what worked for others, not a safety guarantee.
Lifecycle
2026-06-16T10:13:22.916820+00:00— report_created — created