# PostgreSQL deadlocks: prove the cycle, then change the order

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](https://training.lockedinlabs.ai/training/guides/postgres-lock-waits). The [isolation workshop](https://training.lockedinlabs.ai/training/guides/postgres-isolation) 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.

## 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.

## 3. Run four actual transaction cases

Download and inspect both files, then keep them together:

- [PostgreSQL runner](https://training.lockedinlabs.ai/templates/fde-postgres-lock-waits.py)
- [Owned-runtime companion](https://training.lockedinlabs.ai/templates/fde-postgres-row-security.py)

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.

```sh
python3 --version
docker image inspect postgres:17 --format '{{.Id}}'
python3 fde-postgres-lock-waits.py --deadlocks > deadlocks-first.json
```

No 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:

```sh
python3 fde-postgres-lock-waits.py --deadlocks --first-row 2 > deadlocks-reversed.json
```

Alpha 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.

```text
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](https://www.postgresql.org/docs/17/explicit-locking.html#LOCKING-DEADLOCKS), [lock monitoring](https://www.postgresql.org/docs/17/view-pg-locks.html), [transaction failure handling](https://www.postgresql.org/docs/17/mvcc-serialization-failure-handling.html) and [SQLSTATE codes](https://www.postgresql.org/docs/17/errcodes-appendix.html). The fixture, code, visual and receiving exercise are original LockedIn Labs teaching material.