Skip to content

Understanding lock contention in PostgreSQL

Last updated

View as Markdown

Quick answer: Lock contention is queries waiting on locks other transactions hold. Find the culprit with pg_blocking_pids(pid), watch for an ACCESS EXCLUSIVE request (from ALTER, DROP, TRUNCATE, VACUUM FULL) that queues and blocks everything behind it, and keep transactions short so locks release fast. Real deadlocks PostgreSQL breaks on its own after deadlock_timeout.

Find the blocker fast

Section titled “Find the blocker fast”

When a query hangs, you do not need to parse pg_locks by hand. pg_blocking_pids() (since PostgreSQL 9.6) gives you the blockers directly:

SELECT
    blocked.pid            AS blocked_pid,
    blocked.query          AS blocked_query,
    blocking.pid           AS blocking_pid,
    blocking.query         AS blocking_query,
    now() - blocking.xact_start AS blocking_txn_age
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY (pg_blocking_pids(blocked.pid))
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;

That maps each waiting query to the transaction holding it up. The raw pg_locks view shows the underlying granted (granted = true) and waiting (granted = false) locks if you need the detail.

Lock modes and why they conflict

Section titled “Lock modes and why they conflict”

You do not memorize eight modes, you memorize the one that freezes the table. PostgreSQL has eight table-level lock modes; the ones that matter most:

Lock modeTaken byConflicts with
ACCESS SHARESELECTOnly ACCESS EXCLUSIVE
ROW EXCLUSIVEINSERT / UPDATE / DELETESHARE and stronger
SHARE UPDATE EXCLUSIVEVACUUM (not full), ANALYZE, CREATE INDEX CONCURRENTLYItself and stronger
SHARECREATE INDEX (non-concurrent)Writes (ROW EXCLUSIVE)
ACCESS EXCLUSIVEALTER TABLE, DROP, TRUNCATE, VACUUM FULLEverything, including SELECT

ACCESS EXCLUSIVE is the one that bites: it conflicts with every other mode. And because PostgreSQL queues lock requests, a pending ACCESS EXCLUSIVE (an ALTER waiting behind one slow query) blocks the new requests lining up behind it, so ordinary SELECTs start waiting too. One migration plus one long query can freeze a table. (See which ALTER TABLE statements lock.)

Deadlocks vs plain waiting

Section titled “Deadlocks vs plain waiting”
  • Plain contention: A waits for B; B finishes; A proceeds. Slow, not fatal.
  • Deadlock: A holds lock 1 and wants lock 2; B holds lock 2 and wants lock 1. Neither can proceed. PostgreSQL notices, after a wait passes deadlock_timeout (1s by default) it runs deadlock detection, finds the cycle, and aborts one transaction with a deadlock detected error so the other continues.

Deadlocks usually come from two code paths locking the same rows in opposite order. Lock rows in a consistent order and they mostly disappear.

How do I reduce contention?

Section titled “How do I reduce contention?”
  • Keep transactions short. The less time a lock is held, the less anything waits. Never hold a transaction open across a network call or user think-time. That is the idle in transaction trap.
  • Lock in a consistent order across your code to avoid deadlocks.
  • Set lock_timeout so a blocked statement fails fast instead of queuing and stalling others.

Why does aI-generated code create lock contention?

Section titled “Why does aI-generated code create lock contention?”

Your AI coding agent (Claude Code, Cursor) writes each transaction to be correct on its own. It cannot see that two endpoints update the same two tables in opposite orders (a deadlock waiting to happen), or that a migration will take ACCESS EXCLUSIVE while a report holds the table. Lock interactions are a property of concurrent execution across your whole system, not of any one function the model reads.

FAQ

Section titled “FAQ”
  • How do I find what is blocking a query?
    pg_blocking_pids(pid) (since PostgreSQL 9.6) returns the blocking process IDs; join it to pg_stat_activity for the blocking query. pg_locks shows the raw granted/waiting locks.
  • Why does one ALTER TABLE block even simple SELECTs?
    ALTER TABLE needs ACCESS EXCLUSIVE, which conflicts with every mode including the ACCESS SHARE a SELECT takes. A pending request queues ahead of new ones, so SELECTs behind it wait too.
  • How does PostgreSQL handle deadlocks?
    It detects them: after a wait exceeds deadlock_timeout (1s default) it looks for a cycle and aborts one transaction with a deadlock detected error.
  • How do I reduce lock contention?
    Short transactions, consistent lock ordering, a lock_timeout, and no idle-in-transaction sessions.
Section titled “Related”