Resolving PostgreSQL Lock Contention: A pg_locks Deep Dive

PostgreSQL lock contention produces the incidents that look the most like mysteries: throughput collapses while CPU idles, one stuck migration queues four hundred sessions behind it, or a nightly job deadlocks with the application at random. None of it is mysterious once you can read pg_locks fluently — every waiter, holder, and queue position is in the catalog, live.

This guide is the deep dive our support engineers train on: the lock modes and what actually conflicts, tracing blocking chains to the true root holder, the deadlock playbook, and the DDL patterns that let you change schemas on hot tables without stopping the business — PostgreSQL 13 through 17.

Everything below is organised the way an incident actually unfolds: read the lock table, trace the chain, classify the root, act, then engineer the failure class away. The conflict rules themselves are documented in the PostgreSQL manual on explicit locking, but no manual tells you which row of that matrix is about to page you at 02:00. That is what a PostgreSQL lock contention playbook is for.

PostgreSQL Lock Contention: Key Takeaways

The five points below summarise how MinervaDB engineers approach PostgreSQL lock contention on production fleets.

• Read the catalog, not the symptoms. Every holder, waiter and queue position in a PostgreSQL lock contention incident is visible live in pg_locks.
• Chase the root, not the neighbour. pg_blocking_pids() walks the wait graph; cancelling the true root drains the entire queue in one step.
• The queue is the danger. One blocked ACCESS EXCLUSIVE request turns a harmless wait into site-wide PostgreSQL lock contention.
• Deadlocks are a design bug. Detection is automatic; prevention is canonical lock ordering, ON CONFLICT, and indexed foreign keys.
• Bound every migration. lock_timeout, CONCURRENTLY index builds and two-phase constraints keep DDL out of the critical path.

Reading pg_locks: Where PostgreSQL Lock Contention Becomes Visible

pg_locks lists every heavyweight lock held or awaited, one row per lock per holder/waiter. The columns that matter: locktype (relation, tuple, transactionid, virtualxid, advisory...), the object identity (relation::regclass, or transactionid), mode, granted (false = waiting), and pid. Raw pg_locks is noisy — every query holds many innocuous locks — so the productive view is always a join to activity, filtered to conflict:

SELECT
    l.pid,
    l.locktype,
    COALESCE(l.relation::regclass::TEXT, l.transactionid::TEXT) AS locked_object,
    l.mode,
    l.granted,
    a.state,
    NOW() - a.xact_start  AS xact_age,
    NOW() - a.query_start AS query_age,
    LEFT(a.query, 60)     AS query_head
FROM pg_locks AS l
JOIN pg_stat_activity AS a
    ON a.pid = l.pid
WHERE NOT l.granted
   OR l.pid IN (SELECT pid FROM pg_locks WHERE NOT granted)
ORDER BY l.granted, xact_age DESC;

Two catalogs answer almost every question you will ask during a PostgreSQL lock contention incident, and they are joined on a single column. The pg_locks view tells you what is held and what is queued; pg_stat_activity tells you who is holding it and what they were running. Join them on pid and PostgreSQL lock contention stops being a wall of rows and becomes a wait graph you can walk.

Mode fluency is what makes the output meaningful.

The eight table-lock modes form a conflict matrix, but production reading compresses to four tiers: ACCESS SHARE (every SELECT; conflicts only with ACCESS EXCLUSIVE); ROW EXCLUSIVE (every INSERT/UPDATE/DELETE; DML coexists with DML); SHARE UPDATE EXCLUSIVE (vacuum, ANALYZE, CREATE INDEX CONCURRENTLY, many ALTERs since 11+ — conflicts with itself and stronger, so two of these queue on each other but not on DML); SHARE and above through ACCESS EXCLUSIVE (plain CREATE INDEX blocks writes; most classic ALTER TABLE forms and DROP/TRUNCATE/VACUUM FULL block everything).

The single most consequential fact in the matrix: ACCESS EXCLUSIVE conflicts with ACCESS SHARE — meaning a waiting ALTER blocks even plain SELECTs behind it in the queue, because lock waits are strictly queued. That queuing rule, not the ALTER's own duration, is what turns a two-millisecond schema change into a site outage: the ALTER waits politely behind one long-running report, and every subsequent query waits behind the ALTER.

Two lock types deserve their own reading. transactionid: every transaction holds an exclusive lock on its own XID; a row-level conflict surfaces as waiting on the holder's transactionid — so "who has my row locked" is answered by that XID's owner, and the intermediate tuple lock you sometimes see is just the queue ticket for the row.

