Locks and deadlocks

Lock modes, waiting, the lock queue behind a migration, deadlocks, SKIP LOCKED and advisory locks.

Draft. This page is new and has not yet been reviewed by a second person. Every query output on it was produced by a real run on PostgreSQL 18.6 (PGlite 0.5.9). If something looks wrong, report it.

On this page
  1. What a statement locks
  2. Which modes conflict
  3. Waiting for a row
  4. The lock queue: how a quick migration takes a site down
  5. Deadlocks
  6. Patterns that use locks well
  7. Timeouts worth setting
  8. In MySQL
  9. Sources

MVCC means readers and writers rarely wait for each other, but writers still lock what they change, and schema changes lock whole tables. Most "the database is hanging" incidents are not slow queries at all: they are queries waiting for a lock that someone else holds.

What a statement locks#

Every statement takes a lock on each table it touches, in one of eight table-level modes, and holds it until its transaction ends. This runs on the sample database and lists the locks the current transaction holds after an UPDATE and a CREATE INDEX:

sql
update orders set total = total where id = 5;
create index products_price_idx on products (price);
sql
select locktype, relation::regclass as relation, mode, granted
from pg_locks
where pid = pg_backend_pid()
  and locktype in ('relation', 'transactionid')
  and (relation is null or relation::regclass::text not like 'pg_%')
order by locktype, relation::regclass::text, mode;
Result 5 rows; real output from PostgreSQL 18.6 (PGlite 0.5.9)
locktyperelationmodegranted
relationordersRowExclusiveLocktrue
relationorders_pkeyRowExclusiveLocktrue
relationproductsShareLocktrue
relationproducts_price_idxAccessExclusiveLocktrue
transactionidNULLExclusiveLocktrue

The update holds a RowExclusiveLock on orders (and its indexes), and the transaction holds an ExclusiveLock on its own transaction id, which is what other sessions wait on when they want a row it changed. The index build holds a ShareLock on products: other sessions can still read the table, but cannot write to it until the transaction ends. CREATE INDEX CONCURRENTLY avoids that, at the cost of a slower build that cannot run inside a transaction.

Which modes conflict#

Lock modeTaken byBlocks
ACCESS SHARESELECTonly ACCESS EXCLUSIVE
ROW SHARESELECT … FOR UPDATE / FOR SHAREEXCLUSIVE, ACCESS EXCLUSIVE
ROW EXCLUSIVEINSERT, UPDATE, DELETE, MERGESHARE and stronger
SHARE UPDATE EXCLUSIVEVACUUM, ANALYZE, CREATE INDEX CONCURRENTLYitself and stronger
SHARECREATE INDEXROW EXCLUSIVE (all writes) and stronger
ACCESS EXCLUSIVEmost ALTER TABLE, DROP, TRUNCATE, VACUUM FULLeverything, including SELECT

The middle modes (SHARE ROW EXCLUSIVE, EXCLUSIVE) are rarer and are in the documentation's full table.

Rows are locked separately. An UPDATE or DELETE locks the rows it changes; SELECT … FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE and FOR KEY SHARE lock the rows they read. Row locks are stored in the rows themselves rather than in memory, so locking millions of rows is possible, and a transaction waiting for a row lock waits on the transaction id of the one that holds it.

Waiting for a row#

Recorded on a real server with two connections. B changes a different row freely, then waits for the row A is holding:

Recorded on PostgreSQL 18.3, 2026-10-16

Waiting for a row lock

  1. A: begin → ok
  2. A: update accounts set balance = balance - 50 where id = 1 → ok
    A updates row 1 and so holds a lock on it until it ends.
  3. B: update accounts set balance = balance + 10 where id = 2 → ok
    B updates a different row: no conflict, no waiting.
  4. B: update accounts set balance = balance + 10 where id = 1 → waits
    B wants row 1 too and waits. In pg_locks it is waiting for A's transaction id; pg_blocking_pids names A.
  5. A: commit → ok
    A commits; B's update goes ahead immediately, on top of A's change.
  6. B (finishes): update accounts set balance = balance + 10 where id = 1 → ok
  7. B: select id, balance from accounts order by id → 1, 60 | 2, 110

pg_blocking_pids(pid) names who is blocking a session, and pg_stat_activity shows wait_event_type = 'Lock' for anyone waiting. Together they answer "who is blocking whom" on a live system.

The lock queue: how a quick migration takes a site down#

An ALTER TABLE that takes a fraction of a second can still stop every query on a table. It needs an ACCESS EXCLUSIVE lock, so it waits behind any open transaction that has merely read the table. Locks are granted in order, so every new query, even a plain SELECT that does not conflict with the open transaction, queues behind the migration:

Recorded on PostgreSQL 18.3, 2026-10-16

