A PostgreSQL deadlock means transactions have formed a cycle of dependencies: each needs a lock held by another, so waiting alone cannot let everyone finish. The database can break that cycle. It cannot decide whether your application can safely repeat the business operation.
Consider an inventory service that reserves two products in one transaction. One request locks product 10 and then product 20. Another request reaches the same products in the opposite order. Both requests are reasonable individually. Together they expose a missing coordination rule.
The engineering objective is to make that rule explicit, keep transactions short, and recover without repeating an external effect. This guide uses a deliberately small example. The same review applies to transfers, multi-row allocations, and updates crossing aggregates.
Reproduce the cycle before changing timeouts #
Use a disposable database. Create the demonstration table once:
CREATE TABLE lock_order_demo (
id integer PRIMARY KEY,
quantity integer NOT NULL
);
INSERT INTO lock_order_demo VALUES (10, 100), (20, 100);In connection A, run:
BEGIN;
SELECT id FROM lock_order_demo WHERE id = 10 FOR UPDATE;In connection B, run:
BEGIN;
SELECT id FROM lock_order_demo WHERE id = 20 FOR UPDATE;Now ask connection A to lock 20. While it waits, ask connection B to lock 10:
-- Connection A: blocks until the cycle is resolved.
SELECT id FROM lock_order_demo WHERE id = 20 FOR UPDATE;
-- Connection B: run separately, while A is blocked.
SELECT id FROM lock_order_demo WHERE id = 10 FOR UPDATE;PostgreSQL detects the deadlock and aborts one participant; do not depend on which one. End both demonstration transactions with ROLLBACK after the failure resolves. PostgreSQL describes this behavior and recommends consistent lock acquisition order in its locking documentation.
Increasing a statement timeout does not fix the cycle. It only changes how long another class of wait may survive.
Establish an ordering contract #
For this two-row operation, every writer can acquire the lower immutable identifier first and the higher identifier second. Do that before modifying either row:
BEGIN;
SELECT id FROM lock_order_demo WHERE id = 10 FOR UPDATE;
SELECT id FROM lock_order_demo WHERE id = 20 FOR UPDATE;
-- Perform both related changes, then commit.
COMMIT;This is a locking demonstration, not a complete reservation implementation. Production code must validate that both rows exist, quantities satisfy the business rule, and the caller owns the operation.
The important word is every. Sorting identifiers inside one endpoint is insufficient if a batch job, trigger, administrative script, or another service locks the same resources differently.
For operations spanning tables, document the table order as well as the row order. Review foreign-key interactions and trigger behavior. A local convention reduces a known deadlock pattern; it is not a proof that no other cycle exists.
Retry the transaction, including the decision #
PostgreSQL identifies a detected deadlock with SQLSTATE 40P01. Serialization failure uses 40001. These are different errors, even when the application handles both through a transaction-retry boundary. Use error codes rather than matching localized error strings. See the error-code reference.
The retry boundary must include reads and application decisions that determined the writes. Suppose an attempt reads available stock, selects a fulfillment location, and then deadlocks. Replaying only the final UPDATE can reuse a decision that no longer fits the current state.
PostgreSQL explicitly calls for retrying the complete transaction, including logic that selects SQL and values, in its failure-handling guide. An aborted attempt must be rolled back before a fresh attempt begins.
Keep a stable business operation identifier across attempts, but recompute state-dependent choices. These are different forms of state: the intent belongs to the caller; the allocation decision belongs to the current database snapshot.
Keep external effects outside the replay boundary #
Imagine sending an email between two SQL updates. PostgreSQL can undo its own writes after a deadlock. It cannot retract the email. The second attempt may send another.
A safer design records the business change and an outbox event in the same transaction, then performs delivery separately. That still requires a delivery and deduplication contract, as explained in the transactional outbox guide.
The same concern applies to charging a payment method, calling a partner API, or granting access in another system. A database rollback does not imply those effects failed.
Failure policy #
| Observation | Application response |
|---|---|
| 40P01 during a database-only transaction | Roll back; retry the full operation within its budget |
| Caller cancels or deadline expires | Stop creating attempts |
| Business validation fails after rereading | Return that outcome; do not force the original decision |
| Connection disappears during commit | Treat the result as ambiguous; reconcile by operation ID |
| Deadlocks recur on one operation pair | Investigate ordering and transaction scope |
A bounded retry policy is recovery machinery. It should not hide a continuously contested design. Count attempts separately from successful business operations and inspect which operation pairs conflict.
What to measure and what to change #
Track deadlocks by normalized operation name, retry exhaustion, transaction duration, and lock-wait duration. Keep record identifiers and customer data out of metric labels. Retain carefully scoped diagnostic logs when correlation requires them.
Start with transaction scope: remove network calls and user interaction from held-lock intervals. Next, examine lock ordering across all writers. If a small set of hot resources still serializes most traffic, consider whether the domain needs admission control or a different ownership boundary.
That decision has a cost. Serializing an entire tenant may simplify correctness while reducing throughput for unrelated operations. Increasing parallelism can improve unconstrained work while worsening contention on the same inventory rows. Measure useful committed operations, not merely active connections.
Production review #
Before shipping, reproduce reversed acquisition in two sessions, verify both database effects roll back, and verify the business operation can be attempted again. Then interrupt the client near commit and test reconciliation separately: an ambiguous commit is not a deadlock.
Ask whether an old application version or maintenance job can violate the ordering contract. During deployments, both versions may coexist. Tie that review to the schema compatibility checklist.
The defensible outcome is a system whose ordering rule is documented, whose retry boundary includes the decision, and whose external effects remain safe when an attempt disappears.