PostgreSQL 40P01: Deadlock Detected During Concurrent Writes
During parallel recording of two records, one transaction ends with a deadlock detected error, although individual records work. Two transactions await each other's locks; PostgreSQL interrupts one to resolve the dependency cycle.
How to determine the source of the error
Check deadlock details in Postgres logs: PID transactions, accounts and statements awaiting lockout.
Compare UPDATE's order of two tables in competing operations and check the transaction length, especially with CRM calls.
What to do step by step
1. Set a coherent order for downloading locks and modifying tables for all code paths of the same process.
2 . Shorten transactions and SQLSTATE 40P01 handle a limited retra of the entire transaction with an idmpotent recording of side effects.
How to check the score
Parallel load tests end with correct results without losing updates and without double webhooks and repeat payments.
What to do
Do not just increase lock timeout as a solution, because this does not remove the cyclical relationship between the transactions.