Fix PostgreSQL 'ERROR: deadlock detected' in Transactions

The Error

You'll see something like this in your logs or application output:

ERROR: deadlock detected
DETAIL: Process 12345 waits for ShareLock on transaction 7890; blocked by process 67890.
        Process 67890 waits for ShareLock on transaction 12345; blocked by process 12345.
HINT: See server log for query details.

PostgreSQL killed one transaction (the "victim") to break the cycle. The survivor commits normally. The victim gets rolled back — and your application receives this error instead of a result.

What Causes a Deadlock

Classic scenario: two transactions lock the same rows in opposite order.

-- Transaction A             -- Transaction B
BEGIN;                       BEGIN;
UPDATE accounts              UPDATE accounts
  SET balance = balance - 50   SET balance = balance - 30
  WHERE id = 1;                WHERE id = 2;  -- locks row 2
                             -- now tries row 1 → blocked by A
UPDATE accounts
  SET balance = balance + 30
  WHERE id = 2;  -- tries row 2 → blocked by B
-- DEADLOCK

Neither transaction can move forward. PostgreSQL detects the cycle after deadlock_timeout (1 second by default), picks one victim, and terminates it.

Quick Fix: Retry the Failed Transaction

PostgreSQL already rolled back the victim. You just need to re-run it. Add a retry loop — three attempts with a short backoff covers almost all real-world cases.

Python (psycopg2)

import psycopg2
from psycopg2 import errors
import time

def run_with_retry(conn, fn, max_retries=3):
    for attempt in range(max_retries):
        try:
            with conn.cursor() as cur:
                fn(cur)
            conn.commit()
            return
        except errors.DeadlockDetected:
            conn.rollback()
            if attempt  setTimeout(r, 100 * (attempt + 1)));
      } else {
        throw err;
      }
    }
  }
}

Permanent Fix: Lock Rows in a Consistent Order

Retrying fixes the symptom. To stop deadlocks from happening in the first place, always acquire locks in the same order — sort by primary key before touching any rows.

-- Bad: Transaction A locks id=1 then id=2
--      Transaction B locks id=2 then id=1

-- Good: lock both rows upfront, in ascending id order
BEGIN;
SELECT * FROM accounts
  WHERE id IN (1, 2)
  ORDER BY id
  FOR UPDATE;  -- locks row 1, then row 2 — always

UPDATE accounts SET balance = balance - 50 WHERE id = 1;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;
COMMIT;

SELECT ... FOR UPDATE ORDER BY id grabs all needed locks in one shot, in a predictable sequence. Two transactions following this pattern will never deadlock each other — one simply waits while the other finishes.

Keep Transactions Short

Long-running transactions hold locks longer, which raises the chance of collisions. A few simple rules cut deadlock frequency significantly:

• Do all computation before opening the transaction — not inside it.
• Never make network calls or wait on user input inside a transaction.
• For job queues, use FOR UPDATE SKIP LOCKED so workers skip rows already claimed by others.

-- Job queue: grab one pending row without blocking other workers
SELECT id, payload FROM jobs
  WHERE status = 'pending'
  ORDER BY id
  LIMIT 1
  FOR UPDATE SKIP LOCKED;

Find Out Which Queries Are Deadlocking

Before rewriting anything, confirm where the deadlocks actually originate. Two approaches:

Option 1 — Enable logging (persists across restarts, good for production monitoring):

-- postgresql.conf or ALTER SYSTEM SET
deadlock_timeout = 200ms     -- default is 1s; lower to catch more
log_lock_waits = on
log_min_duration_statement = 1000  -- log queries slower than 1s

Option 2 — Query live (no restart needed, shows what's blocked right now):

SELECT
  blocked.pid,
  blocked.query  AS blocked_query,
  blocking.pid   AS blocking_pid,
  blocking.query AS blocking_query
FROM pg_stat_activity AS blocked
JOIN pg_stat_activity AS blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;

Verify the Fix

• Reproduce it first: run two concurrent transactions in the old order and confirm you get ERROR: deadlock detected.
• Apply the fix — consistent lock ordering or FOR UPDATE upfront.
• Re-run the test: no deadlock error this time.
• Watch production logs: after deploying, grep for deadlock detected. If it stops appearing, you've addressed the root cause.

-- Smoke test: open two psql tabs and run these simultaneously

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

-- Tab 2 (same lock order = no deadlock):
BEGIN;
SELECT * FROM accounts WHERE id IN (1,2) ORDER BY id FOR UPDATE;
UPDATE accounts SET balance = balance + 30 WHERE id = 1;
UPDATE accounts SET balance = balance - 30 WHERE id = 2;
COMMIT;

Tab 2 pauses briefly while Tab 1 holds the locks, then continues normally. No errors, no victims — just the expected wait-and-proceed behavior.