# Postgres: lock contention ## Concept When two sessions want to write the same row, one of them waits. That much is obvious. What isn't obvious is that Postgres will tell you *exactly* who is waiting, on whom, and for what — a blocked query is not a black box. This exercise makes a session block on purpose, then walks the chain from "something is slow" to the precise process ID holding the lock, using `pg_stat_activity`, `pg_blocking_pids()` and `pg_locks`. Along the way: row locks turn out to have four different strengths that are not interchangeable, the thing a blocked session actually waits on is not the row, and a genuine deadlock gets detected and killed in front of you. ## Provenance PostgreSQL 16 documentation. - **§13.1 Introduction** — the claim Part 1's first probe tests: *"in MVCC locks acquired for querying (reading) data do not conflict with locks acquired for writing data, and so reading never blocks writing and writing never blocks reading."* - **§13.3.2 Row-Level Locks** — the four modes and their conflicts, which Part 2 measures directly. On `FOR NO KEY UPDATE`: *"Behaves similarly to `FOR UPDATE`, except that the lock acquired is weaker: this lock will not block `SELECT FOR KEY SHARE` commands that attempt to acquire a lock on the same rows. This lock mode is also acquired by any `UPDATE` that does not acquire a `FOR UPDATE` lock."* That last sentence is exactly what steps 20–22 confirm. - **§54.12 `pg_locks`** — the source of Part 1's aha, stated outright: *"Although tuples are a lockable type of object, information about row-level locks is stored on disk, not in memory, and therefore row-level locks normally do not appear in this view. If a process is waiting for a row-level lock, it will usually appear in the view as waiting for the permanent transaction ID of the current holder of that row lock."* And: *"When a process finds it necessary to wait specifically for another transaction to end, it does so by attempting to acquire share lock on the other transaction's ID."* - **§9.26.1 Session Information Functions** — `pg_blocking_pids(integer)`: *"Returns an array of the process ID(s) of the sessions that are blocking the server process with the specified process ID from acquiring a lock, or an empty array if there is no such server process or it is not blocked."* - **§28.2.3 `pg_stat_activity`** (inside §28.2, *The Cumulative Statistics System*) — the `wait_event_type` / `wait_event` columns, and the note that *"If the state is `active` and `wait_event` is non-null, it means that a query is being executed, but is being blocked somewhere in the system."* - **`SELECT` reference, "The Locking Clause"** — `NOWAIT` and `SKIP LOCKED`: *"With `NOWAIT`, the statement reports an error, rather than waiting, if a selected row cannot be locked immediately. With `SKIP LOCKED`, any selected rows that cannot be immediately locked are skipped. Skipping locked rows provides an inconsistent view of the data, so this is not suitable for general purpose work, but can be used to avoid lock contention with multiple consumers accessing a queue-like table."* That last clause is Part 3's job queue, endorsed by the manual. - **§13.3.4 Deadlocks** and **§20.13 `deadlock_timeout`** — Part 4. The config docs explain the delay: *"The check for deadlock is relatively expensive, so the server doesn't run it every time it waits for a lock. We optimistically assume that deadlocks are not common in production applications and just wait on the lock for a while before checking for a deadlock. … The default is one second (`1s`)."* One thing you will observe that the manual does *not* claim: in step 27 Postgres prints its own internal foreign-key check — `SELECT 1 FROM ONLY "public"."accounts" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x` — in an error `CONTEXT`. That's the server's own evidence for why `FOR KEY SHARE` exists, and it's better than a doc sentence. Note also §13.3.4's explicit warning, which Part 4 tests against: *"(Exactly which transaction will be aborted is difficult to predict and should not be relied upon.)"* ## Prerequisites Three ideas carry the result. The first is the one to be solid on. - **MVCC row versions and `xmax`** — *carries the whole exercise.* An `UPDATE` does not overwrite a row; it writes a new version and stamps the old one with the id of the transaction that superseded it (`xmax`). Steps 3–4 (a reader that doesn't block) and step 14 (waiting on a transaction id rather than a row) both fall straight out of this. → [PostgreSQL docs §13.1, Introduction to MVCC](https://www.postgresql.org/docs/16/mvcc-intro.html) - **Transaction IDs (xids) as lockable objects** — *carries Part 1's aha and Part 4's `DETAIL` line.* Every transaction holds an `ExclusiveLock` on its own xid for its whole life. This is the only lock object row-level waiting ever uses. → [PostgreSQL docs §54.12, `pg_locks`](https://www.postgresql.org/docs/16/view-pg-locks.html) - **Lock modes as a compatibility matrix** — used in Part 2. A "lock" is not a boolean; it is a named mode, and the only question that matters is which other modes it conflicts with. → [PostgreSQL docs §13.3, Explicit Locking](https://www.postgresql.org/docs/16/explicit-locking.html) (the row-level table there is the answer key to step 17) ## Environment - **Host:** macOS 26.5.2 (build 25F84), Apple Silicon (arm64) - **Docker:** Docker Desktop, Engine 29.6.2 — Linux VM kernel `6.12.76-linuxkit`, 16 GiB allocated to the VM - **Postgres image:** `postgres:16` — PostgreSQL 16.14 (Debian 16.14-1.pgdg13+1), digest `sha256:33f923b05f64ca54ac4401c01126a6b92afe839a0aa0a52bc5aeb5cc958e5f20` - **Captured:** 2026-07-26 Backend process IDs, transaction IDs and tuple locations (`(0,3)`) below are whatever the server assigned on this run; yours will differ. Nothing in the exercise depends on their values — only on which pid appears where. ## Mental model: four row-lock strengths, not one `SELECT ... FOR UPDATE` is the one everybody knows, and reaching for it reflexively is the most common way to cause avoidable contention. Postgres has four row-level lock modes, ordered weakest to strongest. Every one of them still lets plain `SELECT`s through untouched — **reading never blocks writing and writing never blocks reading** in Postgres, at any of these levels. These modes only conflict with each other. (That premise is worth watching hold and then break: [ddia/ch08/mvcc-no-blocking](../ddia/ch08/mvcc-no-blocking.md) has a writer sail past a long-lived reader, then adds `FOR UPDATE` to the *read* and the writer blocks — the moment a reader steps into this table.) | Mode | What it actually does | Reach for it when... | | --- | --- | --- | | **`FOR KEY SHARE`** (weakest) | Reserves the row's *key* only: nobody may delete it or change a column its key depends on. Anything else — including a plain `UPDATE` of a non-key column — proceeds. | Almost never by hand. You use it constantly without knowing: this is the lock a **foreign-key check takes on the parent row** when you insert a child row. Part 2 catches Postgres doing exactly this. | | **`FOR SHARE`** | Freezes the whole row against modification, but multiple sessions can hold it simultaneously. | "I need this row to stay exactly as it is until I commit, and so might other readers." Validating several child records against one parent config row. Note that it does **not** reserve a right to write later — two `FOR SHARE` holders who both then try to update will deadlock (Part 4's shape). | | **`FOR NO KEY UPDATE`** | Exclusive: only one holder. Blocks `FOR SHARE` and stronger, but still permits `FOR KEY SHARE`. | You rarely type it — **this is the lock a plain `UPDATE` takes** when it doesn't touch a key column. Worth naming because it explains why ordinary updates don't block foreign-key inserts. | | **`FOR UPDATE`** (strongest) | Exclusive and blocks *everything*, including `FOR KEY SHARE`. | The read-modify-write pattern: read a balance/counter/status, decide something, write it back, and you need no one else to interleave. Correct and common. Just know it is strictly stronger than the `UPDATE` it usually precedes — it blocks FK checks that the `UPDATE` itself would have let through. | Two things this table does *not* cover, and it's worth knowing the shape of both so you don't mistake them for row contention: - **Table-level locks** are a separate axis. `ALTER TABLE`, `VACUUM FULL`, `CREATE INDEX` (non-`CONCURRENTLY`) take `ACCESS EXCLUSIVE` and block every reader on the table. Those show up in `pg_locks` as `locktype = 'relation'`, and the diagnosis path below finds them the same way. - **`SERIALIZABLE`** solves a different problem. Locks stop conflicting writes to the *same row*; `SERIALIZABLE` catches conflicts that span *different* rows, and it does so by aborting a transaction rather than making it wait. See [transaction isolation](transaction-isolation.md) for that side. If the rows to be contested are known upfront, `FOR UPDATE` is the cheaper answer — no retry loop. ## Setup Two terminals, side by side. Everything below assumes you have Docker. ```bash docker run --name pg-locks --rm -e POSTGRES_PASSWORD=postgres -d postgres:16 ``` Terminal A: ```bash docker exec -it pg-locks psql -U postgres ``` Terminal B (a second, independent connection): ```bash docker exec -it pg-locks psql -U postgres ``` In **either** session, create the schema once: ```sql CREATE TABLE accounts (id int PRIMARY KEY, owner text, balance int NOT NULL); INSERT INTO accounts VALUES (1, 'alice', 500), (2, 'bob', 500); CREATE TABLE jobs (id int PRIMARY KEY, payload text, status text NOT NULL DEFAULT 'ready'); INSERT INTO jobs SELECT g, 'job-' || g, 'ready' FROM generate_series(1, 5) g; CREATE TABLE transfers (id serial PRIMARY KEY, account_id int REFERENCES accounts(id), amt int); ``` Turn on `\timing` in **both** sessions — the wall-clock duration of a statement is the whole point of this exercise: ``` \timing on ``` From here, "A" and "B" refer to the two `psql` sessions. Because A is the one that will be *holding* the lock (and therefore sitting idle in an open transaction, free to type), all the diagnostic queries get run from A while B hangs. ## Steps ### Part 1 — make a session block, then find out why What this part tests: the basic contention case from the mental model — A takes `FOR UPDATE` on a row, B tries to write it. First confirm the claim that readers are unaffected, then produce the block and trace it. 1. **A**: take the strongest row lock and hold it. ```sql BEGIN; SELECT * FROM accounts WHERE id = 1 FOR UPDATE; ``` ``` id | owner | balance ----+-------+--------- 1 | alice | 500 (1 row) Time: 1.793 ms ``` Don't commit. A is now idle inside an open transaction holding a row lock — an ordinary state for an app mid-request. 2. **Predict**: B runs a plain `SELECT` against that same row. Does it block? 3. **B**: `SELECT balance FROM accounts WHERE id = 1;` 4. **Observe**: ``` balance --------- 500 (1 row) Time: 3.363 ms ``` Instant. MVCC means B reads the last committed version without ever consulting a lock. This is worth pinning down before you go looking for contention: in Postgres, a slow `SELECT` is essentially never a locking problem. 5. **Predict**: now B tries to write it. Blocks, errors, or overwrites? 6. **B**: `UPDATE accounts SET balance = balance - 100 WHERE id = 1;` 7. **Observe**: nothing. No prompt, no error, no timeout. B hangs indefinitely — Postgres's default is to wait forever. 8. **A**: your session is still usable. Start where you would in production, with `pg_stat_activity`: ```sql SELECT pid, state, wait_event_type, wait_event, left(query, 42) AS query FROM pg_stat_activity WHERE datname = 'postgres' ORDER BY pid; ``` ``` pid | state | wait_event_type | wait_event | query -----+--------+-----------------+---------------+-------------------------------------------- 200 | active | Lock | transactionid | UPDATE accounts SET balance = balance - 10 201 | active | | | SELECT pid, state, wait_event_type, wait_e (2 rows) Time: 6.871 ms ``` B is pid 200 here and A is 201 — but don't read anything into the ordering. Pids come from the OS in connection order and B's can land either side of A's; identify the sessions by the `query` column, never by which number is larger. What matters is that B is `active`, not idle: it is running, and `wait_event_type = Lock` says what it's running is a wait. Note the `wait_event`: not `tuple`, not `row` — **`transactionid`**. Hold that thought. 9. **Predict**: `pg_stat_activity` names the *waiter*. What names the *holder*? 10. **A**: the single most useful function in this exercise: ```sql SELECT pid, pg_blocking_pids(pid) AS blocked_by, state, left(query, 38) AS query FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0; ``` 11. **Observe**: ``` pid | blocked_by | state | query -----+------------+--------+---------------------------------------- 200 | {201} | active | UPDATE accounts SET balance = balance (1 row) Time: 1.310 ms ``` `{201}` is A. One query, no joins, and it answers "who is stuck behind whom" for the entire cluster. It returns an *array* because a waiter can be behind several holders at once — several `FOR SHARE` holders, or a queue. Filtering on `cardinality(...) > 0` turns it into a standing "show me all contention right now" query; this is the one to keep in a snippet file. 12. **Predict**: `pg_locks` will show the actual lock objects. What is the row that B is waiting on — a `tuple` lock on `accounts`? 13. **A**: ```sql SELECT pid, locktype, relation::regclass AS relation, transactionid AS xid, mode, granted FROM pg_locks WHERE locktype IN ('relation','transactionid','tuple') AND (relation IS NULL OR relation = 'accounts'::regclass) ORDER BY pid, granted DESC; ``` 14. **Observe**: ``` pid | locktype | relation | xid | mode | granted -----+---------------+----------+-----+------------------+--------- 200 | relation | accounts | | RowExclusiveLock | t 200 | tuple | accounts | | ExclusiveLock | t 200 | transactionid | | 798 | ExclusiveLock | t 200 | transactionid | | 797 | ShareLock | f 201 | relation | accounts | | RowShareLock | t 201 | transactionid | | 797 | ExclusiveLock | t (6 rows) Time: 1.078 ms ``` Read the `granted` column first: exactly one row says `f`. That is the whole contention, and it is **`pid 200 wants ShareLock on transactionid 797`**. Now confirm what 797 is — **A**: ```sql SELECT pg_current_xact_id(); ``` ``` pg_current_xact_id -------------------- 797 (1 row) Time: 0.126 ms ``` So B is not waiting on a row. **B is waiting on A's transaction ID.** Every transaction holds an `ExclusiveLock` on its own xid for its entire life and releases it at commit or rollback (there is A's, on the last line). A row's `xmax` records which transaction has claimed it, so "wait for this row" is implemented as "take a `ShareLock` on whatever xid is in its `xmax`, which you'll get the moment that transaction ends." One lock object per transaction rather than per row — which is why Postgres can lock ten million rows in one statement without a lock table big enough to hold them. Two more details in that output are worth naming: - The `relation` locks are both *granted*. A holds `RowShareLock`, B holds `RowExclusiveLock`, and those two modes are compatible — the table-level locks that row operations take are deliberately weak, so the contention lands on the row and not on the table. - B's granted `tuple` `ExclusiveLock` is its place in the queue for this specific row. It's what stops a third waiter from jumping ahead when A commits. 15. **A**: `COMMIT;` 16. **Observe** — B returns the instant A commits: ``` UPDATE 1 Time: 84194.884 ms (01:24.195) ``` Eighty-four seconds. *That figure is illustrative, not a property of the query* — it is precisely how long A sat idle in its open transaction while the diagnostic queries above got typed, and yours will be whatever your own hold time was. That's the entire point: the duration of B's `UPDATE` is set by somebody else's transaction lifetime and by nothing about B. And it is the only visible symptom an application would ever get — no error, no warning, just a one-row `UPDATE` that took a minute and a half. ``` id | owner | balance ----+-------+--------- 1 | alice | 400 2 | bob | 500 (2 rows) Time: 2.108 ms ``` ### Part 2 — lock strength is a compatibility matrix What this part tests: the four modes from the mental model, in the only way that matters — which pairs actually conflict. The tool for probing this is `NOWAIT`, which turns "block forever" into an immediate error, so each combination takes a second instead of a staring contest. For each pair below: **A** runs `BEGIN;` plus the holding statement and leaves it open; **B** runs the probe; then **A** runs `ROLLBACK;` before the next pair. 17. **Predict**: fill in the matrix before you run it. Which of these five conflict? | A holds | B probes | | --- | --- | | `FOR KEY SHARE` | `FOR KEY SHARE NOWAIT` | | `FOR SHARE` | `FOR SHARE NOWAIT` | | `FOR NO KEY UPDATE` | `FOR KEY SHARE NOWAIT` | | `FOR NO KEY UPDATE` | `FOR SHARE NOWAIT` | | `FOR UPDATE` | `FOR KEY SHARE NOWAIT` | 18. **A/B**: run each pair. The probe form is `SELECT id FROM accounts WHERE id = 1 FOR NOWAIT;` 19. **Observe**: ``` A: FOR KEY SHARE B: FOR KEY SHARE NOWAIT -> granted (both share) A: FOR SHARE B: FOR SHARE NOWAIT -> granted (both share) A: FOR NO KEY UPDATE B: FOR KEY SHARE NOWAIT -> granted (compatible!) A: FOR NO KEY UPDATE B: FOR SHARE NOWAIT -> ERROR A: FOR UPDATE B: FOR KEY SHARE NOWAIT -> ERROR ``` where "granted" means the row came straight back — this one is the third pair, `FOR NO KEY UPDATE` held against a `FOR KEY SHARE` probe: ``` id ---- 1 (1 row) Time: 2.500 ms ``` and `ERROR` is, in full — this one the fifth pair, `FOR UPDATE` held against the same `FOR KEY SHARE` probe: ``` ERROR: could not obtain lock on row in relation "accounts" Time: 0.865 ms ``` All five probes returned in under 3.4 ms; `NOWAIT` never waits, whether it succeeds or fails. The third and fifth lines are the interesting ones, and they're the same probe against two locks that *feel* equivalent. `FOR NO KEY UPDATE` lets a `FOR KEY SHARE` through; `FOR UPDATE` doesn't. 20. **Predict**: a plain `UPDATE` of `balance` (not a key column) — is that `FOR NO KEY UPDATE` or `FOR UPDATE`? 21. **A**: `BEGIN; UPDATE accounts SET balance = balance - 1 WHERE id = 1;` **B**: probe with both `FOR KEY SHARE NOWAIT` and `FOR NO KEY UPDATE NOWAIT`. 22. **Observe**: ``` id ---- 1 (1 row) Time: 1.448 ms ERROR: could not obtain lock on row in relation "accounts" Time: 0.592 ms ``` The `FOR KEY SHARE` probe is granted; the `FOR NO KEY UPDATE` probe is refused. A plain `UPDATE` takes exactly `FOR NO KEY UPDATE`. Which means writing `SELECT ... FOR UPDATE` before an `UPDATE` — the reflex — takes a *stronger* lock than the `UPDATE` itself would. 23. **Predict**: does that extra strength cost anything real? Something has to actually want `FOR KEY SHARE` for it to matter. `transfers` has a foreign key to `accounts`. **A** holds a lock on account 1; **B** inserts a `transfers` row pointing at account 1 — no shared row between them, B never touches `accounts`. Blocks or not? 24. **A**: `BEGIN; UPDATE accounts SET balance = balance - 1 WHERE id = 1;` (leave open) — **B**: ```sql INSERT INTO transfers (account_id, amt) VALUES (1, 25); ``` 25. **Observe**: ``` INSERT 0 1 Time: 4.837 ms ``` No contention. Now the same insert while A holds `FOR UPDATE` instead. 26. **A**: `ROLLBACK;` then `BEGIN; SELECT id FROM accounts WHERE id = 1 FOR UPDATE;` — **B**: `SET lock_timeout = '3s';` then the same `INSERT`. 27. **Observe**: ``` ERROR: canceling statement due to lock timeout CONTEXT: while locking tuple (0,3) in relation "accounts" SQL statement "SELECT 1 FROM ONLY "public"."accounts" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x" Time: 3004.264 ms (00:03.004) ``` That `SQL statement` line is Postgres showing you its own internals: the foreign key check *is* a `SELECT ... FOR KEY SHARE` on the parent row, issued on your behalf. So `SELECT ... FOR UPDATE` on a parent row blocks every concurrent insert of a child row referencing it — while the `UPDATE` it was protecting would not have. On a busy parent row (a tenant, an account, a product) that is a real and frequently-surprising source of contention, and `FOR NO KEY UPDATE` fixes it with a one-word change. 28. **A**: `ROLLBACK;` ### Part 3 — the three escape hatches What this part tests: waiting forever is a *default*, not a law. Postgres offers three ways out, and they are not variations on each other — they answer three different questions. 29. **`NOWAIT`** — you already used it. Fail instantly rather than queue. Reach for it when a wait is meaningless: an interactive request that will time out anyway, where a fast error beats a slow success. 30. **`lock_timeout`** — wait, but bounded. **A**: `BEGIN; SELECT id FROM accounts WHERE id = 1 FOR UPDATE;` — **B**: ```sql SET lock_timeout = '2s'; UPDATE accounts SET balance = balance - 100 WHERE id = 1; ``` 31. **Observe**: ``` ERROR: canceling statement due to lock timeout CONTEXT: while updating tuple (0,3) in relation "accounts" Time: 2003.994 ms (00:02.004) ``` Two seconds on the nose. The most valuable place for this is *not* in application code — it's on your own maintenance statements. A `SET lock_timeout = '2s';` before an `ALTER TABLE` means the migration fails fast instead of parking an `ACCESS EXCLUSIVE` request in front of every reader on the table. **A**: `ROLLBACK;` 32. **`SKIP LOCKED`** — take a *different* row instead of waiting for this one. This is the job-queue pattern, and it is why "we need a real message broker" is often premature. 33. **Predict**: two workers each claim two ready jobs, at the same time, with the same query. Worker 2 runs while worker 1 is still holding its two. What does worker 2 get? 34. **A** (worker 1): ```sql BEGIN; SELECT id, payload FROM jobs WHERE status = 'ready' ORDER BY id LIMIT 2 FOR UPDATE SKIP LOCKED; ``` ``` id | payload ----+--------- 1 | job-1 2 | job-2 (2 rows) Time: 2.735 ms ``` **B** (worker 2), while A is still open — the identical query: 35. **Observe**: ``` id | payload ----+--------- 3 | job-3 4 | job-4 (2 rows) Time: 1.736 ms ``` Jobs 3 and 4, in under two milliseconds. The number to compare it against is Part 1's `UPDATE`: same kind of contention, and there the wait was the holder's whole transaction lifetime. Here there is no wait at all. No coordination, no broker, no risk of two workers taking the same job. `ORDER BY id LIMIT 2` was evaluated, then rows already locked were silently dropped and the scan continued. Add `UPDATE jobs SET status = 'done' WHERE id = ANY(...)` before each `COMMIT` and you have a correct work queue in one statement. Note the failure semantics you get for free: if a worker crashes mid-job its transaction aborts, its locks vanish, and the job becomes claimable again. 36. **A**, **B**: `COMMIT;` ### Part 4 — a real deadlock What this part tests: what happens when the waiting is *circular*. Nothing above can resolve it — both sides are waiting on the other's xid, and both would wait forever. This part needs precise ordering, so follow it literally. 37. **A**: `BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1;` 38. **B**: `BEGIN; UPDATE accounts SET balance = balance - 50 WHERE id = 2;` Both succeed instantly — different rows, no conflict yet. A holds row 1, B holds row 2. 39. **A**: `UPDATE accounts SET balance = balance + 100 WHERE id = 2;` → A blocks, waiting on B. 40. **Predict**: B now reaches for row 1, which A holds. Both sessions are waiting on each other. What happens — do both hang forever? 41. **B**: `UPDATE accounts SET balance = balance + 50 WHERE id = 1;` 42. **Observe**, after roughly one second: ``` ERROR: deadlock detected DETAIL: Process 200 waits for ShareLock on transaction 816; blocked by process 201. Process 201 waits for ShareLock on transaction 817; blocked by process 200. HINT: See server log for query details. CONTEXT: while updating tuple (0,3) in relation "accounts" Time: 1002.675 ms (00:01.003) ``` Postgres found the cycle and killed one side. (This exercise stops at *detecting* it. The fix — having both sessions acquire their rows in a consistent order, so the cycle can never close — is Step 4 of [ddia/ch08/deadlock-detection](../ddia/ch08/deadlock-detection.md), which reproduces this same interleaving from Kleppmann's side.) Read the `DETAIL` closely: it is the Part 1 output in miniature — two `ShareLock` requests on two transaction IDs, forming a loop. The deadlock detector is nothing more than a cycle search over exactly the wait-for edges you queried by hand with `pg_blocking_pids()`. Three details are easy to miss: - **1002 ms.** Detection is not instant and not continuous. When a session begins waiting it sets a timer for `deadlock_timeout` (default `1s`) and only *then* looks for a cycle — because building the wait-for graph is expensive and the overwhelming majority of waits resolve on their own well inside a second. The measured figure lands a few milliseconds over the timeout every time. - **B died, not A** — B being the session whose request *closed* the cycle and therefore the one that ran the detector. That is what happens here, and it is the natural outcome for a simple two-session cycle. Do not build on it: §13.3.4 says outright that *"exactly which transaction will be aborted is difficult to predict and should not be relied upon."* Any transaction you write must be prepared to be the victim. - B's whole transaction is gone, not just the statement. Any further command in it returns ``` ERROR: current transaction is aborted, commands ignored until end of transaction block Time: 0.202 ms ``` until you `ROLLBACK`. 43. **Observe** — A, meanwhile: ``` UPDATE 1 Time: 2512.288 ms (00:02.512) ``` A's blocked update *completed*, unblocked by the victim's rollback releasing row 2. (The 2512 ms is illustrative: it is however long A had already been waiting when the cycle closed, plus the one-second detection.) That's the design: the deadlock is broken by sacrificing exactly one transaction, and the survivor never learns anything happened beyond a slow query. **A**: `COMMIT;` 44. **A**: the server log has more than the client got: ```bash docker logs pg-locks 2>&1 | grep -A8 'deadlock detected' ``` ``` 2026-07-25 16:18:28.442 UTC [200] ERROR: deadlock detected 2026-07-25 16:18:28.442 UTC [200] DETAIL: Process 200 waits for ShareLock on transaction 816; blocked by process 201. Process 201 waits for ShareLock on transaction 817; blocked by process 200. Process 200: UPDATE accounts SET balance = balance + 50 WHERE id = 1; Process 201: UPDATE accounts SET balance = balance + 100 WHERE id = 2; 2026-07-25 16:18:28.442 UTC [200] HINT: See server log for query details. 2026-07-25 16:18:28.442 UTC [200] CONTEXT: while updating tuple (0,3) in relation "accounts" 2026-07-25 16:18:28.442 UTC [200] STATEMENT: UPDATE accounts SET balance = balance + 50 WHERE id = 1; ``` (Log timestamps are the container's UTC clock, not your local time.) The log adds the two SQL statements the client's `HINT` promised — two extra `Process NNN:` lines inside `DETAIL` that the client never sees. In production this is the artifact you want: it names both statements, and the fix for almost every real deadlock is visible from those two lines alone — **make every transaction take its locks in a consistent order** (here: always ascending `id`). Both transactions would then have queued behind one another instead of crossing. ## What you should see A plain `SELECT` that never blocks, and an `UPDATE` on the same row that blocks for exactly as long as the other session's transaction lives — 84195 ms on this run, reported as nothing but a slow query. `pg_blocking_pids()` naming the holder in one line. A `pg_locks` row with `granted = f` showing that the wait is on a **transaction ID**, not on the row. `FOR NO KEY UPDATE` letting a `FOR KEY SHARE` probe through where `FOR UPDATE` rejects it — and that difference turning into a real three-second stall of a foreign-key insert, with Postgres printing its own internal `... FOR KEY SHARE OF x` check in the error context. Two workers claiming disjoint job sets in under two milliseconds via `SKIP LOCKED`, against a wait of a minute and a half for the same contention without it. And a deadlock detected 1002 ms after the cycle closed, killing the session that closed it while the other transaction quietly succeeds. ## Why Because Postgres implements row locking with almost no dedicated lock state. Instead of a lock manager entry per locked row, the claim is written into the row itself — the locking transaction stamps its ID into the tuple's `xmax` — and the only shared-memory lock object involved is the one every transaction already holds on its own transaction ID. Waiting for a row therefore reduces to waiting for a transaction, which is why `pg_locks` showed `ShareLock on transactionid 797` and why locking ten million rows costs no more lock-manager memory than locking one. It also explains the release semantics you observed: there is no `UNLOCK`, and locks cannot be released early, because "released" means "that transaction ended." The four lock strengths exist because Postgres needs the weak ones internally. Foreign-key validation has to pin a parent row's *key* while it checks it, but blocking every unrelated update of that parent would be disastrous on a hot row — so `FOR KEY SHARE` exists to conflict only with things that change keys, and `FOR NO KEY UPDATE` exists so ordinary updates can declare that they don't. The user-facing modes are the same mechanism exposed. That's the whole reason the reflexive `FOR UPDATE` overshoots: you're asking for protection against key changes you weren't worried about. And deadlock detection is deliberately lazy because the cheap thing to do is nothing. Detecting a cycle requires assembling a global wait-for graph under a lock; almost every wait ends by itself in milliseconds. So Postgres waits `deadlock_timeout` first, and only pays for the graph in the rare case where the wait looks pathological. The cost of that choice is that a genuine deadlock always burns at least a second before anyone finds out — which is why the fix is ordering your locks, not tuning the detector. **Where the effect vanishes.** All of this is invisible until two writers want the same row inside overlapping transactions. Shorten transactions to a single statement and there is nothing to wait on; shard the contended row (a counter split into N rows) and the queue disappears; make the workload read-only and no lock is ever taken at all. Contention is not a property of your query — Part 1's `UPDATE` was as cheap a statement as exists — it is a property of how long somebody else's transaction stays open. That is why the 84-second figure is meaningless as a benchmark and total as a diagnosis. ## Go deeper - Queue three sessions on the same row and re-run `pg_blocking_pids()`. The third waiter reports being blocked by *both* of the others, and `pg_locks` shows the `tuple` lock doing its job: waiters are served first-come-first-served rather than in a stampede. - Deadlock two sessions that only ever hold `FOR SHARE` locks: both take `FOR SHARE` on the same row (compatible, both succeed), then both try to `UPDATE` it. Neither ever touched a second row, yet it deadlocks — a shared lock is not a reservation to upgrade, and this is the standard argument for `FOR UPDATE` over `FOR SHARE` in read-modify-write code. - Watch a *table*-level block: run `ALTER TABLE accounts ADD COLUMN x int;` in B while A holds an open transaction that merely `SELECT`ed from `accounts`. B blocks on an `ACCESS EXCLUSIVE` relation lock — and every subsequent plain `SELECT` from any session now queues behind B, since they can't jump the lock queue. This is the classic "one migration froze the whole site" incident, and `lock_timeout` from Part 3 is the standard guard. - Set `deadlock_timeout = '10s'` and repeat Part 4. Detection takes ten seconds, which is a good way to feel why the default isn't higher — and turn on `log_lock_waits` to have Postgres log every wait that exceeds the timeout, which is the production setting that turns invisible contention into a searchable log line. - Build the queue from Part 3 properly: add `UPDATE jobs SET status = 'done'` inside the transaction, run several worker loops, and kill one mid-job to watch its rows become claimable again. Compare with the naive `SELECT ... LIMIT 1` + `UPDATE` version, which hands the same job to every worker. - **Point the Part 1 toolkit at a blocker that has no session.** [ddia/ch08/prepared-transaction-locks](../ddia/ch08/prepared-transaction-locks.md) builds the pathological case this exercise's diagnosis path exists for: a `PREPARE TRANSACTION` that holds its row lock while owned by *nobody*, so `pg_stat_activity` has no row to find and `pg_blocking_pids()` comes back empty. Predict where the holder shows up instead (`pg_prepared_xacts`), and whether Part 3's three escape hatches help the blocked writer at all. Then predict what a `docker restart` does to it — the answer is nothing, which is the whole point. - **The same deadlock, ending in the fix instead of the detector.** [ddia/ch08/deadlock-detection](../ddia/ch08/deadlock-detection.md) runs Part 4's exact interleaving and then closes it: both sessions take row 1 before row 2, and the cycle becomes unconstructible. Run them back to back — this one teaches you to *find* a deadlock in production, that one teaches you to make it impossible. Neither substitutes for the other. - Read the two reference tables this exercise is a hands-on version of: https://www.postgresql.org/docs/16/explicit-locking.html — the row-level lock compatibility matrix there is exactly what Part 2 measures, and the table-level matrix above it is the other axis. ## Cleanup ```bash docker stop pg-locks ``` The container was started with `--rm`, so stopping it removes the container *and* the anonymous volume `postgres:16` creates for `/var/lib/postgresql/data`. Confirm nothing is left behind: ```bash docker ps -a --filter name=pg-locks ``` ``` CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES ```