A deadlock happens when two or more transactions each hold a lock the other needs, so none can proceed — a circular wait. Transaction A holds row 1 and wants row 2; transaction B holds row 2 and wants row 1. Neither will release until it acquires the other's lock, so they would wait forever. PostgreSQL detects this situation and breaks it by aborting one transaction.
Deadlocks are a normal hazard of concurrent systems with locking, not a sign of a broken database. The previous lessons explained how transactions take locks (explicitly via FOR UPDATE, or implicitly when updating rows); deadlocks arise when concurrent transactions acquire those locks in conflicting orders. The key skills are understanding why they occur, preventing most of them by design, and handling the rest gracefully.
This lesson covers how PostgreSQL detects deadlocks automatically, the single most effective prevention technique (consistent lock ordering), and how applications should respond — by catching the deadlock error and retrying. A system that prevents most deadlocks and retries the rest is robust under heavy concurrency.
Analogy🏏Cricket
🏏 Think of it like cricket: Just as a selection decision can depend on a derived benchmark — 'pick batters whose average exceeds the squad's average', which itself must first be computed — a subquery computes an inner result that the outer query then uses. The insight is that some questions are inherently two-stage: you must establish the benchmark before you can judge against it, and composing queries is how SQL expresses that dependency. Watch the selector actually do it: first he tallies every batter's runs and computes the squad average — that inner computation stands alone, needing nothing from the final decision — and only then does he walk the list judging each player against the number he just derived. That independence is what makes it an uncorrelated subquery: the database can compute the benchmark once, keep it, and reuse it for every row, exactly as the selector does not recompute the squad average per player. The composition also comes in shapes: a benchmark producing one number slots in where a value goes (a scalar subquery in WHERE), while a computed shortlist of qualifying players is itself a table the outer query can select from — a subquery in FROM. Two-stage question, two nested queries, dependency flowing inward-out.
🏏 Showing the Cricket analogy — a Cricket version isn’t available for this concept yet.