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

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.

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