Locking and deadlocks

MVCC removes most conflicts between readers and writers, but writers still have to queue for each other, and schema changes have to exclude everyone. PostgreSQL uses two layers of locks for that.

Table-level locks

Every statement takes a lock on each table it touches, held until the transaction ends. The common ones, weakest first:

• ACCESS SHARE — plain SELECT. Conflicts only with ACCESS EXCLUSIVE.
• ROW SHARE — SELECT ... FOR UPDATE / FOR SHARE.
• ROW EXCLUSIVE — INSERT, UPDATE, DELETE, MERGE.
• SHARE — CREATE INDEX (without CONCURRENTLY): blocks writes, allows reads.
• ACCESS EXCLUSIVE — DROP TABLE, TRUNCATE, VACUUM FULL and many forms of ALTER TABLE: blocks everything, including plain SELECT.

Lock requests queue in order. A migration's ALTER TABLE waiting behind one long SELECT makes every later query on that table wait behind the ALTER. Set lock_timeout in migrations so they give up instead.

Row-level locks

UPDATE and DELETE lock the rows they change; SELECT ... FOR UPDATE locks rows you intend to change later in the transaction, so nobody else can modify them in between. There are four strengths:

• FOR UPDATE — strongest; taken by DELETE and by an UPDATE that changes a key column.
• FOR NO KEY UPDATE — taken by an ordinary UPDATE; does not block foreign-key checks.
• FOR SHARE — blocks changes but lets others share.
• FOR KEY SHARE — taken by foreign-key checks; only blocks deleting the row or changing its key.

Row locks are not kept in the shared lock table; they are written into the row version itself (its xmax), so locking a million rows costs no lock memory, though it does write to each locked row's page. A session waiting for a row lock waits on the holder's transaction id instead.

1CREATE TABLE accounts (id int PRIMARY KEY, balance int NOT NULL);2INSERT INTO accounts VALUES (1, 100), (2, 50), (3, 75);

1SELECT id FROM accounts WHERE id IN (1, 3) ORDER BY id FOR UPDATE;2 3SELECT (SELECT string_agg(id || '=' || (xmax = pg_current_xact_id()::xid), ', ' ORDER BY id)4          FROM accounts) AS row_locked_by_me,5       (SELECT string_agg(concat_ws(' ', locktype, relation::regclass::text, mode),6                          ', ' ORDER BY locktype, mode)7          FROM pg_locks8         WHERE pid = pg_backend_pid()9           AND (relation = 'accounts'::regclass OR locktype = 'transactionid')) AS lock_table_entries;

1CREATE TABLE accounts (id int PRIMARY KEY, balance int NOT NULL);2INSERT INTO accounts VALUES (1, 100), (2, 50), (3, 75);

1UPDATE accounts SET balance = 0 WHERE id = 2;2LOCK TABLE accounts IN SHARE MODE;3 4SELECT (SELECT string_agg(mode, ', ' ORDER BY mode)5          FROM pg_locks6         WHERE pid = pg_backend_pid() AND relation = 'accounts'::regclass) AS locks_on_accounts,7       current_setting('deadlock_timeout') AS deadlock_timeout,8       current_setting('lock_timeout')     AS lock_timeout;

Deadlocks

Transaction A locks account 1 and then wants account 2; transaction B has locked 2 and wants 1. Each waits for the other forever. When a lock wait lasts longer than deadlock_timeout (1 second by default), PostgreSQL checks the wait-for graph; if it finds a cycle it aborts one of the transactions with SQLSTATE 40P01 (deadlock detected) and the other proceeds. The aborted transaction must be retried by the application. All of this is documented behaviour rather than something the examples show: a deadlock needs two sessions, and the sandbox runs one.

Prevention is about order, not luck:

• Lock rows in one global order, for example ascending id: SELECT ... WHERE id IN (1, 2) ORDER BY id FOR UPDATE. Two transfers that both lock the lower id first cannot deadlock on each other.
• Keep transactions short; take the locks you need early.
• Treat 40P01 and 40001 as retryable.

Waiting less: NOWAIT, SKIP LOCKED, lock_timeout

• FOR UPDATE NOWAIT fails immediately (55P03) if a row is locked.
• FOR UPDATE SKIP LOCKED silently skips locked rows, which makes a table into a work queue: each worker runs SELECT id FROM jobs WHERE status = 'queued' ORDER BY id LIMIT 10 FOR UPDATE SKIP LOCKED and gets a different batch.
• SET lock_timeout = '2s' bounds any lock wait in the session.

Common mistakes