Why should you care?

PostgresVerse wallet transfers spike deadlocks 40P01 during sale — ops needs playbook not guesswork.

See it live — copy this example

Run in pgAdmin or psql.

SELECT blocked.pid AS blocked_pid,
       blocked.query AS blocked_query,
       blocking.pid AS blocking_pid,
       blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid AND NOT bl.granted
JOIN pg_locks kl ON kl.locktype = bl.locktype
  AND kl.database IS NOT DISTINCT FROM bl.database
  AND kl.relation IS NOT DISTINCT FROM bl.relation
  AND kl.page IS NOT DISTINCT FROM bl.page
  AND kl.tuple IS NOT DISTINCT FROM bl.tuple
  AND kl.transactionid IS NOT DISTINCT FROM bl.transactionid
  AND kl.classid IS NOT DISTINCT FROM bl.classid
  AND kl.objid IS NOT DISTINCT FROM bl.objid
  AND kl.objsubid IS NOT DISTINCT FROM bl.objsubid
  AND kl.pid <> bl.pid
JOIN pg_stat_activity blocking ON blocking.pid = kl.pid
WHERE blocked.datname = 'PostgresVerse';

What happened?

• Join pg_stat_activity and pg_locks to see who blocks whom.
• blocked_query and blocking_query identify conflicting SQL.
• Log detail adds exact order of lock acquisition.

Practice next

1. Reproduce deadlock in two-session lab.
2. Find deadlock section in postgresql.log.
3. Run blocking query during contention.
4. Log lock waits: SET log_lock_waits = on; SET deadlock_timeout = '1s' in lab.
5. Graph deadlock rate in app metrics after fix.

Remember

Log shows cycle of transactions and statements. pg_locks shows live block tree. Fix ordering and shorten transactions.

PostgresVerse transfer fix

Team adds consistent account lock order; retries 40001 once; deadlocks drop 95%.

Outcome: Support tickets for failed transfer nearly zero.