Deadlocks: How They Happen

~12 min · transactions, deadlocks

Level 0Schema Seedling

0 XP0/86 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

Two transactions, opposite order

A deadlock happens when transaction A holds a lock on row X and waits for row Y, while transaction B holds Y and waits for X. Neither can proceed. PostgreSQL detects the cycle and aborts one of them (40P01 deadlock_detected error).

The deadlock recipe

Two updates, two rows, accessed in opposite orders. Almost every deadlock in real systems is some variation of this pattern.

Two preventions

Always update rows in the same order across all code paths. If you always touch row 1 then row 2, you can never deadlock with another transaction doing the same.
Catch and retry. Deadlocks are sometimes unavoidable in real workloads. Wrap your transaction in a retry loop (with backoff) and treat 40P01 as a recoverable error.

Code

Deadlock recipe — DO NOT do this·sql

-- Session A                        | -- Session B
BEGIN;                              | BEGIN;
UPDATE accounts SET balance =       | UPDATE accounts SET balance =
  balance - 100 WHERE id = 1;       |   balance - 100 WHERE id = 2;
                                    |
-- A waits for B's lock on 2        | -- B waits for A's lock on 1
UPDATE accounts SET balance =       | UPDATE accounts SET balance =
  balance + 100 WHERE id = 2;       |   balance + 100 WHERE id = 1;
-- ERROR: deadlock detected         |

Prevention: consistent ordering·sql

-- Always lock the lower-id row first
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = LEAST(1, 2);
UPDATE accounts SET balance = balance + 100 WHERE id = GREATEST(1, 2);
COMMIT;

Retry loop in Python·python

import psycopg, time
for attempt in range(5):
    try:
        with conn.transaction():
            conn.execute("UPDATE accounts SET balance = balance - 100 WHERE id = %s", (from_id,))
            conn.execute("UPDATE accounts SET balance = balance + 100 WHERE id = %s", (to_id,))
        break
    except psycopg.errors.DeadlockDetected:
        time.sleep(0.05 * (2 ** attempt))  # exponential backoff
else:
    raise RuntimeError("deadlock-retry exhausted")

External links

PostgreSQL Deadlocks

Exercise

Reproduce the deadlock recipe in two psql sessions. Observe the deadlock_detected error. Then rewrite the two transactions to lock rows in a consistent order; verify deadlocks no longer happen.