Everyone waits in line

A migration adds a nullable column to accounts. It's a metadata-only change that completes in milliseconds. The moment it runs, every request that touches accounts hangs, the connection pool fills, and the site goes down. Nothing in the migration was slow. It was waiting, and while it waited, everyone else waited too.

Here is that outage, reproduced with three sessions on the book's database. migration runs alter table accounts add column note text, and web runs select name from accounts where id = 42:

  pid  |    app    |        state        | wait_event | blocked_by
-------+-----------+---------------------+------------+------------
 11033 | reporting | idle in transaction | ClientRead | {}
 11046 | migration | active              | relation   | {11033}
 11061 | web       | active              | relation   | {11046}

The reporting session ran select count(*) from accounts inside a transaction and never closed it. The migration needs an exclusive lock, so it waits for the reporting session. The web request only wants to read one row, which the reporting session would happily allow, but it's queued behind the migration. Every request after it joins the line.

Usually readers and writers don't block each other (Updates are inserts). When they do, the line is the whole story. Learn to read it.

Pick an isolation level on purpose

Read committed, the default, gives each statement a fresh snapshot; repeatable read gives the whole transaction one; serializable also guarantees a result some one-at-a-time order could produce. Only serializable rules out every anomaly lists what each prevents; here is what each does to your code. (InnoDB defaults to repeatable read: MySQL defaults to repeatable read; Postgres doesn't.)

Read committed loses updates computed in the app

Most applications run here and are fine. The trap is read-modify-write in application code: two requests each read a balance, subtract in the application, and write back.

tx1 spent 30 and tx2 spent 20: fifty credits spent, balance down by twenty. That's a lost update, and no error says so. Do the arithmetic in the database instead:

update credits
set balance = balance - 30
where account_id = 42 and balance >= 30
returning balance;

An update that waited on a changed row re-reads the latest version and re-checks its where, so two concurrent spends return 70 and then 50. When the logic can't fit in one statement, lock the row as you read it with select ... for update.

  tx1                                tx2
  begin;                             begin;
  select balance ...;  -- 100        select balance ...;  -- 100
  update credits set balance = 70
                                     update credits set balance = 80
                                       (waits on tx1's row lock)
  commit;
                                       (proceeds, overwrites 70)
                                     commit;

select lab.run('tx1', 'update credits set balance = 70 where account_id = 42') \g /dev/null
select dblink_send_query('tx2', 'update credits set balance = 80 where account_id = 42') \g /dev/null
select pg_sleep(0.3) \g /dev/null
 
select lab.prove(
  'tx2''s update waits for tx1''s row lock',
  (select wait_event_type = 'Lock' and wait_event = 'transactionid'
          and pg_blocking_pids(pid) = (select array_agg(pid) from lab.activity
                                       where application_name = 'tx1')
   from lab.activity where application_name = 'tx2'));
 
select lab.run('tx1', 'commit') \g /dev/null
select lab.finish('tx2') as tx2_result \gset
select lab.run('tx2', 'commit') \g /dev/null
 
select lab.prove(
  'under read committed tx2 proceeds without error and overwrites: balance 80, a lost update',
  :'tx2_result' = 'ok'
  and (select balance from credits where account_id = 42) = 80);

select lab.prove(
  'under repeatable read tx2 fails with 40001 could not serialize access due to concurrent update, and tx1''s 70 stands',
  :'tx2_result' = '40001: could not serialize access due to concurrent update'
  and (select balance from credits where account_id = 42) = 70);

Repeatable read turns that race into an error to retry

At repeatable read, an update of a row someone changed since your snapshot is refused instead of overwritten: the same race fails tx2 with could not serialize access due to concurrent update. At this level and at serializable, that error (SQLSTATE 40001) is a normal outcome, not a bug: your code retries the whole transaction, or rare races become user-facing errors.

Serializable catches the write skew repeatable read misses

Repeatable read still allows write skew. An on-call rotation must always have someone on call. Two engineers go off call at the same moment; each counts who's on call (2, so it's safe) and deactivates only their own row. Both commit, and nobody is on call; no row was updated twice, so repeatable read saw nothing wrong. At serializable, one fails with could not serialize access due to read/write dependencies among transactions. Postgres tracks what each transaction read, without blocking anyone, and aborts one when no serial order explains the result (Even a read-only transaction can see the impossible).

begin isolation level repeatable read;
select count(*) from on_call where account_id = 42 and active;  -- 2, so it's safe
-- the other transaction deactivates user 2
update on_call set active = false where account_id = 42 and user_id = 1;
commit;

select lab.prove(
  'serializable records what each transaction read as SIReadLock entries in pg_locks',
  exists (select from pg_locks l join lab.activity a using (pid)
          where a.application_name = 'tx1' and l.mode = 'SIReadLock'));
 
select lab.prove(
  'one transaction fails with 40001 could not serialize access due to read/write dependencies, and someone stays on call',
  :'outcome' like '%40001: could not serialize access due to read/write dependencies among transactions%'
  and (select count(*) from on_call where active) = 1);

Our default: read committed for requests, repeatable read for reports, serializable for rules across rows, with retries.

Retry the whole transaction

Serialization failures (40001) and deadlocks (40P01) mean "nothing happened; try again". Retry the whole transaction, reads included, with capped, jittered attempts. Those two codes are the ones to retry by default; under serializable, a unique violation (23505) or exclusion violation (23P01) can also be a lost race worth one more try. Retry nothing else blindly. Keep side effects out of the retried function, or an email goes out once per attempt; record the intent and act after commit (Put the message in the same transaction as the order).

func withRetry(ctx context.Context, pool *pgxpool.Pool,
	fn func(pgx.Tx) error) error {
	for attempt := 0; ; attempt++ {
		opts := pgx.TxOptions{IsoLevel: pgx.Serializable}
		err := pgx.BeginTxFunc(ctx, pool, opts, fn)
		var pgErr *pgconn.PgError
		retryable := errors.As(err, &pgErr) &&
			(pgErr.Code == "40001" || pgErr.Code == "40P01")
		if retryable && attempt < 4 {
			jitter := rand.Intn(20 * (attempt + 1))
			time.Sleep(time.Duration(jitter) * time.Millisecond)
			continue
		}
		return err
	}
}

One error aborts the whole transaction

In Postgres, any error inside a transaction poisons the rest of it:

begin;
insert into users (account_id, email, name) values (1, 'new@example.com', 'New');
insert into users (account_id, email, name) values (2, 'user1@example.com', 'Dup');
update accounts set name = name where id = 1;
commit;

ERROR:  duplicate key value violates unique constraint "users_account_id_email_key"
ERROR:  current transaction is aborted,
        commands ignored until end of transaction block
ROLLBACK

The update never ran, and commit came back as ROLLBACK: the first insert is gone too. "Insert, catch the duplicate, carry on" breaks the moment someone wraps it in a transaction. Prevent the error instead (on conflict do nothing), or use a savepoint (MySQL differs; see Errors abort the whole transaction).

select lab.prove(
  'after a duplicate key error, the next statement fails with 25P02 and COMMIT reports ROLLBACK',
  lab.run('tx1', 'begin') = 'ok'
  and lab.run('tx1', $$insert into users (account_id, email, name) values (1, 'new@example.com', 'New')$$) = 'ok'
  and lab.run('tx1', $$insert into users (account_id, email, name) values (2, 'user1@example.com', 'Dup')$$)
      = '23505: duplicate key value violates unique constraint "users_account_id_email_key"'
  and lab.run('tx1', 'update accounts set name = name where id = 1')
      = '25P02: current transaction is aborted, commands ignored until end of transaction block'
  and dblink_exec('tx1', 'commit') = 'ROLLBACK');

Savepoints contain the damage

A savepoint marks a point you can roll back to without losing the rest. ORM "nested transactions" are savepoints (Rails' requires_new: true, Django's nested atomic()), and so is every PL/pgSQL EXCEPTION block.

Subtransactions are not free

Each savepoint starts a subtransaction, and one that writes gets its own XID. Each backend caches 64 subtransaction XIDs; past that the cache overflows, and every session's visibility checks against that transaction fall back to an on-disk structure, pg_subtrans. Replicas are more fragile still: there, any subtransaction alongside one long-running transaction can push lookups into pg_subtrans. This bit GitLab in 2021: on their replicas, a single savepoint during a long-running transaction was enough to stall queries on and off for weeks, until they removed savepoints from the application (GitLab's write-up). Keep savepoints out of loops, and reach for on conflict first.

