Locking and Contention

Every backend engineer eventually stares at a Postgres connection pile-up—pg_stat_activity full of rows in lock wait state, latency spiking, and no obvious query to blame. Locking in Postgres is rich, mostly automatic, and almost invisible until it becomes your incident. Understanding the lock hierarchy before the incident gives you both a mental model for what's happening and the vocabulary to diagnose it quickly.

The Lock Hierarchy

Postgres maintains two distinct lock systems: heavyweight locks (tracked in shared memory, visible in pg_locks) and lightweight locks (LWLocks, internal to the engine, not user-visible). Backend engineers interact primarily with heavyweight locks, which Postgres further subdivides into relation-level and tuple-level locks.

The key insight is that most DDL operations (ALTER TABLE, TRUNCATE, DROP) require AccessExclusiveLock, which conflicts with every other lock mode—including plain SELECT. This is why a long-running analytical query can block a schema migration, and why that migration then blocks every subsequent connection waiting to read the table.

Row-Level Locks vs Table-Level Locks

Row-level locks protect individual tuples during concurrent writes. They are acquired implicitly by UPDATE, DELETE, and SELECT FOR UPDATE/SHARE, and they are stored directly in the tuple header (not in shared memory), making them extremely lightweight.

Table-level locks (relation locks) protect the table structure. They are acquired implicitly by DML (at a weaker mode) and by DDL (at a stronger mode). The conflict matrix is what matters in practice:

The practical implication: a single unfinished BEGIN with no statements still holds its transaction snapshot, and any DDL that arrives later queues behind it—then every subsequent read queues behind the DDL.

SELECT FOR UPDATE: Intentional Row Locking

SELECT FOR UPDATE acquires a ForUpdate tuple lock on every row returned. Subsequent transactions that try to lock, update, or delete those rows will block until the first transaction commits or rolls back.

-- Claim the next pending job atomically - safe for concurrent workers
BEGIN;
 
SELECT id, payload
FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;
 
-- Process the job here, then:
UPDATE jobs SET status = 'processing', started_at = now() WHERE id = $1;
 
COMMIT;

SKIP LOCKED is the critical addition for queue-style patterns. Without it, all workers pile up on the same row. With it, each worker atomically skips rows already locked by another session and moves to the next available one.

FOR SHARE is the lighter alternative when you need to prevent concurrent updates but don't need exclusive control—foreign key checks use this mode internally.

-- Read-then-update with conflict detection (optimistic alternative)
SELECT version, balance FROM accounts WHERE id = $1 FOR NO KEY UPDATE;
-- If version matches your expected value, proceed; else retry
UPDATE accounts SET balance = balance - $2, version = version + 1
WHERE id = $1 AND version = $3;

Lock Waits and Timeouts

Postgres does not set a default lock timeout. A transaction that cannot acquire a lock will wait indefinitely unless you configure otherwise. Two settings matter:

-- Per-session: fail fast rather than queue forever
SET lock_timeout = '2s';
 
-- Per-session: fail if the whole statement takes too long
SET statement_timeout = '30s';
 
-- Per-session: for long-running explicit locks, give up quickly
SET deadlock_timeout = '1s';  -- default is 1s globally, but can be overridden

Set lock_timeout at the application session level for all DDL migrations. A queued migration that holds AccessExclusiveLock mode in the wait queue blocks every subsequent read—setting lock_timeout = '5s' on the migration ensures it aborts rather than becoming a traffic dam.

Deadlocks

A deadlock occurs when transaction A holds a lock that B wants, and B holds a lock that A wants. Postgres detects this automatically every deadlock_timeout milliseconds (default 1 s) and terminates one of the transactions with:

ERROR: deadlock detected
DETAIL: Process 12345 waits for ShareLock on transaction 67890; blocked by process 67890.

The most common cause in application code is acquiring locks in inconsistent row order:

-- Transaction A: locks row 1, then row 2
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
 
-- Transaction B (concurrent): locks row 2, then row 1 -- deadlock potential
UPDATE accounts SET balance = balance - 50 WHERE id = 2;
UPDATE accounts SET balance = balance + 50 WHERE id = 1;

The fix is deterministic ordering: always lock rows in the same order (e.g., ascending by primary key). For multi-row updates, sort the IDs in the application layer before issuing statements.

Diagnosing Contention

-- Find blocked queries and what they're waiting on
SELECT
    blocked.pid,
    blocked.query,
    blocked.wait_event_type,
    blocked.wait_event,
    blocking.pid AS blocking_pid,
    blocking.query AS blocking_query
FROM pg_stat_activity AS blocked
JOIN pg_stat_activity AS blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE blocked.wait_event_type = 'Lock';
 
-- Show all current locks with relation names
SELECT
    l.pid,
    l.mode,
    l.granted,
    c.relname
FROM pg_locks l
JOIN pg_class c ON c.oid = l.relation
WHERE l.relation IS NOT NULL
ORDER BY l.granted, c.relname;

Look for granted = false rows—those are the waiting transactions. The matching granted = true row with the same relation and a conflicting mode is what's blocking them.

Advisory Locks

Advisory locks are application-defined locks with no automatic release semantics. Postgres enforces the mutual exclusion; your application defines the meaning.

-- Session-level: held until explicitly released or session ends
SELECT pg_advisory_lock(12345);
-- ... critical section ...
SELECT pg_advisory_unlock(12345);
 
-- Transaction-level: released on COMMIT or ROLLBACK automatically
SELECT pg_advisory_xact_lock(hashtext('report-generation'));

Advisory locks are useful for serializing background jobs, report generation, or any workload where you want mutex semantics without a dedicated table row to lock.

Key Takeaways

• AccessExclusiveLock (DDL) conflicts with everything, including reads—keep DDL transactions short and set lock_timeout to prevent them from becoming traffic dams.
• SELECT FOR UPDATE SKIP LOCKED is the correct primitive for queue-style job claiming; plain FOR UPDATE serializes all workers onto the same row.
• Deadlocks are always application-layer ordering bugs—locking rows in consistent order across all transactions eliminates them.
• pg_stat_activity joined to pg_blocking_pids() is your first stop for live contention diagnosis; pg_locks joined to pg_class shows what relation each lock covers.
• Set lock_timeout and statement_timeout at the session level in all production connections—Postgres will wait forever by default.
• Advisory locks give you application-defined mutexes without a sentinel table; prefer transaction-level advisory locks so they release automatically on error.