What a Deadlock Actually Is
A deadlock happens when two or more transactions each hold a lock the other needs, forming a cycle with no possible progress: transaction A holds a lock on row 1 and waits for row 2, while transaction B holds a lock on row 2 and waits for row 1. PostgreSQL runs a deadlock detector that periodically (governed by the deadlock_timeout setting, default 1 second) checks the wait-for graph for cycles; when it finds one, it doesn't wait forever — it picks one of the transactions in the cycle (typically the one that would be cheapest to abort) and terminates it with an ERROR: deadlock detected, releasing its locks so the other transaction(s) can proceed.
Cricket analogy: A deadlock is like two batters both calling for the same run and ending up stranded mid-pitch, each waiting for the other to move first — the umpire (deadlock detector) eventually intervenes and rules one of them out to unstick the situation.
Preventing Deadlocks in Application Code
The most reliable prevention technique is consistent lock ordering: if every transaction that needs to touch both row 1 and row 2 always acquires them in the same order (say, always by ascending primary key), the circular wait pattern can never form in the first place. Other practical mitigations include keeping transactions short so locks are held for the shortest possible window, using SELECT ... FOR UPDATE explicitly and early rather than relying on an UPDATE deep inside a long transaction, and setting a reasonable statement_timeout or lock_timeout so a stuck transaction fails fast instead of holding locks indefinitely while waiting on something outside the database (like a slow external API call inside a transaction).
Cricket analogy: Consistent lock ordering is like a fielding drill rule where players always call for the ball in a fixed priority order (wicketkeeper before mid-on before point) — eliminating the confusion that causes two fielders to collide going for the same catch.
-- BAD: two transactions can deadlock if run concurrently in opposite order
-- Session A:
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
UPDATE accounts SET balance = balance + 50 WHERE id = 2; -- waits if B holds id=2
-- Session B (started around the same time):
BEGIN;
UPDATE accounts SET balance = balance - 20 WHERE id = 2;
UPDATE accounts SET balance = balance + 20 WHERE id = 1; -- waits if A holds id=1
-- => circular wait => deadlock detected, one session gets:
-- ERROR: deadlock detected
-- DETAIL: Process 1234 waits for ShareLock on transaction 5678; blocked by process 5678.
-- GOOD: always touch accounts in ascending id order in every transaction
UPDATE accounts SET balance = balance - 50 WHERE id = LEAST(1,2);
UPDATE accounts SET balance = balance + 50 WHERE id = GREATEST(1,2);PostgreSQL's deadlock detector logs full diagnostic detail — including the exact PIDs, lock modes, and queries involved — to the server log whenever a deadlock is broken, which is the first place to look when debugging recurring deadlocks.
Deadlock detection itself has overhead: PostgreSQL waits deadlock_timeout (1 second by default) before even checking for a cycle, so a real deadlock in a busy application can appear as a full second of stalled transactions before the error surfaces. Lowering deadlock_timeout speeds detection but increases the background cost of the cycle-check on every lock wait.
- A deadlock is a circular wait where each involved transaction holds a lock another one needs.
- PostgreSQL's deadlock detector runs after deadlock_timeout (default 1s) and breaks cycles by aborting one transaction.
- The aborted transaction receives 'ERROR: deadlock detected' and must be retried by the application.
- Consistent lock ordering across all transactions is the most reliable deadlock prevention technique.
- Keeping transactions short and acquiring locks early (explicit FOR UPDATE) reduces deadlock windows.
- statement_timeout and lock_timeout prevent stuck transactions from holding locks indefinitely.
- Server logs contain full diagnostic detail (PIDs, queries, lock modes) for every broken deadlock.
Practice what you learned
1. What condition defines a deadlock in PostgreSQL?
2. What does PostgreSQL do when it detects a deadlock?
3. What is the most reliable way to prevent deadlocks caused by two transactions locking the same two rows in opposite order?
4. What setting controls how long PostgreSQL waits before checking for a deadlock cycle?
5. Where can you find detailed diagnostic information about a resolved deadlock?
Was this page helpful?
You May Also Like
Locking in PostgreSQL
The row-level and table-level lock modes PostgreSQL uses to coordinate concurrent writers, and how to inspect and reason about lock contention.
ACID and Isolation Levels
How PostgreSQL guarantees Atomicity, Consistency, Isolation, and Durability, and how the four SQL isolation levels trade off correctness against concurrency.
MVCC Explained
How PostgreSQL's Multi-Version Concurrency Control lets readers and writers avoid blocking each other by keeping multiple row versions and using visibility rules.