Skip to content
Back to the playbook

Diagnosing and preventing Postgres deadlocks (40P01)

PostgreSQLrevision 1

Diagnostic tree

  1. In progressYou're seeing SQLSTATE 40P01A deadlock always self-resolves: Postgres's background deadlock detector wakes after `deadlock_timeout` (1 second by default) of waiting, finds the wait cycle, and aborts one of the two transactions so the other can proceed. By the time you're reading the error, it's already over — there is nothing 'stuck' left to kill. What's left to do is find out why the two transactions waited on each other and stop it recurring.
View the whole map (8 more steps)
  • Read the DETAIL line Postgres already logged
  • Do the two statements touch the same rows/tables in a different order?
  • Classic lock-ordering deadlock
  • Turn on full lock-wait logging for the next occurrence
  • Not a simple two-table ordering mismatch
  • Enforce a single, consistent lock acquisition order
  • Confirm deadlocks have stopped recurring
  • Resolved

What to test

You're seeing SQLSTATE 40P01

A deadlock always self-resolves: Postgres's background deadlock detector wakes after `deadlock_timeout` (1 second by default) of waiting, finds the wait cycle, and aborts one of the two transactions so the other can proceed. By the time you're reading the error, it's already over — there is nothing 'stuck' left to kill. What's left to do is find out why the two transactions waited on each other and stop it recurring.

What happened?

Passed