A deadlock happens when two or more transactions each hold a lock the other needs, and neither can proceed. Databases don't let this hang forever — they detect it and kill one of the transactions. Understanding how that detection works, and how to avoid triggering it in the first place, is a core skill for anyone writing high-concurrency database code.
The Classic Deadlock
Two transactions, two rows, opposite order:
-- Transaction A
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- ... pauses ...
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- Transaction B (running concurrently)
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;
-- ... pauses ...
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
COMMIT;
If A locks row 1 and B locks row 2 at nearly the same time, A's second statement waits for B to release row 2, while B's second statement waits for A to release row 1. Neither can ever proceed.
Wait-For Graphs: How Databases Detect This
Most databases (PostgreSQL, MySQL/InnoDB, SQL Server) model lock waits as a directed graph: each transaction is a node, and an edge from T1 to T2 means "T1 is waiting on a lock held by T2." A deadlock exists exactly when this graph contains a cycle.
Periodically (or on every new lock wait, depending on the engine), the database walks this graph looking for cycles. When it finds one, it picks a victim — usually the transaction that has done the least work, or holds the fewest locks — and rolls it back, releasing its locks so the other transaction can continue.
What the Error Actually Looks Like
ERROR: deadlock detected
DETAIL: Process 1234 waits for ShareLock on transaction 5678; blocked by process 5678.
Process 5678 waits for ShareLock on transaction 1234; blocked by process 1234.
HINT: See server log for query details.
The application's job is to catch this specific error and retry the transaction — a deadlock victim is not a bug, it's the database correctly resolving an unavoidable conflict.
import time
def run_with_retry(fn, max_attempts=3):
for attempt in range(max_attempts):
try:
return fn()
except DeadlockDetected:
if attempt == max_attempts - 1:
raise
time.sleep(0.05 * (2 ** attempt)) # exponential backoff
Deadlocks vs. Ordinary Lock Waits
An ordinary lock wait resolves itself once the holding transaction commits or rolls back — no cycle, no error, just a delay. A deadlock is fundamentally different: without intervention, it never resolves on its own, because every party in the cycle is waiting on another party in the same cycle. This is why detection has to be active (scanning for cycles), not passive (just waiting things out).
Prevention: Consistent Lock Ordering
The single most effective prevention technique is to always acquire locks in the same order across every transaction that touches the same set of resources. If both transactions above had updated row 1 before row 2, the second one to arrive would simply wait for the first to finish — no cycle possible.
-- Both transactions now lock in ascending id order — no deadlock possible
UPDATE accounts SET balance = balance - 100 WHERE id = LEAST(1, 2);
UPDATE accounts SET balance = balance + 100 WHERE id = GREATEST(1, 2);
In practice this often means sorting rows by primary key before issuing a batch of updates, or centralizing the lock-acquisition order in a single code path rather than leaving it to each caller.
Prevention: Keep Transactions Short
Long-running transactions hold locks longer, which widens the window during which a conflicting transaction can show up and create a cycle. Moving non-essential work (sending emails, calling external APIs, heavy computation) outside the transaction boundary shrinks that window significantly.
Prevention: Lower Isolation Where Safe
Higher isolation levels take more locks for longer. SERIALIZABLE is the most deadlock-prone; READ COMMITTED takes fewer, shorter-held locks. Not every operation needs the strongest guarantee — evaluate whether a lower isolation level is safe for a given transaction before defaulting to the strictest one everywhere.
Prevention: Smaller Lock Footprint
Locking an entire table when you only need a few rows multiplies the chance of collision. Use indexed WHERE clauses so the database can take row-level (not table-level) locks, and avoid SELECT ... FOR UPDATE over more rows than the transaction actually intends to modify.
Diagnosing Recurring Deadlocks
When deadlocks show up repeatedly in production, look at the database's deadlock log (PostgreSQL logs full lock and query detail when log_lock_waits is on; MySQL exposes SHOW ENGINE INNODB STATUS). The recurring pattern is almost always the same two code paths acquiring the same two resources in opposite order — find those two call sites and fix the ordering, rather than just adding retry logic and hoping.
Common Pitfalls
Treating deadlocks purely as something to retry around, without ever fixing the underlying lock-order conflict, just moves the cost to increased latency and wasted work under load. Assuming an ORM's default query patterns are deadlock-safe is risky — many ORMs issue related updates in whatever order model associations happen to be traversed, which can vary between requests. And forgetting to add retry logic entirely means a deadlock becomes a user-facing error instead of an invisible, automatically-recovered hiccup.
A deadlock isn't a failure of the database — it's the database correctly refusing to let two transactions wait on each other forever. The fix lives in your application's lock ordering, not in disabling detection.