begin;
select format('savepoint s%s', g), format('insert into subxact_demo values (%s)', g)
from generate_series(1, 64) g \gexec
 
select lab.prove(
  'after 64 savepoints, each followed by an insert: 64 cached, not overflowed',
  (select (subxact_count, subxact_overflowed) = (64, false) from lab.my_subxact()));
 
savepoint s65;
insert into subxact_demo values (65);
 
select lab.prove(
  'one more: the count stops at 64 and the overflow flag flips',
  (select (subxact_count, subxact_overflowed) = (64, true) from lab.my_subxact()));
rollback;

Every statement locks the table

Every statement also locks each table it touches until the transaction ends. Three facts about the eight table lock modes cover nearly every incident:

• Reads and writes never conflict at the table level. Row conflicts are a separate system.
• ACCESS EXCLUSIVE conflicts with everything, including plain select. drop, truncate, and most alter table forms take it.
• SHARE blocks writes but not reads, which is why plain create index makes a table read-only and create index concurrently exists.

Most ALTER TABLE forms take ACCESS EXCLUSIVE

Most ALTER TABLE forms take ACCESS EXCLUSIVE, even when the work is instant: adding a nullable column takes microseconds, but getting the lock is the dangerous part (Change the schema, keep the site). To check a statement, run it in a transaction, read your rows in pg_locks, and roll back.

                      held: AS   RS   RE   SUE  S    SRE  E    AE
  requested
  ACCESS SHARE (AS)         .    .    .    .    .    .    .    X
  ROW SHARE (RS)            .    .    .    .    .    .    X    X
  ROW EXCL (RE)             .    .    .    .    X    X    X    X
  SHARE UPD EXCL (SUE)      .    .    .    X    X    X    X    X
  SHARE (S)                 .    .    X    X    .    X    X    X
  SHARE ROW EX (SRE)        .    .    X    X    X    X    X    X
  EXCLUSIVE (E)             .    X    X    X    X    X    X    X
  ACCESS EXCL (AE)          X    X    X    X    X    X    X    X