Row locks themselves live in tuple headers, not pg_locks — which is why you cannot enumerate "all row locks currently held" from the catalog (the pgrowlocks extension inspects a table's tuple headers when you truly need it). virtualxid: what CREATE INDEX CONCURRENTLY waits on when it must outwait existing snapshots — a CIC stuck on virtualxid waits is being held hostage by an old transaction, not by DML volume.

The matrix below is the whole of table-level PostgreSQL lock contention in a single grid: eight modes, and the exact intersections where the second requester has to queue. Memorise the bottom row and you have already avoided most PostgreSQL lock contention incidents, because ACCESS EXCLUSIVE is the only mode that conflicts with a plain SELECT.

Blocking Chains: Trace PostgreSQL Lock Contention to Its Root

During a pile-up, the session blocking you is frequently itself blocked. pg_blocking_pids() (9.6+) does the queue arithmetic correctly — including soft edges via the lock queue — and a recursive walk finds the roots:

WITH RECURSIVE chain AS (
    SELECT pid,
           UNNEST(pg_blocking_pids(pid)) AS blocked_by,
           1 AS depth
    FROM pg_stat_activity
    WHERE CARDINALITY(pg_blocking_pids(pid)) > 0
    UNION ALL
    SELECT c.blocked_by,
           UNNEST(pg_blocking_pids(c.blocked_by)),
           c.depth + 1
    FROM chain AS c
    WHERE depth < 10
)
SELECT DISTINCT
    a.pid,
    a.state,
    NOW() - a.xact_start AS xact_age,
    a.wait_event_type,
    LEFT(a.query, 70)    AS query_head,
    CARDINALITY(pg_blocking_pids(a.pid)) = 0 AS is_root
FROM chain AS c
JOIN pg_stat_activity AS a ON a.pid IN (c.pid, c.blocked_by)
ORDER BY is_root DESC, xact_age DESC;

The root is almost always one of four characters: an idle in transaction session (application opened a transaction, took locks, went off to call an API — the single most common root cause in our incident data); a legitimately long transaction (report, batch) holding modest locks that DDL then escalated into a queue; an orphaned prepared transaction (pg_prepared_xacts — survives restarts, invisible in pg_stat_activity, and the reason "we restarted and it's still locked" happens); or a maintenance operation someone forgot was scheduled.

Resolution is a judgment call with a defined ladder: identify the root's owner and work; prefer pg_cancel_backend(pid) (cancels the query, keeps the session) over pg_terminate_backend(pid); know that terminating a long transaction triggers rollback that can itself take time; and gate any termination of unknown sessions through the application owner — our runbooks require naming the session's origin before killing it, because "mystery session" is occasionally the payment-settlement job.

For after-the-fact forensics, log_lock_waits = on (reload) writes a detailed line whenever any wait exceeds deadlock_timeout (default 1 s), naming waiter, holder, and both queries — the difference between reconstructing an incident from evidence and from memory.

Put together, those steps form a repeatable runbook. Every PostgreSQL lock contention incident we handle follows the same five moves — detect, identify, classify, act, prevent — and the classification step is the one that decides which fix applies. Skip it and you end up terminating innocent sessions while the real root holder keeps the queue open.

Deadlocks: Detection Is Automatic, Prevention Is Design

PostgreSQL detects deadlock cycles after deadlock_timeout and kills one participant with error 40P01; the application sees a failed transaction, the log sees the full cycle with both queries. Chronic deadlocks are therefore a design signal, and the fixes are ordering and scope, not parameters. Three production patterns cause the overwhelming majority of deadlocks, and each of them has a standard fix.

• inconsistent lock ordering — transaction A updates rows 1 then 2, B updates 2 then 1; fix by imposing a canonical order (sort the IDs before the loop, or use a single set-based UPDATE ... WHERE id = ANY(sorted_array)).
• upsert races — concurrent INSERT-or-UPDATE logic hand-rolled with SELECT-then-INSERT deadlocks under load; fix with native INSERT ... ON CONFLICT DO UPDATE (or MERGE on 15+ with the caveat that MERGE has its own concurrency semantics — version-check before treating them as equivalent).
• foreign-key crossfire — updating a parent's key columns takes locks that collide with concurrent child inserts' FOR KEY SHARE row locks; fixes include not updating parent keys under concurrency and ensuring FK columns are indexed so child-side checks are brief.

Application posture completes it: keep transactions short, retry 40P01 idempotently a bounded number of times (deadlock retry is normal engineering, not an admission of defeat), and log every retry so frequency is measured — deadlocks per database is right there in pg_stat_database and belongs on the dashboard with a slope alert.

Schema change is where PostgreSQL lock contention is created deliberately, so it is also where it is cheapest to prevent. The patterns below are the ones we allow on hot tables, in the order of priority we deploy them.

Hot-Table DDL: The Patterns That Don’t Queue Production

Schema changes cause the worst lock incidents because they request the strongest locks on the busiest objects. The safe pattern set, in the order of frequency we deploy them:

-- 1. Every migration session, first statement, no exceptions:
SET lock_timeout = '5s';
SET statement_timeout = '60s';
-- A blocked ALTER now fails fast instead of queueing the site;
-- wrap in retry logic and run at low traffic.

Know your ALTER's real lock. Many operations are cheaper than their reputation: adding a nullable column, or (11+) a column with a constant default, is a catalog-only ACCESS EXCLUSIVE for milliseconds — safe with lock_timeout; adding an index is CREATE INDEX CONCURRENTLY, never plain, on any live table. Constraints in two phases: ADD CONSTRAINT ... NOT VALID takes its strong lock only briefly (no scan), then VALIDATE CONSTRAINT runs with SHARE UPDATE EXCLUSIVE — coexisting with DML — for the long scan; the same split applies to foreign keys.

NOT NULL without the table scan (12+): add a validated CHECK (col IS NOT NULL) via the two-phase route, then SET NOT NULL uses the existing constraint as proof, skipping the scan. Type changes and rewrites: anything forcing a table rewrite under ACCESS EXCLUSIVE (most ALTER COLUMN TYPE beyond binary-compatible cases) gets the expand-and-contract treatment on hot tables — new column, dual-write, backfill in batches, swap reads, drop old — or pg_repack-style tooling; the rewrite-in-place is reserved for declared windows.

And the corollary of the queue rule bears repeating as a rule of its own: before any strong-lock DDL, check for long transactions on the target (the chain query above, scoped to the table) — the safest migration script refuses to start while a two-hour report holds ACCESS SHARE on its target.

Advisory Locks: Contention You Built Yourself

Application-defined advisory locks (pg_advisory_lock(key) and variants) route entirely around table semantics, and their pathologies are self-inflicted: session-scoped locks leak when the app crashes without unlocking (they hold until disconnect — which, behind a session-pooling PgBouncer, can be a very long time and a very confusing incident: transaction pool mode + session advisory locks is an outright bug pattern — use pg_advisory_xact_lock under transaction pooling, always); key-space collisions where two features hash into the same bigint; and convoy patterns where a single global advisory key serializes what should have been sharded work.

They appear in pg_locks with locktype = 'advisory' and the key split across classid/objid — maintain a registry of who owns which key range, because the catalog cannot tell you which microservice a number belongs to. Used well — leader election, migration mutexes, dedup gates — they are excellent; every one of them is also a queue you designed, so instrument wait time on acquisition like any other lock.

PostgreSQL Lock Contention Observability: Alerts That Fire Before the Pile-Up

Every technique above is reactive without a monitoring layer shaped for lock health. The design we deploy has three tiers matched to three time horizons. Real-time (seconds): an exporter query counting sessions with CARDINALITY(pg_blocking_pids(pid)) > 0 and, separately, the age of the oldest ungranted lock — alert when blocked-session count exceeds a workload-calibrated floor for 30+ seconds, and page when the oldest wait crosses your application timeout (past that point users are already failing; the alert is about containment).

Pair it with a root-holder panel driven by the recursive chain query, so the responder opens the dashboard to a named PID, age, and query head rather than a number. Behavioral (minutes–hours): log_lock_waits lines shipped and parsed give you wait frequency by relation and by statement fingerprint — the "orders table lock waits doubled this week" trend that precedes incidents; add pg_stat_database.deadlocks slope and, from wait-event sampling, Lock-class share over time.

Preventive (deploy-time): the highest-leverage alerts fire in CI, not production — a migration linter that rejects DDL without lock_timeout, flags rewriting ALTERs on tables above a size threshold, and requires the two-phase constraint pattern; teams that gate here convert their lock incident class from "production page" to "failed pipeline," which is the entire point.

One calibration note: blocked-session count has a healthy baseline on busy OLTP (brief row-lock queues are normal commerce), so alert on deviation and duration, never on nonzero — presence-based lock alerts are how ops channels learn to mute the word "lock."

Case Study: PostgreSQL Lock Contention That Took Down Reads

SaaS platform, PostgreSQL 15, a routine deploy adds a column with a volatile default to a 900 M-row table — which forces a rewrite — but the outage mechanism wasn't the rewrite: the ALTER queued behind a 47-minute analytics transaction holding ACCESS SHARE, and within 90 seconds, 600 application sessions queued behind the ALTER's pending ACCESS EXCLUSIVE. Every SELECT on the table stalled; the app's health checks (which read that table) failed; the orchestrator restarted app pods, which reconnected and queued more sessions.

The on-call's dashboard showed the chain query resolving to one root PID — the analytics session — with the ALTER second in queue. Containment: pg_cancel_backend() on the ALTER (not the analytics session — cancelling the innocent long reader would have surrendered the queue to the ALTER and rewritten 900 M rows mid-peak), traffic recovered in under a minute, and the migration was re-cut.

The re-cut is the instructive part: session-scoped lock_timeout = '3s' with bounded retries; the column added as nullable-no-default (catalog-only, milliseconds); the default applied to new writes at the application layer; backfill in 10 K-row batches off-peak; then the two-phase CHECK → SET NOT NULL finish — total ACCESS EXCLUSIVE time across the entire sequence under one second, on a table that never stopped serving.

The postmortem's systemic fixes were the CI linter above and a pre-DDL gate that queries for transactions older than five minutes touching the target table — the ninety-second check that would have converted the outage into a rescheduled deploy.

Quick Reference

PostgreSQL Lock Contention FAQ

How do I find PostgreSQL lock contention right now? Ask pg_stat_activity for every session where pg_blocking_pids(pid) returns a non-empty array, then walk that array upwards until you reach a session that nobody is blocking. That session is the root of the chain.

What is the fastest way to stop a PostgreSQL lock contention pile-up? Cancel whatever is holding the queue open — usually the queued DDL or the idle-in-transaction root — with pg_cancel_backend(). Terminate only if the cancel is ignored, and never before you know whose session it is.

Is PostgreSQL lock contention the same thing as a deadlock? No. Contention is a queue that will eventually clear; a deadlock is a cycle that never clears, which is why PostgreSQL breaks it for you by aborting one participant with error 40P01.

Is SELECT ... FOR UPDATE lock contention the same thing as table locks? It's row-level: waiters queue on the holder's transactionid as described above. SKIP LOCKED and NOWAIT (both long-standard) turn queue-worker patterns from convoys into concurrency — the single best fix for job-table contention.

Does read committed vs repeatable read change contention? Writers block writers identically. Higher isolation adds serialization failures (40001) rather than more blocking — and the same retry discipline as deadlocks handles them.

Why did my two-millisecond ALTER take the site down? The queue rule: your ALTER waited behind one long reader, and everything else waited behind your ALTER's queued ACCESS EXCLUSIVE request. lock_timeout on the migration session is the entire fix.

Can I observe lock contention historically, not just live? log_lock_waits gives per-incident lines; wait-event sampling (see our PostgreSQL wait events deep dive) gives the statistical view — Lock-class share over time — and pg_stat_database.deadlocks trends the cycles. All three belong in a production observability baseline.

Do these techniques differ on RDS, Aurora, or Cloud SQL? Lock semantics are engine-level and identical. Differences are peripheral: parameter access via parameter groups, log access via cloud interfaces, and on Aurora, reader endpoints run read-only sessions whose ACCESS SHARE locks still participate in exactly the queue dynamics above.

Related PostgreSQL performance deep dives

PostgreSQL lock contention rarely travels alone, and these companion guides cover the systems it usually shows up with.

• PostgreSQL wait event analysis with pg_stat_activity — the statistical view of Lock-class waits over time.
• PostgreSQL autovacuum tuning — why SHARE UPDATE EXCLUSIVE quietly delays your migrations.
• PostgreSQL slow query troubleshooting — finding the long transactions that become root holders.
• PgBouncer pooling and pool saturation — why session-scoped advisory locks and transaction pooling collide.

PostgreSQL Lock Contention as an Engineering Discipline

MinervaDB builds PostgreSQL lock contention hygiene into fleets: migration frameworks with mandatory lock_timeout, blocking-chain alerting keyed to root-cause identification, deadlock trend review, and hot-table DDL patterns codified into your CI — under PostgreSQL consulting and 24×7 support. If schema changes still get scheduled around fear, or your incident channel knows the phrase "everything is stuck," we should talk: minervadb.com/contact-us · contact@minervadb.com.

Rehearse every migration against staging with production-shaped concurrency first, wrap destructive session terminations in the confirmation gates your runbooks define, and keep verified backups and a tested DR posture current before schema-change windows.