# 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. ## 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 — **readers never block writers and writers never block readers** in Postgres, at any of these levels. These modes only conflict with each other. | 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](postgres-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 -p 5432:5432 -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.727 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: 1.691 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 -----+--------+-----------------+---------------+-------------------------------------------- 262 | active | Lock | transactionid | UPDATE accounts SET balance = balance - 10 263 | active | | | SELECT pid, state, wait_event_type, wait_e (2 rows) ``` B (pid 262 here — pids are assigned by the OS, so B's can be lower than A's) 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 -----+------------+--------+---------------------------------------- 262 | {263} | active | UPDATE accounts SET balance = balance (1 row) ``` `{263}` 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 -----+---------------+----------+-----+------------------+--------- 262 | relation | accounts | | RowExclusiveLock | t 262 | tuple | accounts | | ExclusiveLock | t 262 | transactionid | | 770 | ExclusiveLock | t 262 | transactionid | | 769 | ShareLock | f 263 | relation | accounts | | RowShareLock | t 263 | transactionid | | 769 | ExclusiveLock | t (6 rows) ``` Read the `granted` column first: exactly one row says `f`. That is the whole contention, and it is **`pid 262 wants ShareLock on transactionid 769`**. Now confirm what 769 is — **A**: `SELECT pg_current_xact_id();` → `769`. 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: 6109.447 ms (00:06.109) ``` Six seconds, which is exactly how long A sat there. That number is the only visible symptom an application would ever get: no error, no warning, just a query that took as long as somebody else's transaction. ``` id | owner | balance ----+-------+--------- 1 | alice | 400 2 | bob | 500 ``` ### 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 -> 1 (both share) A: FOR SHARE B: FOR SHARE NOWAIT -> 1 (both share) A: FOR NO KEY UPDATE B: FOR KEY SHARE NOWAIT -> 1 (compatible!) A: FOR NO KEY UPDATE B: FOR SHARE NOWAIT -> ERROR A: FOR UPDATE B: FOR KEY SHARE NOWAIT -> ERROR ``` where `ERROR` is, in full: ``` ERROR: could not obtain lock on row in relation "accounts" ``` 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**: ``` B: FOR KEY SHARE NOWAIT -> 1 B: FOR NO KEY UPDATE NOWAIT -> ERROR: could not obtain lock on row in relation "accounts" ``` 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: 1.596 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**: ``` Time: 3003.384 ms (00:03.003) ERROR: canceling statement due to lock timeout CONTEXT: while locking tuple (0,8) in relation "accounts" SQL statement "SELECT 1 FROM ONLY "public"."accounts" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x" ``` That last 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**: ``` Time: 2002.359 ms (00:02.003) ERROR: canceling statement due to lock timeout CONTEXT: while updating tuple (0,3) in relation "accounts" ``` 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 ``` **B** (worker 2), while A is still open — the identical query: 35. **Observe**: ``` id | payload ----+--------- 3 | job-3 4 | job-4 (2 rows) Time: 0.618 ms ``` Jobs 3 and 4, in 0.6 ms. No coordination, no broker, no risk of two workers taking the same job — and crucially, no waiting. `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 405 waits for ShareLock on transaction 787; blocked by process 406. Process 406 waits for ShareLock on transaction 788; blocked by process 405. HINT: See server log for query details. CONTEXT: while updating tuple (0,7) in relation "accounts" Time: 1004.057 ms (00:01.004) ``` Postgres found the cycle and killed one 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: - **1004 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. - **B died, not A.** The victim is the session whose request *closed* the cycle, i.e. the one that ran the detector and found itself in it. - 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` until you `ROLLBACK`. 43. **Observe** — A, meanwhile: ``` UPDATE 1 Time: 3008.850 ms (00:03.009) ``` A's blocked update *completed*, unblocked by the victim's rollback releasing row 2. 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' ``` ``` ERROR: deadlock detected DETAIL: Process 405 waits for ShareLock on transaction 787; blocked by process 406. Process 406 waits for ShareLock on transaction 788; blocked by process 405. Process 405: UPDATE accounts SET balance = balance + 50 WHERE id = 1; Process 406: UPDATE accounts SET balance = balance + 100 WHERE id = 2; ``` The log adds the two SQL statements the client's `HINT` promised. 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 — 6109 ms, 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 a millisecond via `SKIP LOCKED`. And a deadlock detected 1004 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 769` 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. ## 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. - Read the two reference tables this exercise is a hands-on version of: https://www.postgresql.org/docs/current/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 ```