-- lab.locks_taken runs a statement, reads this session's table locks from
-- pg_locks, and rolls back
select lab.prove('add column takes AccessExclusiveLock',
  lab.locks_taken('alter table projects add column note text', '{projects}') = '{"projects AccessExclusiveLock"}');
select lab.prove('validate constraint takes ShareUpdateExclusiveLock',
  lab.locks_taken('alter table projects validate constraint name_not_empty', '{projects}')
    = '{"projects ShareUpdateExclusiveLock"}');
select lab.prove('create index takes ShareLock',
  lab.locks_taken('create index on projects (created_at)', '{projects}') = '{"projects ShareLock"}');

A waiting lock blocks everyone behind it

Locks are granted in order, like a single line at a counter. Requests that conflict with a waiting request queue behind it, even if they're compatible with everything held. That prevents starvation, and it's exactly what turns a harmless migration into an outage.

In pg_locks during the opening incident, the web query's AccessShareLock shows granted = f, though the only granted lock is another AccessShareLock: it's behind the migration. In production a few hundred requests a second pile up, the pool runs dry, and health checks fail. When the reporting transaction ends, the migration runs in a millisecond and the line drains.

select lab.prove(
  'the web SELECT waits too: its AccessShareLock is not granted, and it is blocked by the migration',
  exists (select from pg_locks where pid = :web_pid and relation = 'accounts'::regclass
          and mode = 'AccessShareLock' and not granted)
  and pg_blocking_pids(:web_pid) = array[:migration_pid]
  and (select (wait_event_type, wait_event) = ('Lock', 'relation') from lab.activity where pid = :web_pid));

The defense is to make DDL give up quickly. With set lock_timeout = '2s', the same alter fails with canceling statement due to lock timeout after two seconds instead of holding the line. Two seconds of blocked traffic is a blip; retry a few times. Then find the session that held the lock: an idle in transaction session holding locks is a bug, and idle_in_transaction_session_timeout caps how long it can last.

  pid  | application_name | locktype |        mode         | granted
-------+------------------+----------+---------------------+---------
 11033 | reporting        | relation | AccessShareLock     | t
 11046 | migration        | relation | AccessExclusiveLock | f
 11061 | web              | relation | AccessShareLock     | f

