PostgreSQL Error 40P01: Deadlock Detected
Troubleshoot PostgreSQL SQLSTATE 40P01 by identifying conflicting transactions, using consistent lock order, and retrying safely.
On this page
PostgreSQL SQLSTATE 40P01 is deadlock_detected. A deadlock occurs when two or more transactions each hold a lock that another transaction needs, leaving none able to continue. PostgreSQL detects the cycle and aborts one transaction; which transaction is chosen is unpredictable. The PostgreSQL error-code appendix classifies 40P01 as a transaction rollback.
Understand how a lock cycle forms
A common pattern is two transactions that lock the same rows in opposite orders:
| Transaction | First lock | Next lock |
|---|---|---|
| A | Row 1 | Waits for Row 2, held by B |
| B | Row 2 | Waits for Row 1, held by A |
The same pattern can involve tables or advisory locks. Deadlocks can also arise from ordinary UPDATE or DELETE statements even when application code never issues LOCK explicitly. PostgreSQL provides a deadlock example and prevention guidance.
Review the error detail and lock order
Capture the SQLSTATE, full error detail, database, application name, and transaction/request ID. The deadlock report can identify the processes and lock requests involved. Since the server resolves the cycle by aborting a transaction, the live wait may be gone by the time you inspect pg_stat_activity.
For lock waits that are still active, use pg_blocking_pids() to identify blockers; see PostgreSQL Error 55P03: lock not available for a monitoring query. Compare the statements and transaction order used by each application path, including triggers and foreign-key actions that may acquire additional locks.
Prevent repeated deadlocks
Make every transaction acquire locks in a consistent order. For example, when a transfer touches two accounts, lock or update the lower account ID before the higher ID regardless of which account sends the funds. Also:
- Keep transactions short; do not wait for user input or network calls while holding database locks.
- Acquire the most restrictive lock mode the transaction will need when first locking an object, rather than upgrading later.
- Review every code path that touches the same rows or tables, including triggers and foreign-key actions.
PostgreSQL recommends consistent lock order. deadlock_timeout controls how long a lock waiter waits before the server checks for a deadlock; its default is one second. Changing it only changes detection timing, not the lock cycle. See PostgreSQL’s lock management settings.
Roll back and retry the whole transaction when safe
The deadlock victim’s transaction is aborted. Roll it back before reusing the connection; if the application continues issuing statements inside the failed transaction, they can return Error 25P02: current transaction is aborted.
An application can retry the whole transaction after a deadlock, as PostgreSQL recommends, but it should do so only when repeating the operation is safe. Keep external side effects such as sending email or publishing a message outside the transaction, or protect them with an idempotency mechanism. For other SQLSTATE guides, browse PostgreSQL Error Troubleshooting.