PostgreSQL "deadlock detected": What It Means and How to Stop It

Sooner or later every busy application logs it: ERROR: deadlock detected. A PostgreSQL deadlock is not a bug in the database and it is not a sign the server is overloaded. It means two (or more) transactions were each holding a lock the other one needed, and neither could ever finish. PostgreSQL noticed and cancelled one of them. The good news: deadlocks are almost always caused by a handful of patterns in application code, and once you know them, they are fixable.

What actually happens

The PostgreSQL documentation uses a classic example. Transaction A updates account 11111. Transaction B updates account 22222 and then tries to update 11111, so it waits for A. Now A tries to update 22222 and waits for B. Each one is waiting for the other. Without intervention they would wait forever.

PostgreSQL doesn't check for this on every lock wait, because the check is expensive. A session that has waited for a lock longer than deadlock_timeout (default 1 second) runs the deadlock check. If it finds a cycle, it aborts one of the transactions with SQLSTATE 40P01 (deadlock_detected) and the others continue. Which transaction gets cancelled is, in the docs' words, "difficult to predict and should not be relied upon."

Two practical consequences: the cancelled transaction is rolled back entirely, so your application must deal with it; and every deadlock costs at least a second of waiting for the sessions involved before it is resolved.

How to read the log message

The error in the server log comes with details that tell you exactly who was waiting for whom. It looks roughly like this:

ERROR:  deadlock detected
DETAIL:  Process 4121 waits for ShareLock on transaction 9182736; blocked by process 4188.
        Process 4188 waits for ShareLock on transaction 9182730; blocked by process 4121.
        Process 4121: UPDATE accounts SET balance = balance - 100 WHERE acctnum = 22222
        Process 4188: UPDATE accounts SET balance = balance - 100 WHERE acctnum = 11111
HINT:  See server log for query details.
CONTEXT:  while updating tuple (12,7) in relation "accounts"

The important parts are the two statements and the relation name. "ShareLock on transaction" is how PostgreSQL waits for a row lock held by another transaction, so this is a row-level deadlock between two UPDATEs. Remember that the log shows only the last statement of each transaction. The lock that caused the problem was usually taken by an earlier statement in the same transaction, so you will often need the application code, not just the log, to see the full sequence.

The patterns behind most deadlocks

1. Same rows, different order

This is the example from the docs, and it is by far the most common one. Two code paths update the same set of rows, but in different orders: a transfer from A to B and one from B to A, or a batch job that walks through orders by date while the web app updates them by customer.

Fix: always lock rows in a consistent order. If a transaction will touch several rows, sort them first (for example by primary key) and update them in that order. For batch updates, add an ORDER BY to a SELECT ... FOR UPDATE that takes the locks up front.

2. Lock upgrade within a transaction

A transaction reads a row with SELECT ... FOR SHARE and later updates it. Two transactions do this at the same time: both get the shared lock, both then wait for the other to release it before they can update. The docs' advice applies directly: the first lock taken on an object should be "the most restrictive mode that will be needed".

Fix: if you intend to update a row, lock it with SELECT ... FOR UPDATE (or FOR NO KEY UPDATE if you don't change key columns) from the start.

3. Foreign keys you forgot about

Inserting or updating a row that references another table takes a shared lock on the referenced parent row, so the parent can't be deleted or have its key changed while the check runs. Many inserts of child rows plus an update of the parent in a different order can deadlock even though the application code never mentions locks at all. A typical case: inserting order lines and then updating the order total, in two concurrent transactions for the same order.

Fix: lock the parent first (update the order row, then insert the lines), and keep these transactions short.

4. Big batch updates running alongside live traffic

A single UPDATE touching 200,000 rows takes its row locks one by one in whatever order the plan visits them. Meanwhile the application updates individual rows from the same set. The longer the batch runs, the more chances for a cycle.

Fix: process batches in small chunks, in a deterministic order, each in its own short transaction. This also helps replication and vacuum.

5. Explicit table locks and DDL

Code that runs LOCK TABLE on several tables, or migrations that alter tables in a different order from what the application touches, can deadlock at the table level. Schema changes are a frequent source of nasty lock queues even without a deadlock; we cover safe ways to run them in zero-downtime PostgreSQL migrations.

Monitoring deadlocks and lock waits

• Count them. pg_stat_database has a deadlocks column per database. Graph the rate: a sudden jump right after a deploy points straight at the change.
• Turn on log_lock_waits. It is off by default (logging settings). When on, PostgreSQL logs every lock wait longer than deadlock_timeout. That shows the "almost deadlocks" and long lock queues that hurt latency even when nothing gets cancelled.
• See who is blocking whom right now:

SELECT pid, pg_blocking_pids(pid) AS blocked_by,
       now() - xact_start AS xact_age, state, left(query, 80) AS query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

• Protect against long waits. lock_timeout on a session or a role makes a statement give up instead of waiting forever behind a lock; it is especially useful for migrations. idle_in_transaction_session_timeout stops forgotten transactions from holding locks for hours.

About deadlock_timeout itself: the docs call 1 second "probably about the smallest value you would want in practice" and suggest raising it on heavily loaded servers (lock management settings). Lowering it doesn't prevent deadlocks, it only reports them sooner at the cost of more checks.

A short checklist when deadlocks start appearing

1. Take the statements and relation names from the log.
2. Find the transactions in the code that run them, and list all statements in each, in order.
3. Look for different row orders, shared-then-exclusive locks, foreign keys and batch jobs touching the same rows.
4. Fix the order or the lock mode; shorten the transactions.
5. Add a retry for 40P01 in the data access layer.
6. Watch the deadlocks counter to confirm the fix.

Locking problems often hide behind "the database is slow". If your slow queries are really waiting rather than working, our PostgreSQL slow query tuning checklist shows how to tell the difference.