Deadlocks: Detection and Avoidance
Understand how deadlocks happen, how Postgres detects them, and design lock-ordering rules that prevent them.
What Is a Deadlock?
Two transactions each hold a lock the other wants — neither can proceed. The database detects the cycle and aborts one transaction.
A Classic Deadlock
Tx A locks row 1, Tx B locks row 2. A asks for row 2, B asks for row 1. Stuck.
-- Tx A:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- waiting for B...
-- Tx B:
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
-- waiting for A...
-- ERROR: deadlock detectedAll lessons in this course
- ACID Properties and Anomalies
- Isolation Levels: READ COMMITTED, REPEATABLE READ, SERIALIZABLE
- Deadlocks: Detection and Avoidance
- Optimistic vs Pessimistic Locking