Overview
This page is the atomic definition. Transaction patterns and lock strategies live at postgres.
Definition
A deadlock is a circular lock-wait: transaction A holds lock L1 and waits for lock L2 held by transaction B, while transaction B holds L2 and waits for L1. Neither can proceed. PostgreSQL detects deadlocks automatically (every deadlock_timeout milliseconds, default 1 s) and terminates one of the waiting transactions with error code 40P01. The application must catch the error and retry. Prevention strategies: always acquire locks in a consistent order across transactions (lock rows in primary-key order, not random order); use SELECT ... FOR UPDATE SKIP LOCKED for queue-like patterns; keep transactions short to minimize the window for lock conflicts. optimistic-locking avoids taking locks entirely for read-modify-write operations by checking a version counter at commit time. High transaction-isolation levels (Serializable) do not increase deadlock frequency on their own, but they do increase serialization failure rates, which require similar retry logic.
When it applies
Expect deadlocks in any multi-table update that touches rows in varying order, in queue processing, and in any feature where two users can concurrently update overlapping data sets. Log deadlocks in production and add retry logic at the application layer.
Example
Transaction A: Transaction B:
UPDATE accounts SET bal=bal-50 WHERE id=1; UPDATE accounts SET bal=bal-50 WHERE id=2;
UPDATE accounts SET bal=bal+50 WHERE id=2; <- UPDATE accounts SET bal=bal+50 WHERE id=1;
-- Both block. Postgres kills one.
Fix: always update rows in ascending id order.
Related concepts
- postgres -
deadlock_timeout, error code 40P01, and lock monitoring. - transaction-isolation - higher isolation does not prevent deadlocks.
- optimistic-locking - an alternative that avoids locks entirely for low-conflict paths.
- foreign-key - FK constraint checks acquire locks that contribute to cycles.
Citing this term
See Deadlock (llmbestpractices.com/glossary/deadlock).