select lab.prove(
  'the migration waits for AccessExclusiveLock, blocked by reporting',
  exists (select from pg_locks where pid = :migration_pid and relation = 'accounts'::regclass
          and mode = 'AccessExclusiveLock' and not granted)
  and pg_blocking_pids(:migration_pid) = array[:reporting_pid]);
 
set lock_timeout = '2s';
select lab.prove(
  'with lock_timeout = 2s the migration gives up: canceling statement due to lock timeout',
  lab.try('alter table accounts add column note text')
    = '55P03: canceling statement due to lock timeout');

Row locks live in the row, not in pg_locks

Row locks aren't kept in pg_locks while held (there could be millions). A locking transaction writes its ID into the row's header (xmax, Everything is a page), and a transaction wanting a conflicting lock waits for that transaction to finish. So a row-lock wait usually shows up as an ungranted ShareLock on a transactionid, not on a row. When several sessions queue for the same row, the first waiter also holds a heavyweight tuple lock on it, and the ones behind it wait on that tuple lock instead, so in a pile-up you'll see ungranted tuple entries too.

The weaker pair exists for foreign keys. Inserting an event for project 1 takes FOR KEY SHARE on the project, and a rename of the project (FOR NO KEY UPDATE) doesn't block it. But while a session holds select ... for update on the project, the insert waits: 3.6 seconds in our test, until that session rolled back. So lock with for no key update unless you'll delete the row or change its key. nowait fails instead of waiting; skip locked skips rows someone holds.

  app   |   locktype    |      mode       | granted | blocked_by
--------+---------------+-----------------+---------+------------
 locker | transactionid | ExclusiveLock   | t       | {}
 writer | transactionid | ExclusiveLock   | t       | {10113}
 writer | tuple         | AccessShareLock | t       | {10113}
 writer | transactionid | ShareLock       | f       | {10113}

set lock_timeout = '1s';
select lab.prove(
  'an explicit FOR NO KEY UPDATE does not block it either',
  lab.try($$insert into events (account_id, project_id, user_id, kind) values (9, 1, 8, 'comment')$$)
    = 'ok');
 
-- now tx1 holds select ... for update on project 1, and tx2 sends the same insert
select lab.prove(
  'FOR UPDATE on the project blocks the insert: an ungranted ShareLock on the locker''s transactionid',
  exists (select from pg_locks l join lab.activity a using (pid)
          where a.application_name = 'tx2' and l.locktype = 'transactionid'
            and l.mode = 'ShareLock' and not l.granted)
  and (select pg_blocking_pids(pid) from lab.activity where application_name = 'tx2')
      = (select array_agg(pid) from lab.activity where application_name = 'tx1'));

Shared row locks make multixacts, and multixacts wrap too

When two open transactions lock the same row in modes that don't conflict, such as two child inserts taking FOR KEY SHARE on one parent, Postgres puts a multixact in its xmax: an ID pointing to a member list. A popular parent under many concurrent child inserts gets a new one per insert. Multixacts have their own wraparound (Wraparound stops writes before anything is corrupted): watch mxid_age(datminmxid) next to transaction ID age, and keep child-inserting transactions short.

select lab.prove(
  'two open child inserts make the parent row''s xmax a multixact of two FOR KEY SHARE members',
  :'mx_multi' = 't'
  and (select count(*) = 2 and bool_and(mode = 'keysh')
       from pg_get_multixact_members(:'mx_two'::xid)));
 
select lab.prove(
  'a third locker creates a new multixact holding all three, rather than changing the old one',
  :'mx_three' <> :'mx_two'
  and (select count(*) = 3 and bool_and(mode = 'keysh')
       from pg_get_multixact_members(:'mx_three'::xid)));

LWLock waits are fixed in the workload, not the query

Not every line forms on a lock you asked for. Under heavy concurrency, sessions also queue on LWLocks, which no statement of yours mentions. A lightweight lock guards a piece of shared memory for microseconds, and a pile of waits means "too many of us are doing the same internal thing at once", not "someone holds what I want". So the fix is in the workload:

