Free learning / 45-minute transaction retry lab
PostgreSQL isolation: retry the decision, not just the update
Run overlapping database transactions, expose write skew and inspect an actual serialization failure. Compare a cached-decision retry with a fresh read before acting.
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 isolation and full transaction retry
Observe five actual two-connection cases and change capacity while retaining the read, decision and committed effects.
Python 3.11+ · Docker · Cached postgres:17 · Lifecycle companion · Use --write-skew
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 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.
Illustrative learning artifact
One event. Two transaction boundaries.
event-42 → delivery may repeat →
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.
UNDERSTAND THE SYSTEM
A fresh transaction needs a fresh decision
Follow authored explanations of five actual PostgreSQL cases and the changed-capacity run. The runner records two connections, SQLSTATE and committed rows; this visual does not execute a database or assess a learner.
Watch one example, step by step.
Read the full worked example
- 011 · Both decisions look reasonable
Both connections read two available owners before either writes. Each sees more than one and decides its own owner may leave. The program then commits Alpha before attempting Beta's update. The apparent local justification is the same in all five cases.
ACTUAL FIRST READS: Alpha sees 2 · Beta sees 2 · rule requires ≥1
- 022 · Two commits break the shared rule
At Read Committed and Repeatable Read, both updates and ends report 00000. Alpha and Beta update different rows, so neither overwrites the other's row. The final table has zero available owners. A stable snapshot does not establish a serializable combined outcome.
OBSERVED WRITE SKEW: Both owners unavailable · final count 0
- 033 · Serializable rejects one write
In the authored ordering, Beta's update receives 40001 and its change is rolled back. One owner remains. The subsequent COMMIT can report 00000 while ending that aborted transaction as a rollback. Inspect the failed update and committed table before claiming a successful operation.
ACTUAL REJECTION: Update 40001 · later end 00000 · final count 1
- 044 · Reusing the decision loses the rule
The cached-decision case starts another Serializable transaction but does not reread the owner table. The old count says two, so Beta's update commits and zero remain. Serializable cannot enforce an application rule omitted from the current transaction's reads and logic.
DELIBERATELY INCOMPLETE RETRY: Old count 2 · new UPDATE commits · final count 0
- 055 · Reread before deciding again
The complete retry starts a transaction and observes one available owner. Its decision withholds the update, so Beta remains available. This is a supported refusal under the supplied rule. It is not an exhausted retry budget or a completed leave operation.
COMPLETE TRANSACTION RETRY: Fresh count 1 · update withheld · final count 1
- 066 · More capacity permits useful work
Change only --owner-count to 3. The supplied Serializable overlap still rejects Beta first, but its complete retry now reads two available owners and allows the update. Gamma remains. A serialization failure is not a measurement that the business minimum would be breached; retain both kinds of evidence.
CHANGED INPUT: Fresh count 2 · Beta may leave · Gamma remains
Read the connections in plain English
- 01Alpha's transaction → Observed owner rows
Alpha's original database observation is retained.
- 02Observed owner rows → Beta's transaction
Beta's first read occurs before Alpha's update and commit.
- 03Beta's transaction → Retry boundary
40001 is an actual rejection; a new transaction is not yet a complete retry.
- 04Retry boundary → Minimum available
A fresh decision can withhold or permit the update.
- 05Minimum available → Receiving review
Compare the supplied rule with actual committed rows and unresolved paths.
Check your understanding: Does starting another Serializable transaction make a cached decision current?
No. Restart the read and decision logic as part of the transaction. In this exercise, replaying the old update commits an invalid final state; a full retry rereads one owner and withholds it, or rereads two in the changed-capacity case and permits it.
3. Run five actual transaction cases
Download and inspect both files. Keep them together:
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.
python3 --version
docker image inspect postgres:17 --format '{{.Id}}'
python3 fde-postgres-lock-waits.py --write-skew > isolation-two.jsonThe 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:
python3 fde-postgres-lock-waits.py --write-skew --owner-count 3 > isolation-three.jsonCompare 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.
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.
Continue with the integration workshop to connect this database boundary to the original request budget and receiver effects.