Overview
PostgreSQL provides row-level lock modes of increasing strength, from FOR KEY SHARE (weakest) through FOR SHARE, FOR NO KEY UPDATE, to FOR UPDATE (strongest, fully exclusive), and choosing the weakest mode that actually satisfies what a transaction needs is what maximizes real concurrency. Deadlocks happen when two or more transactions form a circular wait, each holding a lock the other needs, and PostgreSQL detects this automatically and aborts one of the transactions to break the cycle, but explicitly does not guarantee which one, applications must be ready to retry either side. The durable fix for recurring deadlocks isn't retry logic alone, it's acquiring locks on shared resources in the same order everywhere in the application, which makes the circular-wait pattern structurally impossible rather than merely less likely.
Quick Reference
| Lock mode | Strength | Blocks |
|---|---|---|
FOR KEY SHARE | Weakest | Only DELETE/key-changing UPDATE |
FOR SHARE | Shared | UPDATE, DELETE, and stronger locks; permits other FOR SHARE/FOR KEY SHARE |
FOR NO KEY UPDATE | Exclusive-ish | Most, but not FOR KEY SHARE |
FOR UPDATE | Strongest | Everything else attempting to lock the same rows |
Syntax
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;Examples
-- Deadlock: opposite lock order on the same two rows across
-- two concurrent transactions.
-- Transaction 1:
BEGIN;
UPDATE accounts SET balance = balance + 100 WHERE acctnum = 1;
UPDATE accounts SET balance = balance - 100 WHERE acctnum = 2;
COMMIT;
-- Transaction 2 (concurrently):
BEGIN;
UPDATE accounts SET balance = balance + 100 WHERE acctnum = 2;
UPDATE accounts SET balance = balance - 100 WHERE acctnum = 1;
COMMIT;
-- One of these two transactions gets aborted by deadlock detection.You cannot predict which transaction a deadlock aborts
PostgreSQL's own documentation is explicit that the choice is difficult to predict and shouldn't be relied upon. Any code path that can deadlock needs retry logic on both sides, not just the transaction you'd expect to "lose."
Visual Diagram
Common Mistakes
- Defaulting to
FOR UPDATEeverywhere out of caution, whenFOR SHAREorFOR KEY SHAREwould satisfy the actual requirement with far less blocking. - Assuming a deadlock will always abort the "same" transaction (the smaller one, the one that started waiting first), and building logic that depends on that assumption.
- Treating a recurring deadlock as fixed by adding retry logic alone, without addressing the underlying inconsistent lock ordering that keeps causing it.
- Locking rows in whatever order a query happens to touch them, rather than establishing and following a consistent ordering convention across the whole application.
Performance
- Weaker lock modes (
FOR KEY SHARE,FOR SHARE) allow meaningfully more concurrency than defaulting toFOR UPDATE, which serializes access more than the operation actually requires. - Deadlock detection itself has a real, if usually small, cost, and every deadlock forces a full transaction abort and retry, real wasted work, not just a logged warning.
Best Practices
- Choose the weakest row-lock mode that actually satisfies the transaction's requirement, not
FOR UPDATEby default. - Build retry logic for both deadlocks and serialization failures into any transactional code path where concurrent writes to the same rows are possible.
- Establish and enforce a consistent lock-acquisition order for multi-row/multi-table operations across the entire application, the structural fix for recurring deadlocks.
- Keep transactions short and avoid unrelated work between acquiring a lock and committing, to minimize the window during which a deadlock or contention can occur.