TopolearnWhen the app hangs, the cause is usually a lock chain you can find in a minute with the right query.
Every statement takes locks. Ordinary reads and writes take weak table locks that don't conflict with each other, plus row locks for modified rows. Schema changes take ACCESS EXCLUSIVE, which conflicts with everything — including plain SELECT.
Lock waits queue: if ALTER TABLE waits behind a long transaction, every new query on that table then waits behind the ALTER TABLE. That's how a "quick migration" takes down an application. Defence: SET lock_timeout = '3s' before DDL, and retry.
To diagnose a hang:
pg_stat_activityshows every session:state(active,idle in transaction),wait_event_type(Lockmeans waiting on a lock),xact_startand the currentquery.pg_blocking_pids(pid)returns the sessions blocking a given one.pg_lockshas the raw lock table.pg_cancel_backend(pid)cancels a query;pg_terminate_backend(pid)ends the session.
Row-level locking clauses: FOR UPDATE (will modify), FOR NO KEY UPDATE (weaker, used by ordinary updates), FOR SHARE. Add NOWAIT to fail immediately, or `SKIP LOCKED` to skip rows other sessions hold. SKIP LOCKED turns a table into a reliable concurrent job queue: each worker claims different rows without blocking the others.
Set log_lock_waits = on so waits longer than deadlock_timeout are logged.
Example
CREATE TABLE job (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
payload text, done boolean NOT NULL DEFAULT false);
INSERT INTO job (payload) SELECT 'job ' || g FROM generate_series(1, 5) g;
-- each worker runs this; concurrent workers get different rows
BEGIN;
SELECT id, payload FROM job
WHERE NOT done ORDER BY id
LIMIT 1 FOR UPDATE SKIP LOCKED; -- 1 | job 1
UPDATE job SET done = true WHERE id = 1;
COMMIT;
-- who is blocked, and by whom?
SELECT pid, pg_blocking_pids(pid) AS blocked_by, wait_event_type,
state, now() - xact_start AS xact_age, left(query, 60) AS query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;Remember
- Lock waits queue: DDL behind a long transaction blocks everyone after it
- pg_stat_activity + pg_blocking_pids find the blocker
- FOR UPDATE SKIP LOCKED builds safe concurrent job queues
Watch out for
- Terminating the blocked session instead of the one holding the lock.