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.

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 — readers never block writers and writers never block readers in Postgres, at any of these levels. These modes only conflict with each other.

Setup

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

docker run --name pg-locks --rm -e POSTGRES_PASSWORD=postgres -p 5432:5432 -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 — 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 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.

Sources: PostgreSQL Docs: Explicit Locking (the row-level lock compatibility matrix there is exactly what Part 2 measures) · PostgreSQL Docs: The Statistics Collector for pg_stat_activity's wait-event columns

Cleanup

docker stop pg-locks