PostgreSQL "deadlock detected": read the message, then lock in the same order

1. How it happens

Two transfers run at the same moment, between the same two accounts, in opposite directions:

Neither will ever let go. After deadlock_timeout, one second by default, PostgreSQL checks for such a circle, finds it, and cancels one side.

2. Read the message

DETAIL names the two processes, and CONTEXT names the table and the row: tuple (0,2) in accounts. The server’s log has the full statements, as the HINT says:

sudo grep -A 8 'deadlock detected' /var/log/postgresql/postgresql-18-main.log | tail -n 20

On RHEL, the log is under /var/lib/pgsql/18/data/log/, and in Docker, it’s in docker logs. Look for two statements on the same table, in different orders, from two places in your code.

3. Lock in the same order

If both transfers lock the lower id first, the second one simply waits, and no circle forms:

BEGIN;
SELECT id FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

SELECT ... ORDER BY id FOR UPDATE takes all the locks first, in a fixed order. The same rule applies to batch updates: an UPDATE with no order locks rows in whatever order it finds them, and two such batches can deadlock.

4. Other common causes

Long transactions. The longer you hold locks, the more chances of a collision. Don’t call external services, or wait for a user, inside a transaction.

Foreign keys. Inserting a child row briefly locks its parent row in a shared mode. Two transactions each inserting children and then updating the other’s parent can deadlock. Order the work the same way, or update the parent first.

No index on a foreign key column. Deleting or updating a parent then has to scan the child table, which makes these lock waits longer, and collisions more likely. Index the referencing columns.

5. Retry in the application

Even with careful ordering, deadlocks can happen under load, and retrying is the expected way to handle them. The error has its own code, 40P01:

from psycopg import errors

for attempt in range(3):
    try:
        with conn.transaction():
            transfer(conn, from_id, to_id, amount)
        break
    except errors.DeadlockDetected:
        if attempt == 2:
            raise

Retry the whole transaction, not only the last statement, since all of it was rolled back.

6. See lock waits as they happen

Turn on logging of slow lock waits, which needs only a reload:

ALTER SYSTEM SET log_lock_waits = on;
SELECT pg_reload_conf();

Any wait longer than deadlock_timeout then goes to the log, with the statements. To see who blocks whom right now:

SELECT pid, pg_blocking_pids(pid) AS blocked_by, state, wait_event_type, query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

A blocker in the state idle in transaction is a transaction someone didn’t commit. Setting idle_in_transaction_session_timeout ends those automatically.