• LockManager: hot queries lock too many tables and indexes. Each backend has a few private fast-path slots that skip the shared lock table: 16 through PostgreSQL 17, 64 by default in 18.
• BufferMapping: the working set doesn't fit in memory.
• WALWrite: too many tiny transactions commit.

-- one transaction locks 100 small tables in AccessShare mode
select lab.prove(
  'PostgreSQL 18 has more than 16 fast-path slots, at most 64 at the default max_locks_per_transaction; the rest go to the shared lock table',
  :fp_fast > 16 and :fp_fast <= 64 and :fp_fast + :fp_slow = 100);

A Postgres queue commits with the business write

Row locks and skip locked add up to a solid job queue, with one decisive advantage over a separate system: the job is enqueued in the same transaction as the business write. Roll back and it never existed. A jobs table needs a partial index on queued jobs only, which stays small however many finished jobs pile up:

create index jobs_ready_idx on jobs (queue, run_at, id)
  where status = 'queued';

Each worker skips the jobs others hold

Each worker must get a different job without waiting on the others:

update jobs
set status    = 'running',
    attempts  = attempts + 1,
    locked_at = now(),
    locked_by = 'worker-a'
where id = (
  select id from jobs
  where queue = 'default' and status = 'queued' and run_at <= now()
  order by run_at, id
  limit 1
  for update skip locked
)
returning id, payload, attempts;

The subquery skips any job another worker holds and takes the first it can lock: 20 buffers with 200,000 finished jobs in the table. With worker A holding job 1, worker B got job 2 immediately. Without skip locked, a third worker queued behind A until its lock_timeout fired: every worker in line for the same row, throughput collapsed to one.

create table jobs (
  id          bigint generated always as identity primary key,
  queue       text not null default 'default',
  payload     jsonb not null,
  status      text not null default 'queued'
              check (status in ('queued', 'running', 'done', 'failed')),
  attempts    int not null default 0,
  run_at      timestamptz not null default now(),
  locked_at   timestamptz,
  locked_by   text,
  last_error  text,
  created_at  timestamptz not null default now()
);

 Update on jobs (actual time=0.040..0.041 rows=1.00 loops=1)
   Buffers: shared hit=20
   InitPlan 1
     ->  Limit ...
           ->  LockRows ...
                 ->  Index Scan using jobs_ready_idx on jobs jobs_1 ...

select lab.prove(
  'worker-b gets the next job immediately, not worker-a''s',
  :b_job = :a_job + 1);
 
select lab.prove(
  'without skip locked, worker-c queues behind worker-a''s lock until its lock_timeout fires',
  lab.run('worker-c', 'set lock_timeout = ''1s''') = 'ok'
  and lab.run('worker-c', $$select id from jobs
                            where queue = 'default' and status = 'queued' and run_at <= now()
                            order by run_at, id limit 1
                            for update$$)
      = '55P03: canceling statement due to lock timeout');

Short jobs hold the transaction; long jobs commit first

For jobs of a few seconds, hold the transaction for the whole job: a crash rolls it back and frees the job. For jobs that run minutes, which would hold back vacuum, claim and commit, process, then record the result, with a reaper for jobs stuck in running and idempotent handlers. A claim reads about 20 pages, so the queue rarely limits throughput before the jobs do; we didn't benchmark a ceiling, so measure yours. Delete or archive done jobs on a schedule: the partial index ignores them, but the heap and vacuum don't.

-- success
update jobs set status = 'done', locked_at = null where id = $1;
 
-- failure: retry with exponential backoff, give up after 5 attempts
update jobs
set status     = case when attempts >= 5 then 'failed' else 'queued' end,
    run_at     = now() + interval '10 seconds' * power(2, attempts),
    last_error = $2,
    locked_at  = null
where id = $1;
 
-- the reaper
update jobs
set status = 'queued', locked_at = null, locked_by = null
where status = 'running' and locked_at < now() - interval '15 minutes';

Take advisory locks per transaction, not per session

For mutual exclusion over something that isn't a row ("only one nightly rollup at a time"), advisory locks lock an integer key that means whatever you say. Two cron instances each start a transaction and run select pg_try_advisory_xact_lock(hashtext('nightly-rollup')): the first gets t, the second f and exits. The transaction-scoped form is released at commit or rollback, so prefer it to the session-level pg_advisory_lock. Session-level locks leak through any error path that skips the unlock, for as long as the pooled connection lives.

