December 7, 2025
When rows throw elbows
Locks in PostgreSQL
When your database locks itself, readers yell: talk advisory locks
TLDR: The article breaks down how Postgres spots deadlocks, waits briefly, and sacrifices one stuck task so others can continue, plus how timeouts differ. The loud reaction: a push to cover advisory locks, seen as the real‑world fix for multi‑machine concurrency headaches—practical tools over theory, because outages don’t care.
Today’s Postgres explainer starts calm and ends spicy. The article walks through deadlocks in simple terms: two tasks grab what the other needs, the database waits about a second (a built‑in “deadlock timeout”) before checking, and if trouble’s real, it kills one transaction so the rest can live. It also separates “lock timeout” (wait limit for a lock) from “statement timeout” (limit for the whole command), and uses a bank transfer stalemate to show how circular waits happen. Predicate locks even get a cameo.
But the headline emotion in the room? Advisory locks FOMO. One loud, clear reaction from semiquaver: “Don’t skip advisory locks,” arguing that almost every serious, multi‑machine app trips over timing bugs these locks can tame. That call-to-action steals the show, shifting the vibe from theory to “show me the fix.” It also pokes the long‑running tension: purists say deadlocks mean your app is designed wrong; pragmatists want tools that work at 3 a.m. under fire. Readers are bookmarking deadlocks and advisory locks like survival guides, cracking jokes about databases “arguing with themselves” while quietly checking their own timeouts. The mood? Urgent, slightly exasperated, and very much “give us the playbook.”
Key Points
- •Deadlocks occur when transactions cyclically wait for resources held by each other, detectable via cycles in a wait-for graph.
- •PostgreSQL checks for deadlocks after deadlock_timeout expires (default 1s) by building a wait-for graph; if no cycle, the process continues waiting.
- •lock_timeout limits how long an operator will wait for a lock, failing with lock_not_available if the time elapses; it differs from statement_timeout.
- •When a deadlock is detected, PostgreSQL aborts one transaction (typically the checker) to release locks and allow others to proceed.
- •Deadlocks are logged and reflected by increases in pg_stat_database.deadlocks; a simple example shows circular waits from inconsistent row lock ordering.