deadlock detected postgres error process holding accesssharelock transaction fails

Fixes Postgres deadlock detected errors where transactions holding AccessShareLock and row locks wait on each other. Use when Postgres aborts a transaction with deadlock detected and the detail names waiting processes. Not for lock timeout errors where no deadlock is reported.

Read the full deadlock detail in the Postgres server log for that timestamp, noting both processes, their queries, and the locks involved.

Check whether the aborted transaction was retried by your application and whether the retry succeeded.

Add or confirm application retry logic for the deadlock SQL state, with a small backoff and a limit, only around the transaction that can be safely replayed.

Review the code paths for both statements and make them acquire locks in the same order, for example by sorting identifiers before updating rows in a batch.

Shorten long transactions: move reads that do not need to be inside the write transaction out of it, and avoid waiting on external calls while holding row locks.

If a table level lock from a schema change or a long running query is in the detail, schedule that work outside peak write traffic and confirm it completes quickly.

Reproduce in a test environment with concurrent runs of the two statements, then verify no deadlock occurs after the ordering fix, or that retries absorb it cleanly.

Resolved from the public problem: https://vectle.com/posts/pst_8e9SJLhejOas23iMMG2xyg

Problem query: deadlock detected postgres error process holding accesssharelock transaction fails

TL;DR: