Locks, deadlocks and safe DDL

conceptThe concept

Every statement takes a table-level lock: SELECT takes ACCESS SHARE; INSERT, UPDATE and DELETE take ROW EXCLUSIVE; CREATE INDEX takes SHARE (blocks writes); CREATE INDEX CONCURRENTLY takes SHARE UPDATE EXCLUSIVE; most ALTER TABLE forms, DROP, TRUNCATE and VACUUM FULL take ACCESS EXCLUSIVE, which conflicts with everything, even SELECT.

Lock requests queue in order. If an ALTER TABLE waits for ACCESS EXCLUSIVE behind a long-running SELECT, every new query on that table queues behind the ALTER. A two-second schema change becomes a full outage. Always set lock_timeout (for example 2s) for DDL, and retry.

Row-level locks: UPDATE and DELETE lock the rows they change; SELECT ... FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE and FOR KEY SHARE lock rows explicitly (foreign-key checks use KEY SHARE). NOWAIT fails instead of waiting; SKIP LOCKED skips locked rows, which makes a table a safe concurrent job queue. A deadlock (A waits for B, B waits for A) is detected after deadlock_timeout (1 s) and one transaction is aborted.

whyWhy it matters

Lock queues cause sudden, total outages that look like "the database froze". Knowing which statements take which locks lets you schedule and write migrations that never block traffic.

Use caseReal-world scenario

Workers pull jobs with FOR UPDATE SKIP LOCKED LIMIT 10; each gets different rows without blocking the others. Schema migrations run with SET lock_timeout = '2s' and retry, so they never pile up traffic behind them.

Try itExamples

Run and edit these in the interactive lesson.

BEGIN;
SELECT count(*) FROM customers;
UPDATE products SET stock = stock WHERE id = 1;
ALTER TABLE reviews ADD COLUMN helpful int;
SELECT relation::regclass AS relation, mode, granted
FROM pg_locks
WHERE pid = pg_backend_pid() AND locktype = 'relation' AND relation::regclass::text IN ('customers', 'products', 'reviews')
ORDER BY 1, 2;
ROLLBACK;

BEGIN;
CREATE TABLE jobs (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, payload text, status text NOT NULL DEFAULT 'queued');
INSERT INTO jobs (payload) SELECT 'job ' || g FROM generate_series(1, 20) g;

-- each worker runs this; concurrent workers get disjoint batches
WITH next AS (
  SELECT id FROM jobs
  WHERE status = 'queued'
  ORDER BY id
  LIMIT 5
  FOR UPDATE SKIP LOCKED
)
UPDATE jobs SET status = 'running'
FROM next WHERE jobs.id = next.id
RETURNING jobs.id, jobs.payload;
ROLLBACK;

BEGIN;
SET LOCAL lock_timeout = '2s';
SET LOCAL statement_timeout = '30s';
ALTER TABLE orders ADD COLUMN note text;
SHOW lock_timeout;
ROLLBACK;

SELECT blocked.pid AS blocked_pid,
       blocked.query AS blocked_query,
       blocking.pid AS blocking_pid,
       blocking.state AS blocking_state,
       now() - blocking.xact_start AS blocking_xact_age,
       blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS b(pid) ON true
JOIN pg_stat_activity blocking ON blocking.pid = b.pid;

internalsUnder the hood

Row locks are stored in the tuple header (xmax plus infomask bits), not in shared memory, so locking millions of rows does not exhaust the lock table; when several transactions share a row lock, a MultiXact ID is used. pg_blocking_pids(pid) returns the sessions blocking a given backend.

Avoid / preferDo this, not that

ALTER TABLE orders ADD COLUMN note text;  -- on a busy table

SET lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN note text;  -- retry on timeout

-- session A updates account 1 then 2; session B updates 2 then 1

UPDATE accounts SET ... WHERE id IN (1, 2);  -- or lock rows in id order first:
SELECT id FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;

PitfallsCommon mistakes

• Running ALTER TABLE on a hot table without lock_timeout.
• Building an index with plain CREATE INDEX on a busy table. It blocks writes for the whole build; use CREATE INDEX CONCURRENTLY (outside a transaction block).
• Locking rows in different orders in different code paths, which causes deadlocks. Lock in a consistent order (for example by id).

LimitsWhen not to use it

Do not use LOCK TABLE for application-level mutual exclusion such as "only one job at a time". Advisory locks express that without blocking unrelated queries on the table.

SecuritySecurity notes

Terminating other sessions needs the pg_signal_backend role; grant it to on-call operators instead of handing out superuser.

ChallengeTry it yourself

SELECT pg_try_advisory_lock(42) AS got_lock;
SELECT count(*) AS my_advisory_locks
FROM pg_locks WHERE locktype = 'advisory' AND pid = pg_backend_pid();
SELECT pg_advisory_unlock(42) AS released;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• Explicit locking
• pg_locks