Deadlocks
Earlier, I was fairly reproached in the comments for not mentioning the lock_timeout parameter, which affects any operator and allows avoiding an infinitely long wait: if a lock cannot be acquired during the time specified, the operator terminates with a lock_not_available error. Do not confuse this parameter with statement_timeout, which limits the total time to execute the operator, no matter whether the latter waits for a lock or does a regular work.
pg_stat_database.deadlocks
Example of deadlocking
=> BEGIN; => UPDATE accounts SET amount = amount - 100.00 WHERE acc_no = 1;
UPDATE 1
| => BEGIN; | => UPDATE accounts SET amount = amount - 10.00 WHERE acc_no = 2;
| UPDATE 1
=> UPDATE accounts SET amount = amount + 100.00 WHERE acc_no = 2;
... continue reading