# PostgreSQL isolation: retry the decision, not just the update

Original LockedIn Labs workshop · 45 minutes. Use Python 3.11+, an approved running Docker environment and a locally cached PostgreSQL 17 image. No pip packages. The runner creates one owned, disposable database with fictional queue owners, no external network and no published host port. It never connects to an existing database or performs a remote business action.

You will explain a shared rule across two database transactions, inspect an actual serialization failure, and compare a stale-decision retry with a complete read/decide/write retry. Read SQL transactions first. The [lock-wait workshop](https://training.lockedinlabs.ai/training/guides/postgres-lock-waits) covers a different boundary: waiting for a table lock during a schema change.

## 1. State the rule before choosing an isolation level

An **invariant** is a condition the application must keep true. In this fictional service, at least one queue owner must remain available. Alpha and Beta can each leave only if their own transaction observes more than one available owner. These are supplied decision targets; no real person, identity verification or staffing policy is involved.

A **transaction** contains reads and writes until it commits or rolls back. **Isolation** governs how concurrent transactions interact. A **snapshot** is the database view a read uses. A **write skew** occurs here when two transactions inspect the same shared condition, change different rows and jointly violate that condition. This is not two writers overwriting the same row.

The exercise compares these PostgreSQL levels:

| Level | Question to ask in this exercise |
| --- | --- |
| Read Committed | Is a correct-looking earlier read enough for the later decision? |
| Repeatable Read | Does a stable view make two separate decisions jointly valid? |
| Serializable | What happens when the supplied transactions cannot both fit a serial order? |

Serializable constrains committed transaction histories; it does not infer an application rule that the current transaction never reads. A serialization failure asks the application to restart its transaction logic. It does not supply an automatic successful retry or authorize arbitrary remote work.

## 2. Follow two decisions across the database boundary

Both transactions read before either writes. Each observes two available owners and decides it may disable its own row. Alpha writes and commits first. Beta then attempts the update based on its earlier read. The program deliberately orders these operations on two actual PostgreSQL connections, preserving the overlap rather than using a timing guess.

Predict the available-owner count under Read Committed, Repeatable Read and Serializable. Then predict what changes if Beta starts another Serializable transaction but reuses the earlier decision instead of rereading. The visual explains authored cases from the downloadable runner; it does not execute PostgreSQL in the browser.

## 3. Run five actual transaction cases

Download and inspect both files. 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 again if you kept an older lock-queue-only copy. The explicit `--write-skew` flag selects this workshop. Running without it preserves the separate lock-queue exercise. Importing the companion reuses its owned container lifecycle; it does not run the row-policy lab.

```sh
python3 --version
docker image inspect postgres:17 --format '{{.Id}}'
python3 fde-postgres-lock-waits.py --write-skew > isolation-two.json
```

The runner resolves the cached image to its exact image ID and does not pull an image. If absent, obtain it through your approved image process first. Retain `image_id`, any `image_repo_digests`, PostgreSQL version and `owned_container_removed`. A mutable image tag is not an immutable version guarantee.

Write your first prediction before reading each result:

| Case | Beta's action after the first attempt | Your predicted final count |
| --- | --- | --- |
| `read-committed` | Uses its earlier read | Your prediction |
| `repeatable-read` | Uses its earlier read | Your prediction |
| `serializable-no-retry` | Stops after the rejected first attempt | Your prediction |
| `cached-decision-retry` | Starts a transaction but reuses the old count | Your prediction |
| `whole-transaction-retry` | Starts a transaction, rereads and decides again | Your prediction |

Each case starts with fresh table rows. `reads` retains actual backend PID, session/effective role, transaction isolation, snapshot, count and owner rows. `first_decisions` records which observed count the code used, the exact update and its SQLSTATE. `first_ends`, the separate committed-state observation, optional retry and final rows stay distinct. Complete session commands and diagnostics are retained too.

Two sessions use the same ordinary `lab_worker` role with SELECT/UPDATE privileges. Local trust supplies the login. The owner names are supplied decision targets, not authenticated callers or a row-authorization policy. A separate observer reads the committed table; its default isolation is not the worker transaction's isolation. This is an ordered local mechanism experiment, not a connection pool or load test.

## 4. Inspect the failure before designing the retry

With two initial owners, the observed outcomes are:

- Read Committed and Repeatable Read both finish with zero available owners. The writes target different rows; a stable snapshot does not make this shared-rule decision safe.
- In the Serializable case, Beta's update receives `40001`. Its change is rolled back and one owner remains. Alpha is the first committer in this authored ordering; do not turn that ordering into a rule about which participant always wins under a real workload.
- The cached-decision retry starts another Serializable transaction but does not reread the owner table before deciding. Its old count still says two, so it disables Beta and commits. Zero remain. Serializable cannot make this changed, incomplete transaction program enforce the missing read/decision rule.
- The whole-transaction retry rereads one available owner. The decision now withholds the update and ends with one owner available. That is a supported refusal under this supplied rule, not an automatic successful leave operation.

Read the update's SQLSTATE as well as the transaction-end result. In this ordering, COMMIT after the failed update ends the aborted transaction as a rollback and can report `00000`; that does not mean the earlier write committed. The psql process also exits zero so the runner can collect later evidence. Process success, command completion, committed effects and the business rule are separate observations.

A `40001` serialization failure differs from `40P01` deadlock, `55P03` lock timeout and an unknown outcome after losing a connection. Do not classify every database error as retryable. This workshop executes `40001`; the other codes are comparisons, not additional induced cases. An unknown commit outcome needs its own identity and reconciliation design.

The runner permits one authored retry in the two retry cases, then stops. A real retry policy needs error classification, a bounded attempt/deadline budget and a receiving owner for unresolved work. The complete retry includes fresh reads and the code that chooses values and SQL. HTTP calls, messages and other effects outside the database require separate idempotency/outbox evidence; rolling back these rows cannot undo an already sent message.

## 5. Change the capacity and predict useful work

Add one initially available owner without changing the minimum rule or transaction ordering:

```sh
python3 fde-postgres-lock-waits.py --write-skew --owner-count 3 > isolation-three.json
```

Compare the same five cases. Read Committed and Repeatable Read now finish with one available owner, so their observed final count satisfies this fixture's rule. That one successful input does not erase the two-owner counterexample.

The Serializable first attempt still rejects Beta in this supplied schedule. Its overlap can be inconsistent with a serial execution even when the eventual count would meet the rule; a serialization failure is not itself a business-rule-violation measurement. The full retry now observes two available owners, permits Beta to leave, and retains Gamma as the remaining owner. The same read/decide/write policy can therefore withhold or permit work according to the actual new state.

Retain the original and changed reports. Explain why a deny-all repair would miss that useful operation. Change no expected minimum just to make a bad result look acceptable. Other schedules, constraints and database versions need their own observations.

Explain to a partner: Why can Repeatable Read still allow this write skew? What does Serializable constrain? Why can replaying just the UPDATE break the application rule? Why is withholding a now-invalid operation different from exhausting a retry budget? What changes if the original attempt also called a remote service?

## 6. Give the receiver an executable decision

Give another participant the runner, companion and both reports. Let them locate one first-attempt failure and reproduce the changed-capacity result. Keep their first prediction and actual assistance; do not take over their terminal. Mark a solo run as solo.

```text
Runner / companion / image ID / PostgreSQL version:
My original prediction and reasoning:
Actual Alpha and Beta read counts / isolation / backend PIDs:
Update SQLSTATE / transaction-end state / committed rows:
Retry's actual read, or evidence it reused the old decision:
Changed-capacity result and remaining owner:
Receiving action actually attempted / assistance:
My contribution / unresolved claim:
Next owner / requested evidence / bounded next action:
```

The workflow owner defines the minimum rule and whether leaving is permitted. The application engineer owns the complete transaction and bounded retry logic. The database/platform owner owns actual isolation, pool and operational configuration. The receiver checks the specific operation and retained evidence. The pod lead coordinates those responsibilities without inheriting their permissions.

This workshop does not test workload throughput, fairness, a real driver pool, access policy, remote side effects or production release readiness. Keep those as concrete receiving questions. Practice completion does not establish independent client delivery, qualification or placement.

## 7. Primary references

Checked 27 October 2026. The queue-owner scenario, runner mode, diagrams and receiving worksheet are original LockedIn Labs material.

- [PostgreSQL 17: transaction isolation](https://www.postgresql.org/docs/17/transaction-iso.html)
- [PostgreSQL 17: serialization failure handling](https://www.postgresql.org/docs/17/mvcc-serialization-failure-handling.html)

Continue with the [integration workshop](https://training.lockedinlabs.ai/training/guides/integration-engineering#4-bound-retries-and-replay) to connect this database boundary to the original request budget and receiver effects.