Understanding lock contention in PostgreSQL
Quick answer: Lock contention is queries waiting on locks other transactions hold. Find the culprit with
pg_blocking_pids(pid), watch for anACCESS EXCLUSIVErequest (fromALTER,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 afterdeadlock_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_ageFROM pg_stat_activity blockedJOIN 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.
Lock modes and why they conflict
Section titled “Lock modes and why they conflict”PostgreSQL has eight table-level lock modes:
| Lock mode | Taken by | Conflicts with |
|---|---|---|
ACCESS SHARE | SELECT | Only ACCESS EXCLUSIVE |
ROW EXCLUSIVE | INSERT / UPDATE / DELETE | SHARE and stronger |
SHARE UPDATE EXCLUSIVE | VACUUM (not full), ANALYZE, CREATE INDEX CONCURRENTLY | Itself and stronger |
SHARE | CREATE INDEX (non-concurrent) | Writes (ROW EXCLUSIVE) |
ACCESS EXCLUSIVE | many ALTER TABLE forms, DROP, TRUNCATE, VACUUM FULL | Everything, 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.)
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 adeadlock detectederror 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.
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_timeoutso 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.