ERROR: deadlock detected

The exact error string

ERROR:  deadlock detected
DETAIL:  Process 1234 waits for ShareLock on transaction 5678; blocked by process 4321.
         Process 4321 waits for ShareLock on transaction 8765; blocked by process 1234.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,7) in relation "accounts"
SQLSTATE: 40P01

What it actually looks like

Postgres detected a cycle: process 1234 waits on 4321, and 4321 waits on 1234. Neither can ever proceed, so Postgres aborts one to free the other. The aborted transaction gets the error; the survivor commits normally.

Why deadlocks happen

A deadlock needs two transactions that take the same locks in opposite order. Transaction A locks row 1 then wants row 2; transaction B locks row 2 then wants row 1. Each waits for the other forever — until Postgres steps in:

-- Transaction A                  -- Transaction B
BEGIN;                            BEGIN;
UPDATE accounts SET ... WHERE id=1;
                                  UPDATE accounts SET ... WHERE id=2;
UPDATE accounts SET ... WHERE id=2;   -- waits for B
                                  UPDATE accounts SET ... WHERE id=1;  -- waits for A
-- ❌ deadlock — Postgres aborts one

Fix 1: lock in a consistent order (the real fix)

If every transaction touches rows in the same order, the opposite-order cycle can't form. Order your updates (and SELECT ... FOR UPDATE) deterministically:

-- Both transactions update in ascending id order — no cycle possible
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- locking reads: take the locks up front, in order
SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;

Fix 2: retry the victim

Deadlocks are normal under concurrency — the right response is to retry. Catch SQLSTATE 40P01, roll back, and re-run the transaction (make it idempotent so a retry is safe):

import psycopg2, time

for attempt in range(3):
    try:
        with conn:                      # commits, or rolls back on error
            with conn.cursor() as cur:
                cur.execute("UPDATE accounts SET ... WHERE id = %s", (1,))
                cur.execute("UPDATE accounts SET ... WHERE id = %s", (2,))
        break                           # success
    except psycopg2.errors.DeadlockDetected:
        time.sleep(0.1 * (attempt + 1)) # back off, then retry

Fix 3: keep transactions short

The longer locks are held, the wider the window for a collision. Don't do slow work — HTTP calls, file I/O, waiting on user input — inside a transaction. Read what you need, write quickly, and commit. Adding the right indexes also shrinks lock scope, because Postgres locks fewer rows when it can target them precisely.

Get the conflicting statements out of the server log

The HINT: See server log for query details. line is not boilerplate, and it is the single most useful thing on the page. The client error deliberately withholds the SQL — another session's statements are not yours to see — but the server log records both sides:

2026-09-12 05:10:11.270 UTC [117] ERROR:  deadlock detected
2026-09-12 05:10:11.270 UTC [117] DETAIL:  Process 117 waits for ShareLock on transaction 734; blocked by process 116.
        Process 116 waits for ShareLock on transaction 733; blocked by process 117.
        Process 117: UPDATE accounts SET balance = balance + 10 WHERE id = 1;
        Process 116: UPDATE accounts SET balance = balance + 10 WHERE id = 2;
2026-09-12 05:10:11.270 UTC [117] CONTEXT:  while updating tuple (0,1) in relation "accounts"
2026-09-12 05:10:11.270 UTC [117] STATEMENT:  UPDATE accounts SET balance = balance + 10 WHERE id = 1;

Captured on PostgreSQL 16.15. The two Process N: lines are the whole diagnosis: they show each transaction's final statement, and reading them side by side makes the inverted lock order obvious — one transaction reached id = 1 last, the other reached id = 2 last, so each already held what the other was waiting for. On a managed service (RDS, Cloud SQL, Supabase) this is in the Postgres error log, not in your application logs.

Which transaction gets aborted — and why you can't predict it

It is natural to assume the choice is principled: the newer transaction loses, or the one holding fewer locks, or the one that has done less work. It is none of those. PostgreSQL aborts whichever backend's deadlock_timeout happens to expire first after the cycle has closed — the process that runs the next deadlock check is the one that finds the cycle, reports it, and dies. That is a timer race, and it is decided by microsecond-level arrival order you do not control.

Three runs of the same two-transaction cycle on PostgreSQL 16.15, varying only how long each transaction waited before reaching for the second row:

The first two rows look like a tidy rule — "whoever completes the cycle loses" — and the third breaks it. In row 3 A blocked first and still became the victim: the cycle closed at 2.4s, A had been waiting since 2.0s so its timer fired at 3.0s, and B's would not have fired until 3.4s. A got there first and A died. In rows 1 and 2 the two waits were five seconds apart, so the earlier waiter's timer had already fired and found nothing before the cycle existed, leaving the later one to discover it.

So neither "first in wins" nor "last in loses" is reliable. The only rule that holds is the timer one, and the gap between the two blocks relative to deadlock_timeout (1s by default) decides which case you land in.

What follows from this is the part that affects your code. You cannot designate one of the two code paths as "the one that will be retried." Either side can be the victim on any given occurrence, including the path you think of as primary or user-facing. So retry handling belongs on both transactions in the pair, not just the one that happened to lose while you were debugging — and the retry has to be safe on both sides. If only one path can tolerate replay, consistent lock ordering is not merely the better fix, it is the only one that works, because it removes the race instead of coping with its outcome.

All three rows reproduced on PostgreSQL 16.15 with the default deadlock_timeout = 1s, two UPDATEs per transaction in opposite order. Victim selection is an implementation detail of the detector, not a documented guarantee — which is precisely the reason not to build logic that assumes a particular side loses.

The settings that matter

One consequence of that first row is worth stating plainly: because detection only runs after a full second of waiting, a deadlock costs at least deadlock_timeout of latency before anything is reported. If deadlocks are frequent enough to matter, that delay is part of your p99 — another reason to fix the lock ordering rather than rely on retries alone.

The SQLSTATE is 40P01 (deadlock_detected). It belongs to class 40, transaction rollback — the same family as serialization failures — which is exactly why it is safe to retry: Postgres guarantees the aborted transaction changed nothing. Match on the code rather than the message text, since the message is localised.

Debugging checklist

• ✓ Read the DETAIL — it names the two processes and the queries that collided
• ✓ Make every transaction take locks in the same order (e.g. by ascending id)
• ✓ Catch SQLSTATE 40P01 and retry with a small backoff
• ✓ Keep transactions short; never do HTTP / slow I/O while holding locks
• ✓ Order locking reads: SELECT ... ORDER BY id FOR UPDATE
• ✓ Enable log_lock_waits to capture the colliding queries in the log

References

• Explicit Locking (PostgreSQL docs)
• PostgreSQL Error Codes (PostgreSQL docs)