LEARN. PRACTICE. GROW.Bound the right wait
While A retains the lock, B uses an 80-millisecond lock_timeout and a five-second statement_timeout. Its UPDATE attempt fails with 55P03 and a lock-timeout diagnostic. The limit concerns acquiring a lock; it is not a total budget for the whole transaction. The script configures short values only in exercise sessions to make behavior observable. It does not recommend these values for production. In an actual service, associate limits with the latency budget, retry feasibility and client behavior when an attempt ends without performing the update.
One SQLSTATE can have different operational causes
Next, B uses an 80-millisecond statement_timeout and a one-second lock_timeout. The statement ends first and returns 57014 with a statement-timeout message. Earlier explicit cancellation also produced 57014, but from a different origin. Retain code, diagnostic and effective configuration before assigning cause. An application should use structured codes to classify errors without losing details needed for investigation. The workshop neither measures timer precision nor promises that an operation ends at an exact instant. It shows which limit interrupted the query under controlled conditions and confirms the need to recover the transaction.
Use a savepoint with explicit intent
B begins a transaction, inserts note before and creates retry_step. The blocked UPDATE fails; B rolls back to the savepoint, inserts after and commits. Both notes are recorded, but the blocked update did not occur. This is a scope choice, not automatic replay of the entire transaction. The example shows that work preceding the savepoint remains eligible for commit. In an actual workflow, ask whether committing those effects without the main update respects the requirement. If the business operation is indivisible, retaining notes alone is not correct recovery and may require full rollback.
Reproduce a wait cycle
Two fresh sessions update different rows, subtracting ten units. Each then tries to update the row retained by the other. The observer confirms the first wait before starting the second, forming the cycle under controlled ordering. PostgreSQL interrupts one transaction with 40P01 and allows the other to continue. The test requires exactly one victim without choosing which PID must lose. Detection does not mean every participant was rolled back. After cleaning up the rejected transaction and committing the survivor, both rows hold 90: the victim’s partial work was undone and both survivor updates were committed.
Change ordering without promising universal deadlock freedom
The last experiment has both sessions request rows in the same order. The first retains row 1; the second waits there. The first can update row 2 and commit, allowing the second to update both. Both commits leave 80 in each row. Consistent ordering eliminates the specific reproduced cycle but does not establish that an entire application is deadlock-free. Triggers, additional relations and other code paths can introduce dependencies. Retain error handling and a bounded strategy for retrying the complete transaction with fresh reads and reconciliation of external effects when present.
Deliver a verifiable recovery decision
For the fictional workshop, present the error and ask the participant to identify connection state, committed work and the next authorized command. An answer saying only retry is insufficient: it should distinguish full rollback, return to a savepoint, a new transaction and intervention on the blocker. The workshop records actual results and removes its cluster after joining every query thread. It implements neither application retries, pools nor external-request deduplication. Representative validation should include those elements, attempt limits, observability and functional criteria, alongside specialist human review before claiming operational readiness.
-- Run only in the disposable workshop with its owned fixture.
BEGIN;
INSERT INTO notes VALUES (1, 'before');
SAVEPOINT retry_step;
SET LOCAL lock_timeout = '80ms'
-- If the following statement fails, the client must choose recovery scope:
UPDATE balances SET units = 70 WHERE id = 1;
-- The teaching branch uses ROLLBACK TO SAVEPOINT retry_step;
-- then inserts its second note and commits only that intended partial scope.
lock_timeout produces 55P03; a shorter statement_timeout produces 57014. A savepoint permits step recovery without losing earlier notes.
Common pitfalls
Classifying every wait as deadlock; repeating only the final UPDATE after abort; assuming a fixed victim; converting timeout into permission to commit.
Related topics: Transactions and concurrency · Diagnosis and support handover
Correct recovery depends on failed scope and the business requirement. An error code alone does not determine what to retry.
Reference: Statement, lock and transaction timeouts · PostgreSQL 18 reference semantics;18.6 current stable at review