Free learning / 45-minute PostgreSQL concurrency lab
PostgreSQL lock waits: a compatible change can still queue your readers
Observe three actual PostgreSQL sessions, identify a waiting schema change that delays a later reader, and compare cancellation, lock timeout and transaction release.
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 lock queues · Python
Inspect actual hard and soft blockers across three sessions, then distinguish a refused DDL attempt from an applied change.
Python 3.11+ · Docker · Cached postgres:17 · Keep lifecycle companion beside runner
Original LockedIn Labs exercise. Allow 45 minutes. You need Python 3.11+, an approved running Docker environment and a locally cached postgres:17 image. No pip packages. This workshop uses fictional data inside one disposable container, with no external network or published host port. It never connects to an existing database.
By the end, explain a real three-session blocking chain, distinguish a cancelled or timed-out change from an applied change, and give the receiving team the evidence needed for its next action. Read SQL transactions first; the migration workshop covers old/new data compatibility separately.
1. Learn the lock vocabulary
A transaction groups database work until COMMIT or ROLLBACK. DDL, data definition language, changes database structure. PostgreSQL automatically takes locks to keep conflicting operations apart. A query can finish while its explicit transaction remains open, retaining a table lock. The session may say idle in transaction: it is waiting for the client, not executing that SELECT, yet still holds the lock.
| This workshop's term | What to inspect |
|---|---|
| AccessShareLock | Acquired by the ordinary SELECT on lab.jobs |
| AccessExclusiveLock | Required by this ALTER TABLE ... ADD COLUMN |
| Granted lock | The session already holds it |
| Waiting lock | Requested but not granted |
| Hard blocker | Holds a conflicting lock |
| Soft blocker | Waits ahead of another request with a conflicting mode |
| Backend PID | Actual database session identity; different on another run |
| SQLSTATE | Database outcome code; independent of psql's process exit |
The lab has one table containing job 42 with owner Queue A. The migration adds a nullable owner_display_name column without a default. The old query still selects only id, owner_name. That shape remains compatible, but lock acquisition can still delay it. Do not infer that every ALTER TABLE takes the same lock, that every column addition rewrites every row, or that a small table cannot cause a wait.
Illustrative learning artifact
One event. Two transaction boundaries.
event-42 → delivery may repeat →
2. Follow three sessions through one queue
Session A is an ordinary read-only role in an explicit transaction. It reads job 42, then waits for the client. Session B is the ordinary migration owner and requests the column addition. Session C, another ordinary reader, arrives after that DDL is waiting. An observer inspects only these owned sessions and this table.
Predict whether C can finish while A's read transaction remains open and B is still waiting. Two read locks are compatible with each other; examine what the intervening exclusive request changes. The visual explains authored steps from the runnable experiment. It does not execute a database in the browser.
UNDERSTAND THE SYSTEM
Follow the lock queue, not just the compatible schema
Three actual PostgreSQL sessions produce these authored teaching steps. The downloadable runner retains PIDs, locks, blockers, SQLSTATE and final rows. This visual explains the mechanism; it runs no database in your browser.
Watch one example, step by step.
Read the full worked example
- 011 · A finished query retains a lock
A reads job 42 inside BEGIN. Its query returns Queue A, then the session becomes idle in transaction. The granted AccessShareLock remains until the transaction ends. Read-only work still has a lifecycle.
OBSERVED START: A · AccessShareLock · granted
- 022 · A compatible change waits
B requests ADD COLUMN owner_display_name text. The new optional field would preserve the old query's shape, yet B cannot acquire AccessExclusiveLock. Its actual blocker list names A. SQL compatibility and availability are separate questions.
OBSERVED DDL WAIT: B → A · AccessExclusiveLock not granted
- 033 · A later reader queues too
C requests an ordinary SELECT after B is waiting. C's read lock is compatible with A's, but it cannot jump the earlier exclusive request. The report records B as C's blocker. This queue is not a deadlock cycle.
OBSERVED CHAIN: C → B → A · two actual waiting locks
- 044 · Refuse the waiting change
Cancelling B yields 57014; letting B's lock timeout expire yields 55P03. Either path removes this queued change and lets C return job 42 while A remains idle in transaction. The new column is absent. A later read succeeding is not evidence that the migration applied.
TWO REFUSAL CASES: DDL not applied · C returns 42 · A still open
- 055 · End the original transaction
In the third case, A commits, B acquires its lock and commits the column addition, and C reads job 42. Changing only A's end to ROLLBACK also releases this read-only transaction's lock. Inspect columns and rows separately; this does not test write rollback or production latency.
APPLIED CHANGE: DDL 00000 · column present · C returns 42
Read the connections in plain English
- 01A · Read transaction → lab.jobs
A finishes its query but leaves the explicit transaction open.
- 02lab.jobs → B · Schema change
The schema change cannot acquire its requested lock while A holds its read lock.
- 03B · Schema change → C · Later reader
An earlier waiting exclusive request can be a soft blocker.
- 04C · Later reader → Observe the chain
Inspect PID relationships, modes, granted flags and transaction states.
- 05Observe the chain → Receiving decision
A released queue and an applied schema are different results.
Check your understanding: A later SELECT succeeds after the migration's lock timeout. Has the migration succeeded?
No. Inspect its SQLSTATE and the schema. In this experiment the timed-out DDL records 55P03, the new column is absent and the earlier transaction is still open. The later read resumes because the conflicting request left the queue.
3. Prepare and run the actual interleavings
Download and inspect both Python files. Keep them in the same directory:
The companion supplies the existing cached-image resolution, bounded startup and owned-container cleanup. Importing it does not run its separate row-policy lab. Review both files before execution. The image tag is resolved once to its cached image ID; retain image_id and any image_repo_digests. A tag is not an immutable version guarantee. If the image is absent, obtain it through your approved image process first; neither runner pulls an image.
python3 --version
docker image inspect postgres:17 --format '{{.Id}}'
python3 fde-postgres-lock-waits.py > locks-original.jsonThe runner creates three persistent psql connections per case and uses a separate observer. It starts the later read only after inspecting the queued DDL, then waits until the later read's ungranted lock is actually present. These are ordered real database interleavings, not an arbitrary sleep used as proof of blocking. Startup, observation and statement deadlines bound the run. A missing observation fails the lab instead of becoming a completed-case result.
Before looking at the report, write your predictions for these three resolutions:
| Case | What the coordinator changes | Predict DDL, later read and columns |
|---|---|---|
cancel-queued-ddl | Cancels only B's current query in the owned scratch database | Your prediction |
lock-timeout | Leaves A open; B has a six-second lock timeout | Your prediction |
release-reader | Commits A; B can acquire its lock and commit | Your prediction |
The observer's postgres role can inspect sessions and cancel the selected synthetic query. That is local exercise authority, not a permission given to a learner in production. Local trust supplies connection identities without verifying passwords or tokens. Only the runner's randomly named container is removed; existing containers and databases are left alone.
4. Read the evidence before choosing an intervention
Inspect later_read_queued.sessions and later_read_queued.table_locks beside each case's pids. PID numbers vary. In the observed chain, A holds a granted AccessShareLock; B waits for AccessExclusiveLock and names A in blockers; C waits for AccessShareLock and names B. C is blocked by the earlier queued exclusive request even though A's and C's read locks are mutually compatible.
pg_blocking_pids can report both hard and soft blockers. A snapshot is one observation, not a historical trace of everything a session did. Retain the actual SQL, modes, granted flags and session states. A wait event alone is less informative than that relationship. No deadlock is induced: this is a queue with a blocker, not a cycle of sessions waiting on one another.
The three outcomes differ:
- Cancelling B produces DDL SQLSTATE
57014. Its transaction does not apply the column. C reads job 42 while A still holds its original read transaction open. - B's lock timeout produces
55P03. The column remains absent; C can finish after B leaves the queue. A is still idle in transaction. This bounded refusal protects this attempt from waiting indefinitely, but does not repair A's lifecycle. - Ending A releases its lock. B completes with
00000, commits the new column, and C reads job 42. Checkcolumnsand C's actualrows; a success code alone does not populate the new field or prove all consumers work.
The psql processes normally exit zero even for the deliberately failed DDL because this teaching script continues to collect evidence. Read ddl_sqlstate, diagnostics, final schema and later query independently. The COMMIT after a failed transaction ends it as a rollback; it does not turn the failed ALTER into a successful migration.
lock_timeout bounds each lock acquisition wait. statement_timeout bounds a statement more broadly. Neither is a promise that the whole migration, user request or deployment meets a service objective. Retrying DDL in a tight loop could repeatedly rebuild the same queue. Diagnose the transaction owner and agree a bounded retry or maintenance action before another attempt.
5. Change the transaction end and preserve the comparison
Repeat the experiment with only the read-only blocker's final end changed:
python3 fde-postgres-lock-waits.py --reader-end rollback > locks-rollback.jsonPredict which case changes. release-reader.resolution_action.sql now records ROLLBACK. That read-only transaction has no writes to preserve. Its lock still releases, B adds the column and C reads job 42. Compare actual commands, outcomes and columns. Do not transfer the same conclusion to a transaction that made business writes: commit and rollback would then have different data effects.
An optional second experiment changes only --lock-timeout-ms to 9000. The lock-timeout case still refuses the waiting DDL while A stays open; the other cases use their own fixed safety bounds. Wall-clock speed is not the learning result. A longer wait does not remove the dependency or establish acceptable request latency.
Explain these interview questions to a partner: Why can read-only work hold a lock? Why does adding an optional column still need a lock? How can a waiting DDL request block a later SELECT? What is the difference between a deadlock, a lock timeout and statement cancellation? Why are data compatibility and operational availability separate release questions?
6. Receive the change as a team
Give another participant the two reports and the unchanged runner files. Let that person locate the chain and reproduce one resolution without taking over their keyboard. If working alone, mark it solo. Keep an individual record:
Candidate files / image ID / PostgreSQL version:
My initial prediction and why:
Actual blocker → DDL → later-reader PIDs:
Exact lock modes / granted flags / SQLSTATE inspected:
Actual later row and final columns:
Receiving action actually attempted / help received:
Mismatch or unresolved claim:
My implementation or review contribution:
Next owner / requested evidence / bounded next action:The application owner explains why the transaction stays open and how it ends on errors and cancellation. The database change owner owns the exact DDL, permissions, lock/statement budget and supported retry plan. The receiving operator owns the actual probe and evidence for the customer task. The pod lead coordinates those people and unresolved decisions; coordination grants none of their permissions.
For a real environment, ask for its PostgreSQL version, actual transaction and pool behavior, competing workload, table/dependency size, monitoring authority, migration retirement plan and recovery-compatible readers/writers. Index construction, rewrites, long backfills, replication lag, failover and user latency are not exercised here. This is a local mechanism demonstration with actual SQL, not zero-downtime release approval, client-site independence, qualification or placement.
7. Primary references
Checked 27 October 2026. The scenarios, code, diagram and receiving worksheet are original LockedIn Labs material.
- PostgreSQL 17: explicit locking
- PostgreSQL 17: lock and statement timeouts
- PostgreSQL 17: blocking-session information
Continue with the migration compatibility workshop to inspect old/new data meaning as a separate question.