Free learning / 50-minute deadlock and receiving lab
PostgreSQL deadlocks: prove the cycle, then change the order
Capture an actual two-transaction wait cycle, inspect the full rollback, and compare statement-only retry, complete retry and consistent row order.
No account needed. Use your own copy for writing and keep the original as a reference.
Engineering lab library / Run locally
Choose your next agent lab.
Start with the durable workflow reference, then choose the boundary you want to test. Open its walkthrough for setup, commands and expected observations.
Synthetic records. No provider key or model inference. Runs locally with the standard library.
Start here / Docker · Actual local containers
PostgreSQL deadlocks, retry scope and consistent row order
Capture a real cycle and full rollback, execute two retry scopes, then compare a consistent lock order.
Python 3.11+ · Docker · Cached postgres:17 · Lifecycle companion · Use --deadlocks
Original LockedIn Labs workshop · 50 minutes. Use Python 3.11+, Docker and a locally cached PostgreSQL 17 image. Two actual connections update two fictional work-item counters inside one owned disposable database. No pip packages, external network, host ports, existing database or remote business action.
You will distinguish a wait from a cycle, find the transaction PostgreSQL rejects, explain which earlier work rolled back, and compare retrying one statement with retrying the whole operation. A final case acquires rows in a consistent order. Start with SQL transactions and the lock-wait workshop. The isolation workshop covers the separate problem of stale read-dependent decisions.
1. Define the operation before interpreting the error
A lock coordinates conflicting access. A wait-for edge points from a transaction that cannot proceed to a transaction it needs to finish. A deadlock contains a cycle of those dependencies. Two slow transactions are not enough to establish a cycle.
Here each participant's complete logical operation increments both work-item counters once, in one transaction. Initial rows are id=1, touches=0 and id=2, touches=0. With both complete operations committed, both counters should be two. These are fictional counters, not financial balances, patients or authenticated identities.
A row update takes locks implicitly; no explicit LOCK TABLE is needed for this experiment. PostgreSQL can detect a deadlock and abort one transaction. Do not depend on a particular victim or mistake that abort for completion of both logical operations.
Illustrative learning artifact
One event. Two transaction boundaries.
event-42 → delivery may repeat →
2. Draw the wait before closing the cycle
Alpha changes row 1; Beta changes row 2. Both changes are still uncommitted. Alpha then requests row 2 and waits for Beta. That is one edge: Alpha → Beta. Beta requests row 1 and adds the reverse edge: Beta → Alpha. Neither can finish its second update while the other keeps its first lock.
The runner waits for actual pg_blocking_pids relationships before moving on. It captures both edges before the detector resolves the cycle. It retains pg_stat_activity states, wait events and pg_locks entries. Row-lock holders are not normally enumerated as a complete list in pg_locks; a transaction-ID wait can carry the relevant dependency. Do not infer “no row lock” from the absence of a convenient tuple-lock row.
Predict which earlier changes survive if either participant is the victim. The following visual is an authored explanation of the downloadable program, not a database running inside this page.
UNDERSTAND THE SYSTEM
A wait becomes a deadlock when the path returns
Follow two actual connection histories from the local runner: a one-way wait, a captured cycle, rollback, two retry scopes and a consistent acquisition order. This visual explains authored cases; it does not execute PostgreSQL or assess a learner.
Watch one example, step by step.
Read the full worked example
- 011 · One edge can be ordinary waiting
Alpha changes row 1 and Beta changes row 2. Alpha requests row 2 and waits. The observer captures Alpha → Beta before the next request. Beta can still finish or roll back and release its lock; this single wait is not yet a cycle.
OBSERVED ONE-WAY WAIT: Alpha → Beta · each first update is uncommitted
- 022 · The second edge closes the cycle
Beta now requests row 1. Each transaction needs the other to end. The runner captures both pg_blocking_pids relationships before detection resolves them. A transaction-ID wait can carry the row dependency; pg_locks is not a complete inventory of every row lock held.
ACTUAL WAIT-FOR CYCLE: Alpha → Beta → Alpha
- 033 · The abort removes earlier work too
One second UPDATE gets 40P01; the victim is not fixed. Its next probe gets 25P02. COMMIT can then report 00000 while rolling that aborted transaction back. Both committed counters are one: the survivor completed; the victim's successful first update disappeared.
OBSERVED ROLLBACK BOUNDARY: 40P01 → 25P02 → end 00000 · rows 1, 1
- 044 · A successful statement can be a partial repair
The failed-statement retry starts a new transaction and replays only the victim's rejected second UPDATE. Its commands succeed, but one counter becomes two and the other stays one. The missing first update belonged to the rolled-back attempt.
EXECUTED INCOMPLETE RETRY: Final counters unequal: one 2, one 1
- 055 · Retry the complete operation once
The full retry begins again, reads the committed rows and increments both counters once before committing. Final values are two and two. This is one authored retry after a known abort, not an automatic policy for unknown commit outcomes or external side effects.
EXECUTED FULL RETRY: Fresh rows 1, 1 → both increments → rows 2, 2
- 066 · Consistent order leaves a path to finish
Both participants take row 1 before row 2, even when the incoming opposite-order input is reversed. Beta waits before holding row 2. Alpha can complete and commit; Beta follows. This case has a one-way wait and rows two and two. Other objects and code paths still need their own review.
CHANGED ACQUISITION ORDER: Alpha: 1 → 2 · Beta: 1 → 2 · no cycle
Read the connections in plain English
- 01Work item 1 → Alpha transaction
Row 1 remains locked by Alpha until its transaction ends.
- 02Alpha transaction → Work item 2
In the opposite-order history, Alpha needs row 2, which Beta holds.
- 03Work item 2 → Beta transaction
In the opposite-order cases, Beta retains row 2 while requesting row 1.
- 04Beta transaction → Work item 1
Beta needs row 1, which Alpha holds; this request closes the opposite-order cycle.
- 05Beta transaction → Database resolution
Either participant may be the victim; retain the actual 40P01 and full rollback.
- 06Database resolution → Receiving engineer
A resolved cycle does not establish that both operations completed.
Check your understanding: After a deadlock abort, is retrying only the rejected UPDATE enough?
No. The victim's earlier successful statements rolled back too. This fixture executes that incomplete retry and produces unequal counters. A supported retry must repeat the whole transaction logic within a bounded policy; consistent lock order prevents this particular cycle but does not eliminate waits.
3. Run four actual transaction cases
Download and inspect both files, then keep them together:
Download the current runner if your older copy lacks --deadlocks. The explicit flag selects this workshop; the default lock queue and --write-skew exercise remain separate. The companion supplies its container lifecycle without running the row-policy exercise.
python3 --version
docker image inspect postgres:17 --format '{{.Id}}'
python3 fde-postgres-lock-waits.py --deadlocks > deadlocks-first.jsonNo image is pulled. If the approved cached image is missing, obtain it through your environment's normal process. Retain the exact image ID, PostgreSQL version and confirmed removal of the owned container. The current tag alone is not an immutable identity.
The scratch administrator sets a three-second deadlock-detection delay for the ordinary worker role so the program can capture the short-lived cycle. Each transaction has larger lock/statement bounds. These settings are teaching controls, not recommendations for a production server. Both connections use the same ordinary role supplied by local trust; this does not test authentication or a connection pool.
Write your predictions before inspecting the report:
| Case | What happens after the first attempt? | Expected counter relationship to investigate |
|---|---|---|
opposite-order | No retry | Only one complete operation remains |
failed-statement-retry | Victim repeats only its rejected second update | One partial operation is added |
whole-transaction-retry | Victim increments both rows in a new transaction | Both complete operations remain |
consistent-order | Both take row 1 before row 2 | One waits, then both finish |
Each case resets the two rows. The report retains session PIDs and actual settings, first-update SQLSTATE, one-way and cycle snapshots, later statement states, separate committed rows, optional retry steps, final rows and complete session transcripts. It identifies the actual victim from 40P01; no participant is declared the guaranteed loser.
4. Read the rollback boundary, not just the final command
In the three opposite-order cases, one second update receives 40P01 (deadlock_detected). A probe in that same failed transaction receives 25P02 (in_failed_sql_transaction). The program then deliberately issues COMMIT: it can report 00000 while ending the aborted transaction as a rollback. The earlier successful first update from that victim is gone too. Inspect the first error and committed rows; the last command and psql process exit are insufficient.
After the first attempt, both counters are one. The survivor's two increments committed; the victim's entire attempt did not. The no-retry case stops there. That is one completed logical operation, not two.
Retrying only the rejected second UPDATE in a new transaction produces unequal counters: one is two, the other one. The first update from the aborted attempt was not restored. Every command in this new attempt can succeed while the intended two-row operation remains incomplete. This intentionally incomplete repair is executed so the receiver can point to the exact missing work.
The complete retry begins again, reads the committed rows and applies both increments once, then commits. Final counters are two and two. This is one authored retry after a known database abort. A real policy still needs error classification, a total attempt/deadline budget and a reconciliation owner. The separate isolation exercise explains why read-dependent decisions also need to be recomputed. An unknown connection/commit outcome cannot be treated as this confirmed abort, and row rollback cannot undo an external HTTP call or message.
5. Change the request order; keep one lock order
Run a changed input without changing the operation or expected final result:
python3 fde-postgres-lock-waits.py --deadlocks --first-row 2 > deadlocks-reversed.jsonAlpha now requests row 2 before row 1 in the opposite-order cases; Beta starts with row 1. Compare the actual cycle, victim and failed-statement retry's unequal rows. Do not change expected values according to which transaction you hoped would lose. A second run choosing the same victim does not establish a selection rule.
The consistent-order case deliberately normalizes both participants to row 1 then row 2 in both runs. Alpha holds row 1; Beta waits there before acquiring row 2. Alpha can finish row 2 and commit, allowing Beta to finish. The captured graph has one directed edge and no cycle; both final counters are two. Locking still waits. This experiment establishes neither fairness nor throughput.
In a real service, every relevant code path needs the same order across all affected objects. Sorting the values in an IN (...) list is not by itself proof of acquisition order. Here the program sends two explicit, ordered UPDATE statements. Triggers, foreign keys, other tables, lock upgrades and other applications can introduce dependencies absent from this two-row fixture.
Explain to a partner: Can ordinary UPDATE statements deadlock? What distinguishes a row conflict from a cycle? Which successful earlier work disappears after 40P01? Why does retrying just the failed statement produce partial work? Why does consistent order prevent this particular cycle without eliminating waiting? What evidence would you need before applying the idea to a client service?
6. Receive the evidence as a pod
Each participant writes a prediction and retains their own original/changed reports. Exchange one case with a receiver. Ask them to reconstruct the two edges from actual PIDs, locate the failed transaction and explain the committed counter values without the author taking over. Record actual assistance; mark solo work as solo.
Runner / companion / image ID / PostgreSQL version:
My prediction before running / original request order:
Actual Alpha and Beta PIDs / first SQLSTATE / lock settings:
One-way wait edge / cycle edges / exact observation fields:
Actual victim / second UPDATE state / probe state / transaction-end state:
Committed rows after the abort / why the first update disappeared:
Retry scope / actual fresh read / SQL / final counter values:
Changed first-row run / consistent-order wait / remaining untested paths:
Receiving action actually attempted / assistance:
My contribution / next owner / requested evidence / bounded next action:The workflow owner defines the complete business operation. The application engineer owns transaction boundaries, shared acquisition order and bounded retry logic. The database/platform owner owns detector settings, diagnostics and the production workload evidence. The receiver verifies the retained graph and effects. The pod lead coordinates their review without acquiring their privileges.
Practice completion is not proof of client placement, qualification or operational independence. A useful next receiving task is to inspect one real application's transaction wrapper in an approved scratch environment and name a hidden lock dependency this fixture does not include.
7. Check the primary references
Sources checked 27 October 2026: PostgreSQL 17 deadlocks and explicit locking, lock monitoring, transaction failure handling and SQLSTATE codes. The fixture, code, visual and receiving exercise are original LockedIn Labs teaching material.