📋

Description

The 'deadlock detected' error (40P01) indicates that two or more database transactions are waiting for each other to release locks on resources, forming a circular dependency. PostgreSQL's deadlock detector identifies this situation and aborts one of the transactions (the 'victim') to break the cycle, resulting in a transaction rollback for the victim.

💬

Error Message

deadlock detected

🔍

Known Causes

4 known causes

⚠️

Inconsistent Lock Order

Transactions attempt to acquire locks on the same set of resources but in different sequences, leading to a circular wait condition.

⚠️

High Concurrency on Shared Resources

Numerous concurrent transactions frequently access and modify the same rows or tables, increasing the likelihood of lock contention and deadlocks.

⚠️

Long-Running Transactions

Transactions that hold locks for an extended duration increase the window for other transactions to become blocked, thus raising the potential for deadlocks.

⚠️

Missing or Inefficient Indexes

Lack of appropriate indexes can force transactions to scan larger portions of tables, acquiring more locks than necessary and increasing contention.

🛠️

Solutions

5 solutions available

1. Retry the Transaction easy

Deadlock victim can succeed on retry

1

Implement retry logic

import psycopg2
import time

def execute_with_retry(conn, queries, max_retries=3):
    for attempt in range(max_retries):
        try:
            with conn.cursor() as cur:
                for query, params in queries:
                    cur.execute(query, params)
            conn.commit()
            return True
        except psycopg2.errors.DeadlockDetected:
            conn.rollback()
            time.sleep(0.1 * (attempt + 1))
    raise Exception('Max retries exceeded')

2. Access Tables in Consistent Order medium

Prevent deadlocks with ordered access

1

Always access tables in same order

-- Transaction 1 and 2 should both do:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- Lower ID first
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- NOT:
-- Transaction 1: update id=1, then id=2
-- Transaction 2: update id=2, then id=1  -- Causes deadlock!

3. Use Lock Timeout medium

Fail fast instead of waiting indefinitely

1

Set lock timeout

SET lock_timeout = '10s';  -- Wait max 10 seconds for lock

2

Set for specific transaction

BEGIN;
SET LOCAL lock_timeout = '5s';
-- Your queries
COMMIT;

4. Reduce Lock Scope medium

Lock fewer rows for shorter time

1

Be specific in WHERE clause

-- Bad: Locks many rows
UPDATE accounts SET status = 'active' WHERE region = 'US';

-- Good: Lock specific rows
UPDATE accounts SET status = 'active' WHERE id IN (1, 2, 3);

2

Add indexes to reduce lock scan

-- Without index, UPDATE scans (and locks) more rows
CREATE INDEX idx_accounts_id ON accounts(id);

5. Investigate Deadlock Cause advanced

Find what's causing deadlocks

1

Enable deadlock logging

-- In postgresql.conf:
log_lock_waits = on
deadlock_timeout = 1s

2

Check PostgreSQL logs for deadlock details

-- Log shows which queries were involved:
-- Process 12345 waits for ShareLock on transaction 1234
-- Process 12346 waits for ShareLock on transaction 1235
-- Process 12345 holds ExclusiveLock on transaction 1235
-- etc.

🔗

Related Errors

5 related errors

PostgreSQL Error 40P01: Deadlock Detected, Transaction Rollback