Chapter 20: Deadlocks — Detection, Prevention, and Recovery

A deadlock occurs when two or more transactions are waiting for each other to release locks, creating a cycle where none can proceed.

How Deadlocks Happen

Detection

PostgreSQL detects deadlocks automatically (checks every deadlock_timeout, default 1 second). It kills one transaction with:

ERROR: deadlock detected
DETAIL: Process 1234 waits for ShareLock on transaction 5678;
        blocked by process 5678.
        Process 5678 waits for ShareLock on transaction 1234;
        blocked by process 1234.

Prevention Strategies

• Always lock in the same order — if all transactions lock row 1 before row 2, no cycle can form
• Keep transactions short — less time holding locks = less chance of conflict
• Use SELECT FOR UPDATE with NOWAIT or SKIP LOCKED
• Reduce lock scope — lock only what you need

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

-- SKIP LOCKED: skip rows that are locked (great for job queues)
SELECT * FROM jobs WHERE status='pending'
ORDER BY created_at LIMIT 1 FOR UPDATE SKIP LOCKED;

Recovery

When a deadlock is detected, one transaction is aborted. Your application must retry:

// Application-level retry loop (pseudocode)
for (retries = 0; retries < 3; retries++) {
    try {
        begin_transaction();
        do_work();
        commit();
        break;  // success
    } catch (DeadlockError) {
        rollback();
        sleep(random_backoff);  // wait before retry
    }
}