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 only when you write, or explicitly ask. There are four row-lock strengths, strongest to weakest:

  • FOR UPDATE — taken by UPDATE/DELETE and SELECT … FOR UPDATE. The exclusive one: nobody else may lock, update, or delete the row.
  • FOR NO KEY UPDATE — a slightly weaker exclusive lock, taken by updates that change no key column; it conflicts with less.
  • FOR SHARE — shared: others may also FOR SHARE-read it, but none may update it.
  • FOR KEY SHARE — the weakest; it only prevents the row's key from changing. This is what a foreign key quietly takes on a parent row so it can't be deleted out from under a child.

Here's what a FOR UPDATE actually registers:

sql · live postgresrun me first — the locks a FOR UPDATE holds
⌘/ctrl + enter

Notice there is no per-row entry — just a RowShareLock on the table and a lock on the transaction's own id. That's deliberate: tracking every locked row in shared memory couldn't scale to millions of locks. Instead a row lock is written onto the tuple itself — Postgres stamps the row's xmax with the locking transaction's id (plus an infomask bit meaning "locked, not deleted"). See it:

sql · live postgresthe row lock lives in the tuple's xmax
⌘/ctrl + enter

A would-be writer reads that xmax, sees a live transaction holds it, and waits. No shared-memory entry per row — the lock is a few bits in the tuple header.

Every statement also locks the table

Beyond row locks, every statement takes a table-level lock — a weak one you never notice until a strong one collides with it. SELECT takes ACCESS SHARE; INSERT/UPDATE/DELETE take ROW EXCLUSIVE; CREATE INDEX takes SHARE; and ALTER TABLE, DROP, TRUNCATE, and VACUUM FULL take ACCESS EXCLUSIVE — which conflicts with everything, including a plain SELECT. Watch the mode change with the statement:

sql · live postgresa read takes ACCESS SHARE…
⌘/ctrl + enter
sql · live postgres…a write takes ROW EXCLUSIVE
⌘/ctrl + enter

// gotcha · this is why a careless migration takes down an app

An ALTER TABLE that needs ACCESS EXCLUSIVE must first wait for every current reader and writer to finish — and once it's queued, it blocks all the new SELECTs arriving behind it too. On a busy table, a change that takes one second to apply can stall the whole application for far longer. (Same reason VACUUM FULL's ACCESS EXCLUSIVE lock is an outage.) Run schema changes with a short lock_timeout so they fail fast instead of building a queue behind them.

Waiting, and the deadlock detector

When you request a lock someone else holds, your transaction joins a wait queue for it — first-come-first-served, so waiters can't starve. Usually the holder commits a moment later and you proceed. A deadlock is the pathological case: a cycle of waiters — A waits for B while B waits for A — which left alone would never resolve.

Postgres breaks the cycle, but lazily: checking for one on every lock wait would be wasteful, so a blocked backend only runs the deadlock detector after it's been waiting for deadlock_timeout (1 second by default). The detector builds a wait-for graph of who's blocked on whom, searches it for a cycle, and if it finds one aborts a victim — rolling that transaction back with a deadlock detected error, which frees its locks so everyone else proceeds. Your app should catch that error and retry the transaction.

Two related knobs bound a query's patience: lock_timeout caps any single lock wait, and statement_timeout caps the whole statement — both let a query give up instead of blocking forever.

// 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
⌘/ctrl + enter

Advisory locks: your own mutexes

Sometimes you want a lock tied to no row at all — a mutex around an application operation (“only one worker rebuilds this cache at a time”). Advisory locks are arbitrary integer locks Postgres tracks for you but never enforces on its own; their meaning is entirely up to your code.

sql · live postgreseditable — run it
⌘/ctrl + enter

They come in two scopes. Session-level (pg_advisory_lock / the non-blocking pg_try_advisory_lock) is held until you unlock it or disconnect — powerful, but a forgotten unlock, or a connection handed back to a pool, can leak it. Transaction-level (pg_advisory_xact_lock) releases automatically at commit or rollback, so it can't leak — usually the safer choice. Reach for the pg_try_* variants (which return false instead of blocking) for the classic "only one worker at a time" pattern.

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

⌘/ctrl + enter
// challengewrite it yourself

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

⌘/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.
  • 02Four row-lock strengths: FOR UPDATE > FOR NO KEY UPDATE > FOR SHARE > FOR KEY SHARE (foreign keys take FOR KEY SHARE on the parent). Row locks aren't in pg_locks — they're stamped onto the tuple's xmax, so they scale to millions.
  • 03Every statement also takes a table lock: SELECT = ACCESS SHARE, writes = ROW EXCLUSIVE, DDL / VACUUM FULL = ACCESS EXCLUSIVE — which conflicts with everything and can stall an app behind its queue.
  • 04A deadlock is a cycle of waiters; a blocked backend runs the detector after deadlock_timeout (1s), builds a wait-for graph, and aborts a victim — the app should catch the error and retry. lock_timeout / statement_timeout bound how long a query waits.
  • 05Deadlocks come from inconsistent lock ordering — always acquire rows/tables in the same order to prevent them.
  • 06FOR UPDATE NOWAIT fails instead of waiting; SKIP LOCKED ignores locked rows — the basis of SELECT-FOR-UPDATE work queues.
  • 07Advisory locks are arbitrary integer mutexes; session-scoped ones can leak, transaction-scoped (pg_advisory_xact_lock) auto-release at commit.

// 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

nextWAL & Durability