Database Deadlocks Explained

Introduction¶

A deadlock is a cycle: transaction A holds lock X and wants lock Y; B holds Y and wants X. Neither can proceed. The database detects the cycle and aborts one transaction (the "victim") with a deadlock error; the app should retry it. Frequent deadlocks are an application design problem.

Detection and resolution¶

• PostgreSQL — checks for a cycle after a lock wait exceeds deadlock_timeout (default 1s). Victim gets ERROR: deadlock detected / SQLSTATE 40P01.
• MySQL/InnoDB — maintains a wait-for graph, detects immediately, rolls back the transaction that has done the least work. Error 1213 / SQLSTATE 40001. (innodb_deadlock_detect = OFF disables it in favour of innodb_lock_wait_timeout — only for extreme-concurrency workloads.)

The victim's transaction is fully rolled back. Your code must catch the deadlock error and retry (with a little backoff, a few times).

Reading the log¶

PostgreSQL¶

ERROR:  deadlock detected
DETAIL:  Process 123 waits for ShareLock on transaction 456; blocked by process 789.
         Process 789 waits for ShareLock on transaction 455; blocked by process 123.
         Process 123: UPDATE accounts SET balance = balance - 100 WHERE id = 2;
         Process 789: UPDATE accounts SET balance = balance + 100 WHERE id = 1;

The two Process N: queries are the ones that collided — and they show the lock order problem: one updates id 2 then id 1, the other id 1 then id 2.

MySQL/InnoDB¶

SHOW ENGINE INNODB STATUS\G     -- the "LATEST DETECTED DEADLOCK" section

Shows both transactions, the locks each holds and waits for, the index and lock mode (X, S, gap, rec but not gap), and the SQL. Enable innodb_print_all_deadlocks = ON to log every deadlock, not just the last.

Common causes and fixes¶

Inconsistent lock ordering (the big one)¶

Two code paths lock rows/tables in different orders. Fix: always acquire locks in the same order — e.g. always update accounts by ascending id.

-- transfer: lock the lower id first, always
BEGIN;
SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Lock escalation / whole-table scans under a lock¶

An UPDATE ... WHERE non_indexed_column = x locks every row it scans. Add the index so only the matching rows are locked.

InnoDB gap locks (REPEATABLE READ)¶

INSERTs and range UPDATEs take gap locks to prevent phantoms. Two sessions inserting into the same gap, or an insert vs a range delete, deadlock. Options: READ COMMITTED isolation (no gap locks, if your app tolerates it), insert in consistent key order, or use INSERT ... ON DUPLICATE KEY / upserts carefully.

Foreign key checks¶

Inserting a child row takes a shared lock on the parent. Concurrent inserts referencing parents + a parent update can cycle. Keep parent updates short; consider ON DELETE/ON UPDATE implications.

Long transactions¶

The longer a transaction holds locks, the wider the window for a cycle. Keep transactions short; don't do HTTP calls or user think-time inside one.

Verification and troubleshooting¶

-- Postgres: current lock waits
SELECT blocked.pid AS blocked_pid, blocking.pid AS blocking_pid,
       blocked.query AS blocked_query, blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid));

-- MySQL
SELECT * FROM performance_schema.data_lock_waits;
SELECT * FROM sys.innodb_lock_waits;

• Deadlocks under load, gone when serial — expected; the question is whether the rate is acceptable with retries. If it's high, fix lock ordering.
• Same two statements always in the log — you've found it. Reorder the locking so both paths take locks in the same sequence.
• Deadlock on INSERT only (InnoDB) — gap locks under REPEATABLE READ. Consider READ COMMITTED, or make the insert order deterministic, or use a dedicated sequence/table for ID allocation.
• App doesn't retry — a deadlock is a transient error. Wrap the transaction in a retry loop (3–5 attempts, small jittered backoff); make it idempotent.
• Lock wait timeout exceeded instead of deadlock — not a cycle, just a long lock hold. Find the blocker (sys.innodb_lock_waits / pg_blocking_pids), shorten it, or raise innodb_lock_wait_timeout if the wait is legitimate.
• Deadlocks after adding a trigger / FK — the trigger/constraint takes extra locks. Review what it touches and in what order.

Related tools and reading¶

• On-site: SQL / JSON formatter.
• Related posts: PostgreSQL autovacuum tuning, Connection pooling explained.