PostgreSQL in Practice #6 Locks and Concurrency: Deadlocks, DDL Locks, SKIP LOCKED

Infrastructure PostgreSQL Database
4 min read

Basics chapter 6 concluded that “thanks to MVCC, reads and writes never block each other.” What remains is write versus write. When two transactions try to modify the same row at once, locks enter — and this is where production incidents live: sudden waits, deadlocks, and the migration that stops the service.

The two layers of locking #

  • Row locks: UPDATE and DELETE lock their target rows. Another transaction trying to modify the same row waits until the first finishes. Different rows don’t interact. Row-lock trouble therefore usually lives on “hot rows” — the few rows everyone modifies (aggregate counters, the inventory row of a popular product).
  • Table locks: the layer DDL takes. As seen in practice #1, ALTER TABLE’s ACCESS EXCLUSIVE blocks even SELECTs, and the scarier phenomenon was every query queueing behind a waiting DDL. lock_timeout was the insurance.

One principle to keep: locks release when the transaction ends. The statement finishing doesn’t release anything if the transaction stays open. That’s the third reason practice #3’s idle in transaction is dangerous (after lock retention and VACUUM blockage).

Who is blocking whom #

When “the query won’t finish” comes in mid-incident, trace the chain of waits.

Check lock waits 
-- queries waiting on locks, and the pids blocking them
SELECT pid, pg_blocking_pids(pid) AS blocked_by,
       state, now() - query_start AS waiting_for,
       left(query, 60) AS query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

pg_blocking_pids() returns “the pids blocking this pid” directly, so you can find the root of the chain without hand-joining the raw pg_locks view. If the root is an idle-in-transaction session or a runaway query, SELECT pg_terminate_backend(pid); is the last resort.

Deadlocks: structure and prevention #

A deadlock is two transactions each waiting on a lock the other holds. The structure is always the same: A holds row 1 and wants row 2, while B holds row 2 and wants row 1. PostgreSQL detects this and kills one side with an error, so the system never freezes forever — but the killed side’s request fails.

The prevention rule falls straight out of the structure: when modifying multiple rows (or tables), always lock them in the same order. For transfer logic, that means not “sender first” but a global order like “lower account id first.” Even a single statement updating several rows gives no guarantee about lock order within WHERE id IN (...), so order-sensitive logic locks with an explicit sort.

Pin the lock order 
-- pin the lock order explicitly
SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;

SELECT FOR UPDATE: the safety latch for read-decide-write #

Basics chapter 6 said read-decide-write logic should first be collapsed into one atomic statement. When one statement isn’t enough (the read feeds a complex computation), the tool is SELECT ... FOR UPDATE. It locks the rows at read time with the same strength as an UPDATE, so nothing can slip in between your decision and your write. The usage rules: lock the minimum range, keep the transaction short.

SKIP LOCKED: a work queue made of a database #

One derivative option of FOR UPDATE created an entire pattern: “skip locked rows and give me the next one” — SKIP LOCKED.

Work queue query 
-- multiple workers can run this concurrently and each picks a different job
UPDATE jobs SET status = 'running', started_at = now()
WHERE id = (
    SELECT id FROM jobs
    WHERE status = 'queued'
    ORDER BY created_at
    LIMIT 1
    FOR UPDATE SKIP LOCKED
)
RETURNING *;

Ten workers can run the same query at once; each grabs the first unlocked row, so there is neither duplicate processing nor waiting. Before standing up a separate message queue — if what you need is a work queue that participates atomically in your transactions — this pattern is PostgreSQL’s standard answer.

Summary #

  • The conflict MVCC leaves behind is write versus write. Row locks act up on hot rows; table locks act up on DDL.
  • Locks release when the transaction ends. Short transactions prevent half of all locking problems.
  • Trace waits to the root with pg_blocking_pids; the last resort is pg_terminate_backend.
  • Deadlock prevention reduces to one rule: a global lock order, always the same.
  • Read-decide-write gets FOR UPDATE; work queues get FOR UPDATE SKIP LOCKED. Next chapter: partitioning.
X