TopolearnLock waits are behind most mysterious production stalls, from job queues to migrations that freeze a site.
MVCC removes reader/writer blocking, but writers still lock. UPDATE, DELETE and SELECT ... FOR UPDATE take row-level locks held until the transaction ends. A second transaction trying to modify the same row waits.
Explicit row locks come in four strengths: FOR UPDATE (exclusive), FOR NO KEY UPDATE (taken by ordinary updates that do not change keys), FOR SHARE and FOR KEY SHARE (taken by foreign-key checks). Two modifiers change waiting behaviour:
NOWAITerrors immediately instead of waiting.SKIP LOCKEDsilently skips locked rows, which turns a table into a safe job queue: many workers runSELECT ... FOR UPDATE SKIP LOCKED LIMIT 1and never grab the same job.
Table-level locks are taken by every statement. Most are compatible with each other, but many ALTER TABLE forms, DROP, TRUNCATE and VACUUM FULL need ACCESS EXCLUSIVE, which conflicts with everything, including plain SELECT. Locks queue in order: a migration waiting behind one long transaction blocks every query that arrives after it. Always set lock_timeout in migrations so they fail fast and retry instead of freezing the application.
A deadlock occurs when two transactions each hold a lock the other needs. PostgreSQL checks for cycles after deadlock_timeout (1 second by default) and aborts one transaction with ERROR: deadlock detected (SQLSTATE 40P01). Prevent them by locking rows in a consistent order (for example by ascending id) and keeping transactions short; handle the occasional one with a retry.
For locks on things that are not rows, such as "only one worker runs the nightly job", use advisory locks: pg_try_advisory_lock(42).
Diagnose with pg_locks and pg_blocking_pids(pid).
Example
-- Job queue: each worker claims a different pending job
BEGIN;
SELECT id, payload FROM jobs
WHERE status = 'queued'
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- ... do the work ...
UPDATE jobs SET status = 'done' WHERE id = 7;
COMMIT;
-- Deadlock: A locks order 101 then 102; B locks 102 then 101
-- ERROR: deadlock detected (SQLSTATE 40P01)
-- Fix: always lock in the same order
SELECT id FROM orders WHERE id IN (101, 102) ORDER BY id FOR UPDATE;
-- Migrations: fail fast instead of queueing behind long transactions
SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN note text;
-- Who blocks whom?
SELECT pid, pg_blocking_pids(pid) AS blocked_by, state, left(query, 50)
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;Remember
- Row locks from writes and FOR UPDATE last until commit or rollback
- FOR UPDATE SKIP LOCKED builds safe concurrent job queues
- ACCESS EXCLUSIVE locks queue and block even reads; use lock_timeout for DDL
- Prevent deadlocks with a consistent lock order; retry on 40P01
Watch out for
- Running ALTER TABLE on a hot table without lock_timeout, so it queues behind a long query and blocks all traffic.