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 quickly. PostgreSQL breaks real deadlocks on its own after deadlock_timeout.

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 more detail.

PostgreSQL has eight table-level lock modes:

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 EXCLUSIVEmany ALTER TABLE forms, DROP, TRUNCATE, VACUUM FULLEverything, including SELECT

ACCESS EXCLUSIVE is the most restrictive mode because it conflicts with every other mode. PostgreSQL queues lock requests, so a pending ACCESS EXCLUSIVE request (an ALTER waiting behind one slow query) blocks new requests lining up behind it. Ordinary SELECTs can then wait too. One migration and one long query can cause all requests on a table to wait. (See which ALTER TABLE statements lock.)

  • 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 to make them less likely.

  • 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 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.

  • How do I find what is blocking a query in PostgreSQL?

    Use pg_blocking_pids(pid), available since PostgreSQL 9.6, which returns the process IDs blocking a given backend. Join it to pg_stat_activity to see the blocking query and how long it has run. pg_locks shows the raw granted and waiting locks.

  • Why does one ALTER TABLE block even simple SELECTs?

    Many forms of ALTER TABLE need an ACCESS EXCLUSIVE lock, which conflicts with every other lock mode including the ACCESS SHARE that a SELECT takes. If it has to wait, its pending request queues ahead of new lock requests, so SELECTs arriving after it also wait, one blocked ALTER can stall all access to the table.

  • How does PostgreSQL handle deadlocks?

    PostgreSQL detects true deadlocks automatically. After a wait exceeds deadlock_timeout (1 second by default) it checks for a cycle, and if two transactions are each waiting on a lock the other holds, it aborts one with a deadlock detected error so the other can proceed.

  • How do I reduce lock contention?

    Keep transactions short so locks are held briefly, acquire locks in a consistent order to avoid deadlocks, set a lock_timeout so a blocked statement fails fast instead of queuing, and avoid leaving transactions idle in transaction.