Practical guide

How to solve a database deadlock

A deadlock is not a random database error. Find the reversed lock order, fix it, and only then add a bounded retry.

25 minutes · PostgreSQL

First, the short version

Two transactions are waiting for each other

A deadlock occurs when two transactions hold different locks and each waits for the other lock. PostgreSQL detects the cycle, aborts one transaction, and returns SQLSTATE 40P01.

Rolling back the victim releases its locks, but the business operation remains incomplete. The durable fix is predictable lock ordering and a short transaction; bounded retries only protect against unavoidable concurrency.

Get ready

What you need

You need both sides of the conflict. A stack trace from only the aborted transaction is usually not enough.

A PostgreSQL log containing deadlock details, SQLSTATE, and timestamps for the affected queries.
An application log with a correlation ID, use-case name, and safely recorded record identifiers.
Access to pg_stat_activity and pg_locks for observing blocking without changing the database.
An integration scenario that can run two competing transactions against the same data.

Steps 1 to 3

Find the cycle and remove its cause

First reconstruct query order in both transactions. Then make locking consistent and add precisely scoped recovery.

1. Confirm the deadlock and reconstruct ordering

Distinguish SQLSTATE 40P01 deadlock_detected from ordinary lock waiting, a timeout, and a lost connection. Each failure needs a different response.
Find the queries and locks from both processes in the database log. Match them to specific use cases in the application log.
Write down the order of reads and writes. A typical cycle is: transaction A locks row 1 and requests 2, while transaction B locks 2 and requests 1.
Do not log sensitive parameter values. A correlation ID and internal identifiers are enough to connect the events.

SELECT pid, wait_event_type, wait_event, state, query FROM pg_stat_activity WHERE datname = current_database();

Official PostgreSQL pg_stat_activity documentation

2. Always lock data in the same order

Define one order shared by every use case, such as resource type first and primary key ascending second.
When locking multiple rows, sort the identifiers first. The SELECT FOR UPDATE query must enforce the same order.
Lock only data needed for the invariant and write immediately. HTTP calls, email, files, and long computation do not belong in the transaction.
Verify indexes used by UPDATE and SELECT FOR UPDATE conditions. Unnecessarily broad scans hold locks longer and widen the conflict window.

SELECT id FROM account WHERE id IN (:ids) ORDER BY id FOR UPDATE;

Official PostgreSQL deadlock documentation

3. Retry only recognised safe conflicts

Catch only a recognised 40P01 deadlock and optionally 40001 serialization_failure when the use case uses an isolation level that requires retries.
After rollback, create a new EntityManager and repeat the entire transaction, including every read and check. Do not continue from a middle query.
Set a small limit, such as three attempts, with short random jitter between them. Return a controlled failure and record a metric after exhaustion.
Do not retry every database exception automatically. A syntax error, constraint violation, or lost connection is not a confirmed deadlock.

SQLSTATE 40P01 / 40001 → rollback → jitter → retry the whole transaction

Official PostgreSQL list of SQLSTATE codes

Step 4

Verify the fix under concurrency

A sequential test cannot cause a deadlock. You need two real connections, controlled step interleaving, and a check of the final invariant.

Reproduce the original reversed order

Lock the same rows in opposite order through two connections and verify that the test captures SQLSTATE 40P01 before the fix.
```
php bin/phpunit --filter Deadlock
```
Run many concurrent attempts after the fix

Both use cases must follow the same order. Observe the deadlock count, wait duration, and the number of retries used.
Verify data after a retry

The operation must happen exactly once from the business perspective. Check balances, record counts, and unique keys.

If something goes wrong

Common problems

The deadlock still returns after adding ORDER BY

Another use case probably locks the same resources in a different order or already holds another lock. Compare complete transactions, not just one query.

A retry fails on a closed EntityManager

Do not reuse the original Unit of Work after rollback. Start every new attempt with a new EntityManager and load all data again.

The application retries permanent errors too

Filter by the specific SQLSTATE or a precisely mapped Doctrine exception. Limit attempts and pass every other error to the caller immediately.

An external request was sent twice after a retry

External I/O occurred inside the retried transaction. Move it after commit or use a transactional outbox and idempotent processing.

Done

Lock acquisition now has a predictable order.

PostgreSQL now receives short transactions with consistent lock ordering, and the application retries only recognised concurrency conflicts. Keep monitoring deadlock count as a production metric.

Read PostgreSQL in the glossary All guides