PostgreSQLERROR 40P01

PostgreSQL: Deadlock Detected

PostgreSQL 40P01 is raised when the deadlock detector finds a cycle of transactions each waiting for a lock held by another. PostgreSQL automatically picks one transaction as the victim and aborts it.

Example Query

-- Transaction A: UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Transaction B: UPDATE accounts SET balance = balance + 100 WHERE id = 1;
-- Transaction A: UPDATE accounts SET balance = balance - 50 WHERE id = 2; -- deadlock

Common Causes

  1. 1Transactions locking rows in different orders
  2. 2Long transactions that accumulate many locks
  3. 3Bulk operations without explicit ordering

How to Fix It

Always acquire locks in a consistent order (e.g. ORDER BY id). Keep transactions short. Implement retry logic for 40P01 in the application — it is transient and a retry usually succeeds.

Need a reliable database?

Try Supabase — free PostgreSQL with a generous free tier. No credit card required.

Get started free →

Got a query causing this error?

Paste it into SQLbuddy and get an instant plain-English explanation with optimization tips.

Analyze My Query →
← Browse all SQL errors