deadlock detected

This error occurs when two or more transactions are waiting for each other to release locks. PostgreSQL automatically detects and breaks deadlocks.

The deadlock detected error occurs when transactions create a circular wait for locks.

Understanding the Error

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

Two or more transactions are waiting for each other, creating a deadlock.

Common Causes

1. Opposite Lock Order

SQL

-- Transaction A
BEGIN;
UPDATE accounts SET balance = 100 WHERE id = 1;  -- Locks row 1
UPDATE accounts SET balance = 200 WHERE id = 2;  -- Waits for row 2

-- Transaction B (concurrent)
BEGIN;
UPDATE accounts SET balance = 300 WHERE id = 2;  -- Locks row 2
UPDATE accounts SET balance = 400 WHERE id = 1;  -- Waits for row 1 - DEADLOCK!

2. Foreign Key Locks

SQL

-- Transaction A
BEGIN;
INSERT INTO orders (user_id, total) VALUES (1, 100);  -- Locks users row 1

-- Transaction B
BEGIN;
UPDATE users SET status = 'vip' WHERE id = 1;  -- Waits for lock
INSERT INTO orders (user_id, total) VALUES (2, 200);  -- Might cause deadlock

How to Fix It

Solution 1: Consistent Lock Order

SQL

-- Always lock in same order (e.g., by ID ascending)
BEGIN;
UPDATE accounts SET balance = 100 WHERE id = 1;  -- Lower ID first
UPDATE accounts SET balance = 200 WHERE id = 2;
COMMIT;

Solution 2: Lock All Rows at Once

SQL

-- Instead of locking one by one
BEGIN;
SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
UPDATE accounts SET balance = 100 WHERE id = 1;
UPDATE accounts SET balance = 200 WHERE id = 2;
COMMIT;

Solution 3: Retry on Deadlock

JAVASCRIPT

async function withDeadlockRetry(fn, maxRetries = 3) {
  for (let attempt = 1; attempt <= maxRetries; attempt++) {
    try {
      return await fn();
    } catch (error) {
      if (error.code === '40P01' && attempt < maxRetries) {
        await sleep(Math.random() * 100);  // Random backoff
        continue;
      }
      throw error;
    }
  }
}

Solution 4: Use NOWAIT or SKIP LOCKED

SQL

-- Fail immediately instead of waiting
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;

-- Skip locked rows
SELECT * FROM tasks WHERE status = 'pending'
FOR UPDATE SKIP LOCKED
LIMIT 1;

Solution 5: Reduce Lock Duration

SQL

-- Keep transactions short
BEGIN;
-- Minimal work with locks
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

Solution 6: Use Advisory Locks

SQL

-- Explicit application-level locking
SELECT pg_advisory_xact_lock(1, 2);  -- Lock for IDs 1 and 2
-- Do your updates
-- Lock released at transaction end

Debugging Deadlocks

SQL

-- View current locks
SELECT * FROM pg_locks WHERE NOT granted;

-- View blocking queries
SELECT
  blocked.pid AS blocked_pid,
  blocked.query AS blocked_query,
  blocking.pid AS blocking_pid,
  blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks blocked_locks ON blocked.pid = blocked_locks.pid
JOIN pg_locks blocking_locks ON blocked_locks.locktype = blocking_locks.locktype
  AND blocked_locks.relation = blocking_locks.relation
JOIN pg_stat_activity blocking ON blocking_locks.pid = blocking.pid
WHERE NOT blocked_locks.granted AND blocking_locks.granted;

Best Practices

Lock in consistent order across all transactions
Keep transactions short to minimize lock time
Implement retry logic for deadlock errors
Use SKIP LOCKED for queue-like patterns
Monitor deadlock frequency in logs