How to Fix PostgreSQL `deadlock detected` Errors (SQLSTATE 40P01)

A PostgreSQL deadlock detected error means two or more transactions waited on each other in a cycle, so PostgreSQL canceled one of them to let the others continue. The usual SQLSTATE is 40P01. The canceled transaction did not partially succeed; it was aborted and must be rolled back before the application can do more work on that connection.

The quick fix is not to raise every timeout or immediately add more database capacity. A deadlock is usually a lock-ordering problem in application code, jobs, or migrations. Retry can make the user experience smoother, but the durable fix is to make concurrent transactions take locks in a consistent order and keep those transactions short.

First confirm it is a deadlock

Start with the exact error and SQLSTATE from the application log, driver exception, or PostgreSQL log. PostgreSQL documents deadlock_detected as SQLSTATE 40P01, which is different from a serialization failure, a lock timeout, or a statement timeout. Those failures can all appear during concurrent writes, but they point to different fixes.

A deadlock has a cycle: transaction A holds a lock transaction B needs, while transaction B holds a lock transaction A needs. PostgreSQL waits for deadlock_timeout before running deadlock detection, then aborts one participant. The default is intentionally not instant, because checking for deadlocks has overhead and many lock waits resolve naturally.

That distinction matters because a blind retry loop around every database error can hide a lock-order bug. Retrying 40P01 is often reasonable for a short idempotent transaction, but repeated deadlocks should be treated as a schema or workflow defect.

Find the two code paths

In self-hosted PostgreSQL, enable enough logging to capture the deadlock details, then inspect the blocked statements. PostgreSQL's deadlock report normally includes the waiting process, lock type, relation or tuple involved, and the statements participating in the cycle. In managed PostgreSQL, the same information is usually available through database logs in the provider dashboard.

If the incident is still happening, query pg_stat_activity and pg_locks to see who is waiting and who is blocking. A simple investigation starts by looking for sessions where wait_event_type = 'Lock', then joining lock information to the active query text. The goal is not to memorize every lock mode. The goal is to identify the two application paths that touch the same rows or tables in opposite orders.

For example, one request might update an account row and then insert an invoice row, while a background job updates invoice rows and then touches the account summary. Each path is individually reasonable. Together, under concurrency, they can create a cycle. The fix is to choose one order and make both paths follow it.

Apply the fix in order

First, make transaction boundaries obvious. A transaction that performs API calls, renders templates, sleeps, or waits on a queue while holding row locks is much more likely to deadlock. Move non-database work outside the transaction and commit as soon as the database state is consistent.

Second, standardize lock order. When multiple tables or rows must be updated together, touch them in the same order everywhere. If a job processes many rows, order them by a stable key before locking or updating. If the application sometimes updates parent then child and elsewhere child then parent, pick one direction and enforce it in code review.

Third, lock deliberately when the natural query order is not enough. In some workflows, a small SELECT ... FOR UPDATE on the parent row at the start of the transaction is clearer than letting several later updates acquire locks in surprising order. Do not add broad table locks as a reflex; they reduce concurrency and can create new bottlenecks. Prefer narrow row locks around the state that truly coordinates the workflow.

Fourth, add a bounded retry around safe units of work. The retry should wrap the whole transaction, not the single failed statement, because PostgreSQL aborts the transaction after the error. Use a small randomized backoff, cap the number of attempts, and only retry operations that can run again without duplicating external side effects such as emails, webhooks, or charges.

What changes with pooling and managed PostgreSQL

Connection pooling can make deadlocks more visible because more workers can reach the database efficiently. That is usually good, but it means pool size is part of the concurrency design. If a deployment scales from a handful of writers to dozens of simultaneous write transactions, lock-order problems that were rare in staging can become common in production.

PgBouncer does not change PostgreSQL's lock rules. It changes how many application tasks can compete for them. For ArmorDB users, PgBouncer is included, so the practical move is to pair pooling with short transactions, consistent write order, and application-level retries. If you are also tuning pool size, the ArmorDB guide to PgBouncer connection pooling is useful context.

A safe retry pattern

A good retry handler checks the SQLSTATE, rolls back the failed transaction, waits briefly, and reruns the whole transaction from the beginning. Keep the retry count low. If a deadlock happens repeatedly, the retry is buying time for users while you fix the conflict pattern.

The most common mistake is retrying after doing irreversible work outside the database. If the transaction sends a welcome email and then deadlocks while writing an audit row, a retry can send duplicate emails. Put external side effects after commit, or store them in an outbox table that is processed after the transaction succeeds.

Takeaway

PostgreSQL cancels one participant in a deadlock to protect the rest of the system. Treat 40P01 as both a recoverable transaction failure and a design signal. Roll back, retry only safe transactions, then remove the cycle by shortening transactions and enforcing a consistent lock order. That fixes the production symptom without turning concurrency into a guessing game.

Sources and further reading

• PostgreSQL documentation on explicit locking and deadlocks: https://www.postgresql.org/docs/current/explicit-locking.html
• PostgreSQL documentation on monitoring locks: https://www.postgresql.org/docs/current/monitoring-locks.html
• PostgreSQL error codes appendix for 40P01 deadlock_detected: https://www.postgresql.org/docs/current/errcodes-appendix.html
• PostgreSQL lock management settings, including deadlock_timeout: https://www.postgresql.org/docs/current/runtime-config-locks.html