postgres://internals

Locks & Deadlocks

// the problem

MVCC means readers never block writers and writers never block readers — but two transactions that write the same row still have to take turns. That's a lock. Usually it's invisible; occasionally two transactions each hold what the other needs, wait forever, and Postgres has to shoot one of them. Let's make locks visible and manufacture a deadlock on purpose.

Two transactions, two rows. Lock rows in different orders and watch one block — then deadlock.

fig.01 · row locks & deadlock

lock rows to see who blocks whom

Txn Aactive

holds: —

Txn Bactive

holds: —

lock table

row 1held by—waiting—

row 2held by—waiting—

try: A locks 1 · B locks 2 · A locks 2 (waits) · B locks 1 → deadlock

// note · reproduce the classic deadlock

A → Lock row 1, B → Lock row 2, then A → Lock row 2 (A now waits for B), then B → Lock row 1. Now each transaction holds what the other wants: a cycle. Postgres's deadlock detector notices and aborts one so the other can proceed. Do the same sequence but with both transactions locking 1 then 2 — no deadlock, just a normal wait.

What actually takes a lock

Reads under MVCC take no row locks at all. Locks appear when you write or explicitly ask:

UPDATE / DELETE take an exclusive lock on each affected row until the transaction ends.
SELECT … FOR UPDATE locks the rows you read, so nobody can change them before you do (the standard “read-then-update” pattern).
SELECT … FOR SHARE is weaker — others can still read, but not update.

Every lock a transaction holds is visible in pg_locks:

sql · live postgresrun me first

DROP TABLE IF EXISTS account;
CREATE TABLE account (id int primary key, balance int);
INSERT INTO account VALUES (1, 100), (2, 200);

BEGIN;
SELECT id, balance FROM account WHERE id = 1 FOR UPDATE;  -- lock row 1
SELECT locktype, mode, granted FROM pg_locks
WHERE locktype IN ('relation','transactionid','tuple')
ORDER BY locktype;
COMMIT;

DROP TABLE IF EXISTS account;
CREATE TABLE account (id int primary key, balance int);
INSERT INTO account VALUES (1, 100), (2, 200);

BEGIN;
SELECT id, balance FROM account WHERE id = 1 FOR UPDATE;  -- lock row 1
SELECT locktype, mode, granted FROM pg_locks
WHERE locktype IN ('relation','transactionid','tuple')
ORDER BY locktype;
COMMIT;

⌘/ctrl + enter

Row locks don't each get their own entry in shared memory — that wouldn't scale — so a locked row is marked on the tuple itself, and pg_locks shows the transaction-level and relation-level locks that back it.

Waiting, and the deadlock detector

When you request a lock someone else holds, your transaction simply waits in a queue. Most of the time the holder commits a moment later and you proceed — that's normal and fine. A deadlock is the pathological case: a cycle of waiters. Postgres doesn't wait forever — after deadlock_timeout (1 s by default) a backend that's still waiting runs the detector, finds the cycle, and aborts one transaction (the victim gets a deadlock detected error). The survivor continues.

// why it matters · the golden rule: lock in a consistent order

Deadlocks almost always come from transactions grabbing the same rows in different orders. If every transaction touches rows (and tables) in the same order — e.g. always the lower account id first — a cycle can never form. This one discipline prevents the vast majority of deadlocks.

Skipping and not-waiting

Sometimes you don't want to wait at all:

FOR UPDATE NOWAIT — fail immediately instead of blocking.
FOR UPDATE SKIP LOCKED — ignore already-locked rows and take the rest. This is the backbone of queue/worker patterns: many workers each SELECT … FOR UPDATE SKIP LOCKED LIMIT 1 and never contend on the same job.

sql · live postgreseditable — run it

-- a work-queue grab: take an unlocked row, skip any a peer already holds
SELECT id FROM account ORDER BY id
FOR UPDATE SKIP LOCKED
LIMIT 1;

-- a work-queue grab: take an unlocked row, skip any a peer already holds
SELECT id FROM account ORDER BY id
FOR UPDATE SKIP LOCKED
LIMIT 1;

⌘/ctrl + enter

Advisory locks: your own mutexes

Sometimes you need a lock that isn't tied to a row at all — a mutex around some application-level operation (“only one worker rebuilds this cache”). Advisory locks are arbitrary integer locks Postgres tracks for you:

sql · live postgreseditable — run it

SELECT pg_try_advisory_lock(42) AS got_it;   -- true if acquired, false if held by another session
SELECT pg_advisory_unlock(42)  AS released;

SELECT pg_try_advisory_lock(42) AS got_it;   -- true if acquired, false if held by another session
SELECT pg_advisory_unlock(42)  AS released;

⌘/ctrl + enter

Your turn

// challengewrite it yourself

Lock row 1 of the account table for a pending update, returning its id and balance. (Wrap it in a transaction.)

BEGIN;
SELECT id, balance FROM account WHERE id = 1 FOR UPDATE;
COMMIT;

BEGIN;
SELECT id, balance FROM account WHERE id = 1 FOR UPDATE;
COMMIT;

⌘/ctrl + enter

// challengewrite it yourself

Grab one job from the queue without ever waiting on a row another worker already holds. Return its id.

SELECT id FROM account ORDER BY id
FOR UPDATE SKIP LOCKED
LIMIT 1;

SELECT id FROM account ORDER BY id
FOR UPDATE SKIP LOCKED
LIMIT 1;

⌘/ctrl + enter

// what you now understand

01MVCC removes read/write blocking, but two transactions writing the same row serialize — the second waits on a row lock.
02UPDATE/DELETE and SELECT FOR UPDATE take row locks held until the transaction ends; pg_locks shows the backing locks.
03A deadlock is a cycle of waiters; after deadlock_timeout Postgres detects it and aborts one victim with a 'deadlock detected' error.
04Deadlocks come from inconsistent lock ordering — always acquire rows/tables in the same order to prevent them.
05FOR UPDATE NOWAIT fails instead of waiting; SKIP LOCKED ignores locked rows — the basis of SELECT-FOR-UPDATE work queues.
06Advisory locks are arbitrary integer mutexes for application-level coordination, independent of any row.

// self-test

Two transactions deadlock. What does Postgres do?

// self-test

Your service occasionally deadlocks when transferring between two accounts. What's the most reliable fix?

// go deeper

Explicit Locking (official docs) — row/table lock modes, the conflict matrix, advisory locks
Deadlocks — detection, deadlock_timeout, avoidance
pg_locks view — inspecting held and awaited locks
SELECT … FOR UPDATE / SKIP LOCKED — locking clauses and the queue pattern

nextConnections & Pooling