Concurrencyrow locktable lockfor updateshared lockexclusive lock

Locks and Deadlocks

Row and table locks serialise conflicting writers; a deadlock is a cycle in who-waits-for-whom, which the database detects and breaks by killing one side — and which you prevent by acquiring locks in a consistent order.

▶ Interactive Interview question See how this works internally →

Progress

What gets locked, and by what

UPDATE, DELETE and SELECT … FOR UPDATE take an exclusive row lock on each row they touch, held until the transaction ends. A second transaction wanting the same row waits. SELECT … FOR SHARE takes a shared row lock: many readers may hold it, and it blocks a writer. Plain SELECT takes no row locks at all under MVCC — readers read versions.

Table locks are taken by DDL. ALTER TABLE … ADD COLUMN needs an exclusive lock on the whole table and waits for every running query on it to finish — and every new query waits behind it. A "quick" migration that queues behind one long report can freeze an application. CREATE INDEX CONCURRENTLY, lock_timeout, and doing DDL in short steps are how you avoid that.

Deadlock

A holds row 1 and wants row 2. B holds row 2 and wants row 1. Neither can proceed; waiting will never resolve it. The database keeps a wait-for graph — an edge from each waiter to the holder — and after deadlock_timeout (1 s) checks it for a cycle. If it finds one, it aborts one transaction with 40P01 deadlock detected, releasing its locks so the other can finish. This is *detection*, not prevention: the database lets deadlocks happen and then breaks them.

Your job is to make the cycle impossible. Consistent lock order: if every transaction locks rows in ascending id, no cycle can form — SELECT … WHERE id IN (1, 2) ORDER BY id FOR UPDATE. Short transactions: a lock held for two milliseconds rarely collides. Fail fast: FOR UPDATE NOWAIT errors instead of waiting; SKIP LOCKED moves on to the next unlocked row, which is the entire basis of a database-backed work queue.

Ordered locking, and a queue that never deadlocks

1-- ordered: both transfers lock the lower id first
2BEGIN;
3SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
4UPDATE accounts SET balance = balance - 50 WHERE id = 1;
5UPDATE accounts SET balance = balance + 50 WHERE id = 2;
6COMMIT;
7 
8-- work queue: each worker grabs a different job, nobody waits
9UPDATE jobs SET started_at = now(), worker = $1
10WHERE id = (
11  SELECT id FROM jobs WHERE started_at IS NULL
12  ORDER BY created_at LIMIT 1 FOR UPDATE SKIP LOCKED
13) RETURNING id;

Pessimistic vs optimistic

Pessimistic locking takes the lock before reading: SELECT … FOR UPDATE. Others wait. Correct by construction; the costs are contention and deadlock risk. Right when conflicts are likely and a retry would be expensive or user-visible — money, inventory, seat assignment.

Optimistic locking takes no lock. Read a version number with the row; write with WHERE id = ? AND version = ? and increment it; zero rows updated means someone else got there first, so re-read and retry. No waiting, no deadlocks, at the cost of a retry loop and wasted work under contention. Right when conflicts are rare — editing a profile, saving a document.

The same choice appears at every layer of the stack: locks vs compare-and-swap, mutexes vs versioned writes, Serializable-with-retry vs FOR UPDATE. Pick by expected conflict rate.

Key points

Writers take exclusive row locks until commit; readers take none. DDL takes table locks that queue everything.
Deadlock = cycle in the wait-for graph; the database detects it and kills a victim. Prevent with consistent lock order and short transactions.
NOWAIT fails fast; SKIP LOCKED is how work queues work.
Pessimistic when conflicts are likely and expensive; optimistic when they are rare.

Locks and the deadlock cycle

Locks, waiting, and the deadlock cycle

Two transfers touching the same two accounts in opposite orders. Watch the wait-for graph close.

t	Transaction A (1 → 2)	Transaction B (2 → 1)
1	BEGIN	BEGIN
2	UPDATE accounts SET balance = balance - 50 WHERE id = 1;
3		UPDATE accounts SET balance = balance - 30 WHERE id = 2;
4	UPDATE accounts SET balance = balance + 50 WHERE id = 2;
5		UPDATE accounts SET balance = balance + 30 WHERE id = 1;
6		ERROR: deadlock detected