A migration that stalls every query

  1. A: begin → ok
  2. A: select count(*) from accounts → 2
    A long-running transaction has read the table, so it holds a light ACCESS SHARE lock until it ends.
  3. B: alter table accounts add column note text → waits
    A migration needs an ACCESS EXCLUSIVE lock, which conflicts with every other lock, so it waits for A.
  4. C: select count(*) from accounts → waits
    An ordinary read now waits too, even though it does not conflict with A: it queues behind the migration's request. Every new query on the table piles up here.
  5. A: commit → ok
    A finishes. The migration runs, then the queued reads. In production, set lock_timeout before a migration so it gives up instead of stalling everything.
  6. B (finishes): alter table accounts add column note text → ok
  7. C (finishes): select count(*) from accounts → 2

Protect migrations with SET lock_timeout = '2s' so they give up and retry instead of stalling everything, and do not leave transactions open longer than they need to be.

Deadlocks#

Two transactions each hold a lock the other wants. Neither can continue, so after deadlock_timeout (1 second by default) PostgreSQL checks for a cycle, finds it, and aborts one transaction with error 40P01 so the other can finish:

Recorded on PostgreSQL 18.3, 2026-10-16

A deadlock, detected and broken

  1. A: begin → ok
  2. B: begin → ok
  3. A: update accounts set balance = balance - 10 where id = 1 → ok
    A moves money from Asha to Ben: it locks Asha's row first.
  4. B: update accounts set balance = balance - 10 where id = 2 → ok
    B moves money the other way: it locks Ben's row first.
  5. A: update accounts set balance = balance + 10 where id = 2 → waits
    A now needs Ben's row, which B holds. A waits.
  6. B: update accounts set balance = balance + 10 where id = 1 → ok
    B needs Asha's row, which A holds. Each waits for the other: a deadlock. After deadlock_timeout (1 second by default) PostgreSQL checks, finds the cycle and aborts one transaction so the other can finish.
  7. A (finishes): update accounts set balance = balance + 10 where id = 2 → error: deadlock detected
  8. A: rollback → ok
  9. B: rollback → ok

Deadlocks are not bugs in the database; they are the database rescuing two transactions that locked in opposite orders. To make them rare:

  1. Lock in a consistent order. If every transfer updates the lower account id first, two transfers cannot each hold what the other needs.
  2. Keep transactions short, so there is less time for overlaps.
  3. Retry a transaction that fails with a deadlock or serialization error. Some will happen under load however careful you are.

Patterns that use locks well#

Work queues with SKIP LOCKED. Several workers can take jobs from one table without waiting for each other, because each skips rows another worker has already locked:

Recorded on PostgreSQL 18.3, 2026-10-16

A job queue with SKIP LOCKED

  1. A: begin → ok
  2. A: select id from jobs where not done order by id limit 1 for update skip locked → 1
    Worker A claims the first job that nobody else has locked: job 1.
  3. B: begin → ok
  4. B: select id from jobs where not done order by id limit 1 for update skip locked → 2
    Worker B skips job 1, which A holds, and claims job 2 without waiting. Without SKIP LOCKED it would wait for A.
  5. A: update jobs set done = true where id = 1 → ok
  6. A: commit → ok
  7. B: update jobs set done = true where id = 2 → ok
  8. B: commit → ok

Advisory locks lock a number your application chooses rather than a row, for example to make sure only one instance of a nightly job runs:

Recorded on PostgreSQL 18.3, 2026-10-16

An advisory lock for a job that must run once

  1. A: select pg_try_advisory_lock(42) as got_it → true
    Instance A of a nightly job takes advisory lock 42, a number the application chose.
  2. B: select pg_try_advisory_lock(42) as got_it → false
    Instance B tries the same number and gets false at once, so it knows another instance is running and exits.
  3. A: select pg_advisory_unlock(42) as released → true
    A finishes and releases the lock. A session-level advisory lock is also released if the connection drops.

Timeouts worth setting#

  • lock_timeout: give up waiting for a lock after this long. Essential for migrations.
  • statement_timeout: cancel any statement that runs longer than this.
  • idle_in_transaction_session_timeout: end sessions that sit in an open transaction doing nothing.

In MySQL#

Not run here; from the MySQL reference manual. InnoDB locks rows through its indexes, and at its default repeatable read level also locks the gaps between index records (next-key locks), so a range update can block inserts into that range. It detects deadlocks immediately rather than after a timeout, and rolls back the smaller transaction. innodb_lock_wait_timeout (50 seconds by default) limits waits. Many ALTER TABLE operations run as online DDL, but still need a brief metadata lock that queues like PostgreSQL's.

Sources#

Esc
↑ ↓ to move↵ to open