Lock Contention

One session blocks, and Postgres tells you exactly who is waiting, on whom, and for what — ending in a real deadlock, detected and killed in front of you.

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
• 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
• 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 (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 SELECTs 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 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.)

Setup

Two terminals, side by side. Everything below assumes you have Docker.

docker run --name pg-locks --rm -e POSTGRES_PASSWORD=postgres -d postgres:16

Terminal A:

docker exec -it pg-locks psql -U postgres

Terminal B (a second, independent connection):

docker exec -it pg-locks psql -U postgres

In either session, create the schema once:

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.

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.

BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

SELECT balance FROM accounts WHERE id = 1;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;

SELECT pid, state, wait_event_type, wait_event, left(query, 42) AS query
FROM pg_stat_activity WHERE datname = 'postgres' ORDER BY pid;

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;

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;

SELECT pg_current_xact_id();

COMMIT;

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: Session A runs BEGIN; plus the holding statement and leaves it open; Session B runs the probe (SELECT id FROM accounts WHERE id = 1 FOR <mode> NOWAIT;); then A runs ROLLBACK; before the next pair.

BEGIN; UPDATE accounts SET balance = balance - 1 WHERE id = 1;

SELECT id FROM accounts WHERE id = 1 FOR KEY SHARE NOWAIT;
SELECT id FROM accounts WHERE id = 1 FOR NO KEY UPDATE NOWAIT;

BEGIN; UPDATE accounts SET balance = balance - 1 WHERE id = 1;

INSERT INTO transfers (account_id, amt) VALUES (1, 25);

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.

BEGIN; SELECT id FROM accounts WHERE id = 1 FOR UPDATE;

SET lock_timeout = '2s';
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

BEGIN;
SELECT id, payload FROM jobs WHERE status = 'ready'
ORDER BY id LIMIT 2 FOR UPDATE SKIP LOCKED;

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.

BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1;

BEGIN; UPDATE accounts SET balance = balance - 50 WHERE id = 2;

UPDATE accounts SET balance = balance + 100 WHERE id = 2;

UPDATE accounts SET balance = balance + 50 WHERE id = 1;

docker logs pg-locks 2>&1 | grep -A8 'deadlock detected'

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.

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

Sources: PostgreSQL 16 Docs §13.3: Explicit Locking (the row-level lock compatibility matrix there is exactly what Part 2 measures) · §54.12: pg_locks for the transaction-ID wait · §28.2: The Cumulative Statistics System for pg_stat_activity's wait-event columns

Cleanup

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:

docker ps -a --filter name=pg-locks