select lab.prove(
  'the first cron instance gets the lock, the second does not',
  (select got from dblink('cron-a', $$select pg_try_advisory_lock(hashtext('nightly-rollup'))$$) as t(got boolean))
  and not (select got from dblink('cron-b', $$select pg_try_advisory_lock(hashtext('nightly-rollup'))$$) as t(got boolean)));
 
select lab.prove(
  'a transaction-scoped advisory lock is released at commit',
  lab.run('cron-a', 'begin') = 'ok'
  and (select true from dblink('cron-a', 'select pg_advisory_xact_lock(1, 42)') as t(v text))
  and not (select got from dblink('cron-b', 'select pg_try_advisory_xact_lock(1, 42)') as t(got boolean))
  and lab.run('cron-a', 'commit') = 'ok'
  and (select got from dblink('cron-b', 'select pg_try_advisory_xact_lock(1, 42)') as t(got boolean)));

Deadlocks are an ordering bug

A deadlock is two transactions each waiting for a lock the other holds. After deadlock_timeout (1 second) Postgres finds the cycle and aborts one:

  tx1                                tx2
  ------------------------------     ------------------------------
  update accounts ... id = 1;
                                     update accounts ... id = 2;
  update accounts ... id = 2;
    (waits for tx2)
                                     update accounts ... id = 1;
                                       (waits for tx1: cycle)

tx1 got ERROR: deadlock detected; tx2 committed. The fix is consistent lock ordering: lock rows in primary key order (select ... order by id for update first, since where id = any($1) doesn't guarantee order), touch tables in a fixed order, and retry 40P01. Turn on log_lock_waits.

select lab.prove(
  'exactly one of the two fails with 40P01 deadlock detected; the other gets its lock',
  :'outcome' in ('40P01: deadlock detected / ok', 'ok / 40P01: deadlock detected'));
 
select lab.prove(
  'pg_stat_database counts the deadlock',
  (select deadlocks from pg_stat_database where datname = current_database()) > :deadlocks_before);

Find the root blocker

When something is stuck, find the root of the line first. This query lists every session waiting on a lock plus every session blocking one; it produced the table at the top of this chapter, which shows only some of its columns:

select pid,
       application_name           as app,
       state,
       wait_event_type            as wait_type,
       wait_event,
       pg_blocking_pids(pid)      as blocked_by,
       date_trunc('second', now() - xact_start) as xact_age,
       left(query, 40)            as query
from pg_stat_activity
where backend_type = 'client backend'
  and (wait_event_type = 'Lock' or pid in (
        select unnest(pg_blocking_pids(pid)) from pg_stat_activity))
order by xact_start;

wait_event says relation for a table lock, transactionid for a row lock. The root has an empty blocked_by and is often idle in transaction. select pg_terminate_backend(11033) ends it, rolling back its transaction (pg_cancel_backend does nothing for an idle session). Then fix whatever left it open.

The short version

• Read committed loses updates in application read-modify-write. Do the arithmetic in SQL.
• Repeatable read and serializable turn anomalies into 40001 errors; retry the whole transaction, side effects after commit.
• One error aborts the transaction. Prevent errors instead of catching them, and keep savepoints out of loops.
• One waiting ALTER TABLE blocks every later query on its table. Always set lock_timeout for migrations.
• LWLock waits mean the workload is crowding an internal structure: fix the workload.
• for update skip locked and a partial index make a job queue that commits with the business write.
• Lock in a consistent order. In an incident, find the root blocker first.

Further reading

• Transaction Isolation, PostgreSQL documentation. What each level guarantees, including the read committed re-check rule.
• Explicit Locking, PostgreSQL documentation. Table and row lock matrices, advisory locks, deadlocks.
• Serialization Failure Handling, PostgreSQL documentation. Which SQLSTATEs to retry.
• Why we spent the last month eliminating PostgreSQL subtransactions, GitLab, 2021. The incident above, investigated in full.
• Subtransactions, PostgreSQL documentation. The 64-entry cache and what happens past it.
• src/backend/storage/lmgr/README, PostgreSQL source. Spinlocks, LWLocks, heavyweight locks, and the fast path.
• Multixacts and Wraparound, PostgreSQL documentation. Freezing multixacts and the member-space limits.