How to Fix "ERROR: Deadlocks Detected" in PostgreSQL Transactions

Two concurrent transactions each wait on a lock the other holds, and PostgreSQL detects the standoff and automatically kills one to break the cycle. Unlike an application bug that silently hangs, PostgreSQL's deadlock detector is designed to catch exactly this situation and fail fast with a clear, detailed error rather than letting both transactions wait forever.

The Problem

A transaction that was proceeding normally suddenly aborts with a specific, detailed error:

ERROR:  deadlock detected
DETAIL:  Process 4821 waits for ShareLock on transaction 9021834; blocked by process 4899.
Process 4899 waits for ShareLock on transaction 9021801; blocked by process 4821.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,12) in relation "accounts"

The application-level error is usually simpler, but the root cause is the same:

psycopg2.errors.DeadlockDetected: deadlock detected

Why It Happens

A deadlock happens when transaction A holds a lock that transaction B is waiting for, while transaction B simultaneously holds a different lock that transaction A is waiting for — a circular dependency that can never resolve on its own. PostgreSQL's deadlock detector periodically checks for exactly this cycle and forcibly rolls back one of the transactions (chosen to minimize disruption) to let the other proceed. Common patterns that produce this:

• Inconsistent row locking order across different code paths — one function updates rows in the order (account A, then account B), while another updates them in the reverse order, and under concurrency these can collide.
• Explicit SELECT ... FOR UPDATE statements issued in different orders across concurrent transactions touching an overlapping set of rows.
• Long-running transactions holding locks longer than necessary, widening the window during which a conflicting transaction can start and collide with it.
• Foreign key constraint checks acquiring locks implicitly in an order that isn't obvious from the application code itself, especially with cascading updates or deletes.

The Fix

Always look at the full error detail — PostgreSQL's deadlock message tells you exactly which processes and locks were involved, which is essential for actually fixing the ordering problem rather than guessing:

DETAIL:  Process 4821 waits for ShareLock on transaction 9021834; blocked by process 4899.
Process 4899 waits for ShareLock on transaction 9021801; blocked by process 4821.

Check the PostgreSQL log for the full context, including the actual queries each process was running when the deadlock occurred:

tail -50 /var/log/postgresql/postgresql-16-main.log | grep -A 10 "deadlock detected"

The durable fix is enforcing a consistent lock acquisition order everywhere in your application that touches the same set of rows or tables — always lock in the same sequence, regardless of which code path triggers it:

-- Consistent order: always lock the lower account ID first, everywhere
BEGIN;
SELECT * FROM accounts WHERE id = LEAST(5, 12) FOR UPDATE;
SELECT * FROM accounts WHERE id = GREATEST(5, 12) FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 5;
UPDATE accounts SET balance = balance + 100 WHERE id = 12;
COMMIT;

Since deadlocks are an expected, normal part of concurrent database access even with careful design, applications should always be prepared to catch and retry a transaction that failed due to one, rather than treating it as a fatal, unrecoverable error:

def transfer_with_retry(fn, max_attempts=3):
    for attempt in range(max_attempts):
        try:
            return fn()
        except psycopg2.errors.DeadlockDetected:
            if attempt == max_attempts - 1:
                raise
            time.sleep(0.1 * (attempt + 1))

Keep transactions short and avoid doing slow, non-database work (external API calls, heavy computation) while holding locks, since every extra second a lock is held widens the window for a conflicting transaction to collide with it.

Still Not Working?

If deadlocks keep happening even with consistent lock ordering, check whether missing indexes are causing PostgreSQL to lock more rows than strictly necessary for a given operation — an unindexed UPDATE ... WHERE clause can force a broader table scan and lock footprint than an indexed equivalent would:

EXPLAIN (ANALYZE, BUFFERS) UPDATE accounts SET balance = balance - 100 WHERE id = 5;

If the plan shows a sequential scan instead of an index lookup on a large table, adding the right index narrows exactly which rows get locked, which reduces both the odds and the blast radius of a future deadlock:

CREATE INDEX idx_accounts_id ON accounts (id);