# PostgreSQL lock waits: a compatible change can still queue your readers

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](https://training.lockedinlabs.ai/training/guides/integration-engineering#5a-run-and-change-the-migration) 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.

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

## 3. Prepare and run the actual interleavings

Download and inspect both Python files. Keep them in the same directory:

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

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.

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

The 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. Check `columns` and C's actual `rows`; 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:

```sh
python3 fde-postgres-lock-waits.py --reader-end rollback > locks-rollback.json
```

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

```text
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](https://www.postgresql.org/docs/17/explicit-locking.html)
- [PostgreSQL 17: lock and statement timeouts](https://www.postgresql.org/docs/17/runtime-config-client.html)
- [PostgreSQL 17: blocking-session information](https://www.postgresql.org/docs/17/functions-info.html)

Continue with the [migration compatibility workshop](https://training.lockedinlabs.ai/training/guides/integration-engineering#5a-run-and-change-the-migration) to inspect old/new data meaning as a separate question.