Locks

Every statement takes a table lock

BEGIN;

SELECT owner, balance FROM accounts ORDER BY id;

SELECT relation::regclass AS relation, mode
FROM pg_locks
WHERE locktype = 'relation'
  AND pid = pg_backend_pid()
  AND relation IN ('accounts'::regclass, 'transfers'::regclass)
ORDER BY relation::regclass::text, mode;

Each table gets the lock its statement needs, and the transaction keeps all of them until it commits or rolls back:

BEGIN;

SELECT id, amount FROM transfers WHERE status = 'pending' ORDER BY id;

UPDATE accounts SET balance = balance - 100 WHERE owner = 'alice';

SELECT relation::regclass AS relation, mode
FROM pg_locks
WHERE locktype = 'relation'
  AND pid = pg_backend_pid()
  AND relation IN ('accounts'::regclass, 'transfers'::regclass)
ORDER BY relation::regclass::text, mode;

And a schema change takes the strongest one:

BEGIN;

ALTER TABLE accounts ADD COLUMN note TEXT;

SELECT relation::regclass AS relation, mode
FROM pg_locks
WHERE locktype = 'relation'
  AND pid = pg_backend_pid()
  AND relation IN ('accounts'::regclass, 'transfers'::regclass)
ORDER BY relation::regclass::text, mode;

The eight table lock modes

BEGIN;

SELECT owner, balance FROM accounts ORDER BY id;

UPDATE accounts SET balance = balance + 10 WHERE owner = 'bob'
RETURNING owner, balance;

ALTER TABLE accounts ADD COLUMN note TEXT;

SELECT owner, balance FROM accounts ORDER BY id;

SELECT activity.application_name AS session, lock.mode, lock.granted
FROM pg_locks AS lock
JOIN pg_stat_activity AS activity USING (pid)
WHERE lock.locktype = 'relation'
  AND lock.relation = 'accounts'::regclass
  AND activity.pid <> pg_backend_pid()
ORDER BY session, lock.mode;

SELECT
  waiting.application_name AS waiting_session,
  blocker.application_name AS blocked_by,
  waiting.query AS waiting_query
FROM pg_stat_activity AS waiting
JOIN pg_stat_activity AS blocker
  ON blocker.pid = ANY (pg_blocking_pids(waiting.pid))
ORDER BY waiting_session, blocked_by;

COMMIT;

This is why schema migrations on busy tables are risky: while an ALTER TABLE holds or even just waits for ACCESS EXCLUSIVE, every new query on that table queues behind it, like Session C did. See the lock_timeout section below for the standard defense.

BEGIN;

SELECT owner, balance FROM accounts WHERE owner = 'alice' FOR UPDATE;

INSERT INTO transfers (account_id, amount) VALUES (1, 40)
RETURNING id, account_id, amount;

COMMIT;

BEGIN;

SELECT owner, balance FROM accounts WHERE owner = 'alice' FOR NO KEY UPDATE;

INSERT INTO transfers (account_id, amount) VALUES (1, 40)
RETURNING id, account_id, amount;

COMMIT;

A plain UPDATE accounts SET balance = ... takes FOR NO KEY UPDATE automatically, since it doesn't touch the key. So when you lock rows yourself before updating them, prefer FOR NO KEY UPDATE over FOR UPDATE unless you're about to delete the row or change its key. This is the conflict the reservation system lesson mentions when it avoids locking the user row.

BEGIN;

SELECT id, account_id, amount
FROM transfers
WHERE status = 'pending'
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;

SELECT id, account_id, amount
FROM transfers
WHERE status = 'pending'
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;

SELECT id, account_id, amount
FROM transfers
WHERE status = 'pending'
ORDER BY id
LIMIT 1
FOR UPDATE;

SELECT id, account_id, amount
FROM transfers
WHERE status = 'pending'
ORDER BY id
LIMIT 1
FOR UPDATE NOWAIT;

could not obtain lock on row in relation "transfers"

UPDATE transfers SET status = 'done' WHERE id = 1;

COMMIT;

BEGIN;

-- Give up quickly instead of queueing behind a long-running query.
-- While this ALTER waits for its ACCESS EXCLUSIVE lock, every new query
-- on accounts queues behind it, so waiting a long time is the dangerous part.
SET LOCAL lock_timeout = '2s';

ALTER TABLE accounts ADD COLUMN note TEXT;

COMMIT;

BEGIN;

UPDATE accounts SET balance = balance - 10 WHERE owner = 'alice';

BEGIN;

UPDATE accounts SET balance = balance - 10 WHERE owner = 'bob';

UPDATE accounts SET balance = balance + 10 WHERE owner = 'bob';

deadlock detected

UPDATE accounts SET balance = balance + 10 WHERE owner = 'alice';

ROLLBACK;

COMMIT;

SELECT owner, balance FROM accounts ORDER BY id;

-- Both transfers lock their rows in the same order (lowest id first),
-- so the second one simply waits for the first instead of deadlocking.
BEGIN;

SELECT id FROM accounts
WHERE owner IN ('alice', 'bob')
ORDER BY id
FOR NO KEY UPDATE;

UPDATE accounts SET balance = balance - 10 WHERE owner = 'bob';
UPDATE accounts SET balance = balance + 10 WHERE owner = 'alice';

COMMIT;

Who is blocking whom?

When queries pile up in production, this is the first query to run. It's the one Session D ran earlier: for every session waiting on a lock, it shows which sessions are in its way, using pg_blocking_pids().

SELECT
  waiting.application_name AS waiting_session,
  blocker.application_name AS blocked_by,
  waiting.query AS waiting_query
FROM pg_stat_activity AS waiting
JOIN pg_stat_activity AS blocker
  ON blocker.pid = ANY (pg_blocking_pids(waiting.pid))
ORDER BY waiting_session, blocked_by;

Follow the chain to the session at its head, and check that session's state in pg_stat_activity. The classic culprit is idle in transaction: an application that opened a transaction, took some locks, and never committed. pg_terminate_backend(pid) ends it, and idle_in_transaction_session_timeout stops it from happening again.

Advisory locks

Every lock so far protects a table or a row. Sometimes you need to protect an idea instead, like "only one request at a time for this user and this event", where there's no single row to lock. Advisory locks lock an arbitrary number that your application chooses the meaning of. The reservation system lesson uses one to close a real race condition.

Rules of thumb

• Keep transactions short. Every lock is held until the transaction ends, so a transaction left open while your app calls another service holds its locks the whole time.
• Set lock_timeout in migrations, and prefer CREATE INDEX CONCURRENTLY, which takes SHARE UPDATE EXCLUSIVE instead of SHARE and so doesn't block writes.
• Use FOR NO KEY UPDATE rather than FOR UPDATE unless you're deleting the row or changing its key.
• Lock rows in a consistent order to avoid deadlocks, and retry when one happens anyway.
• For work queues, use FOR UPDATE SKIP LOCKED. The job queue lesson builds a complete one.
• When something is stuck, pg_locks and pg_blocking_pids(pid) tell you who is waiting on whom.