The New Deadlock Window Created by audit_log Advisory Locks and UPDATE Ordering

The advisory lock that serializes the tamper-detection hash chain is held until commit. In a configuration where audit INSERTs are interleaved immediately after each ads UPDATE, a 40P01 deadlock occurs the moment they cross with row locks held by another transaction. The fix is to consolidate audit writes into a single call placed after all UPDATEs have completed.

Conclusion

The advisory lock that serializes the tamper-detection hash chain into a single sequence is held until the transaction commits. The BEFORE INSERT trigger on audit_log (migration 0003) acquires pg_advisory_xact_lock(hashtext('audit_log')::bigint) to avoid conflicts between concurrent INSERTs, and is designed to hold that lock until the transaction ends. Meanwhile, the portal delivery application process (applyPartnerDelivery) was interleaving audit records for ads that had been removed from or returned to the delivery target in the following order, repeated multiple times per placement: "ads UPDATE → audit INSERT (advisory lock acquired) → ads UPDATE for the next placement." When this process ran concurrently with an ad suspension operation from the admin UI — which follows the same pattern of "ads UPDATE → audit INSERT" — the two transactions could end up each requesting a row the other had already updated. This created a cycle in which the side holding the advisory lock waited for the other's row lock, while the other waited for the advisory lock to be released. PostgreSQL would then detect a 40P01 (deadlock detected) and forcibly terminate one of the transactions. The fix is to consolidate all audit INSERT calls into a single invocation placed after every target row UPDATE has finished.

Full Article

Detailed steps, how to reproduce the issue, and frequently asked questions are covered in the original article.

→ The New Deadlock Window Created by audit_log Advisory Locks and UPDATE Ordering｜Rebounder Tech Blog