What is PostgreSQL lock contention, and which locks can you actually see?

Lock contention happens when a session asks for a lock that conflicts with one already held, and has to wait. The waiting is the problem. A query that normally takes 2 ms can sit for minutes while the CPU stays idle. On-call dashboards then show it as "slow," even though it never ran. For engineers who own migrations and incident response, the first rule is this: the slow query is usually the victim, not the cause. Something else holds the lock.

PostgreSQL has three kinds of locks, and each one leaves a different trace.

  • Heavyweight locks cover relations, tuples, transaction IDs, advisory locks and a few other object types. These are the locks listed in pg_locks. A session waiting on one shows wait_event_type = 'Lock' in pg_stat_activity.
  • Lightweight locks (LWLocks) protect shared-memory structures such as buffer mappings and WAL insertion. They never appear in pg_locks. You can only see them as wait events with type LWLock. Contention here points to throughput or I/O pressure. It does not mean one transaction is blocking another.
  • Row locks are not stored in the lock table at all. When a transaction updates or locks a row, PostgreSQL records its transaction ID in the tuple header (xmax). This means a million locked rows cost nothing in shared memory.

Because row locks live in the tuple header, a session waiting for a row lock cannot wait on the row itself. It requests a share lock on the holder's transactionid instead, and that lock is released only when the holder commits or rolls back. In pg_locks, a row lock wait therefore shows up as an ungranted transactionid lock. You may also see a short-lived tuple lock alongside it.

Who should use these techniques?

The queries, timeouts and migration patterns in this article are for anyone who can cause a lock wait or has to clear one. In practice that covers four groups:

  • Application engineers who write transactional code, especially code that mixes database writes with network calls, ORMs that open transactions implicitly, or background jobs that hold transactions open across batches. They are the people most likely to introduce an idle in transaction blocker, and the fix belongs in their code.
  • Engineers who write and ship schema migrations. Any ALTER TABLE, TRUNCATE or index build on a busy table can trigger the lock-queue pileup described below. The lock_timeout retry pattern is aimed at this group.
  • On-call engineers and SREs who get paged for "the database is slow." They need a fast way to tell a lock wait from CPU, I/O or LWLock pressure, find the root blocker, and decide between cancelling and terminating it.
  • DBAs and platform teams who own server configuration, per-role settings and connection poolers. They decide where statement_timeout and idle_in_transaction_session_timeout apply and whether log_lock_waits is on.

Some of these steps need privileges. Without superuser rights, pg_stat_activity shows the query column only for sessions of your own role unless you have membership in pg_read_all_stats. pg_cancel_backend and pg_terminate_backend work on sessions of your own role, and membership in pg_signal_backend allows signalling other non-superuser sessions. Check that the on-call role has these grants before an incident, not during one.

If your workload is read-mostly with no long transactions and no DDL during traffic, you will rarely need most of this. The diagnostic queries are still worth keeping in a runbook.

How does PostgreSQL locking work?

Every statement acquires the locks it needs before it touches data, and you never request them explicitly except through LOCK TABLE, SELECT ... FOR UPDATE and similar clauses. The mechanics behind a wait are the same each time:

  1. Request. The backend asks the lock manager for a lock on an object (a relation, a transaction ID, a tuple, an advisory key) in a specific mode. Heavyweight locks are tracked in a shared-memory lock table that every backend can see. Weak relation locks such as ACCESS SHARE and ROW EXCLUSIVE can be recorded through a per-backend fast path when no conflicting lock exists. They still appear in pg_locks, where the fastpath column marks them.
  2. Conflict check. The lock manager compares the requested mode with the modes already granted on that object, and with requests already waiting in the queue for it. If there is no conflict, the lock is granted immediately.
  3. Wait. If there is a conflict, the backend adds itself to that object's wait queue and sleeps. It is not using CPU. pg_stat_activity shows wait_event_type = 'Lock' and a wait_event naming the lock type, such as relation or transactionid.
  4. Deadlock check. After waiting for deadlock_timeout, the backend checks whether the waits form a cycle. If they do, one participant gets an error and its transaction is aborted. If not, it keeps waiting.
  5. Release. Locks taken by a statement are held until the transaction ends, not until the statement ends. On COMMIT or ROLLBACK, all of the transaction's locks are released at once and waiting backends are woken in queue order. Session-level advisory locks are the main exception: they persist until explicitly released or the session ends.

Two consequences follow, and the rest of this article builds on them. First, how long a lock is held depends on how long the transaction is open, not how long the query runs. Second, because waiters also respect requests queued ahead of them, one waiting request for a strong lock can block sessions that do not conflict with any lock actually held. pg_blocking_pids reads the lock table and reports both kinds of blocking, which is why it is the starting point for diagnosis.

How do table lock modes conflict, and why does one ALTER TABLE block every SELECT?

Table-level locks are taken implicitly by every statement, and they differ in what they conflict with. Four modes cover most production incidents:

  • ACCESS SHARE: taken by SELECT.
  • ROW EXCLUSIVE: taken by INSERT, UPDATE, DELETE.
  • SHARE UPDATE EXCLUSIVE: taken by VACUUM (non-FULL) and CREATE INDEX CONCURRENTLY.
  • ACCESS EXCLUSIVE: taken by DROP TABLE, TRUNCATE, VACUUM FULL and most forms of ALTER TABLE.
Requested ↓ / Held → ACCESS SHARE ROW EXCLUSIVE SHARE UPDATE EXCLUSIVE ACCESS EXCLUSIVE
ACCESS SHARE ok ok ok conflict
ROW EXCLUSIVE ok ok ok conflict
SHARE UPDATE EXCLUSIVE ok ok conflict conflict
ACCESS EXCLUSIVE conflict conflict conflict conflict

ACCESS EXCLUSIVE is the only mode that blocks plain reads. SHARE UPDATE EXCLUSIVE conflicts with itself, so two CREATE INDEX CONCURRENTLY builds on the same table cannot run at once. A manual VACUUM also waits behind autovacuum on that table.

The outage pattern comes from the wait queue. A new lock request has to wait if it conflicts with a lock that is already held. It also has to wait if it conflicts with a request already queued ahead of it. Without that second rule, a steady stream of readers could starve the ALTER TABLE indefinitely. The cost is this chain:

  1. Session A: long SELECT. Holds ACCESS SHARE. Granted.
  2. Session B: ALTER TABLE. Wants ACCESS EXCLUSIVE. Waiting on A.
  3. Sessions C, D, E…: ordinary SELECTs. Want ACCESS SHARE. Waiting on B.

A [granted] ← B [waiting] ← C, D, E … [waiting]

B has not changed anything yet, but every new query against the table now stalls until A finishes.

                    Lock queue on one table (◄── = waits for)

┌─────────────────┐   ┌────────────────────────┐   ┌──────────────────────┐
│ Session A       │   │ Session B              │   │ Sessions C, D, E ... │
│ SELECT (long)   │◄──│ ALTER TABLE            │◄──│ SELECT               │
│ ACCESS SHARE    │   │ ACCESS EXCLUSIVE       │   │ ACCESS SHARE         │
│ GRANTED         │   │ WAITING on A           │   │ WAITING on B         │
└─────────────────┘   └────────────────────────┘   └──────────────────────┘

 B    : ACCESS EXCLUSIVE conflicts with A's held ACCESS SHARE.
 C..E : ACCESS SHARE is compatible with A, but conflicts with
        B's ACCESS EXCLUSIVE request queued ahead of them.

 Release order:
 A commits ──► B granted, runs ALTER, commits ──► C, D, E granted

Session B's queued ACCESS EXCLUSIVE request waits on Session A's granted ACCESS SHARE lock, and every later SELECT queues behind B even though it does not conflict with A.

How do you find the root blocker with pg_blocking_pids and pg_stat_activity?

That chain is what you need to reconstruct during an incident, and PostgreSQL can report it directly. pg_blocking_pids(pid) returns an array of the process IDs blocking a given backend. To list every waiting session with enough context to act on, select from pg_stat_activity where cardinality(pg_blocking_pids(pid)) > 0, and return pid, the blocker array, wait_event_type, wait_event, state, xact_start, application_name, client_addr and query. A wait_event_type of Lock confirms a heavyweight lock wait rather than I/O or LWLock pressure.

To find the root, follow the arrays. In the scenario above, C, D and E list B, because the function also reports sessions queued ahead in the wait queue. B lists A. A has an empty array, so A is the root. That is the session to investigate, and its xact_start usually shows a transaction that has been open far longer than its current query suggests.

When you need to know which object and lock mode are involved, query pg_locks directly for locktype, relation::regclass, mode, granted and pid. Rows with granted = false are the waiters. This detail matters when a session holds locks on several relations, or when the contention is on a non-relation lock such as a transactionid.

Both views are point-in-time snapshots, and reading lock state briefly locks the lock manager's shared data. Use these queries for diagnosis, not for high-frequency polling. Run as one query, they replace the guesswork of scanning hundreds of active sessions by hand and give you a single PID to act on within seconds.

-- 1. Every waiting session, with the PIDs blocking it and enough context to act on.
SELECT pid,
       pg_blocking_pids(pid)        AS blocked_by,
       wait_event_type,             -- 'Lock' = heavyweight lock wait
       wait_event,
       state,
       now() - xact_start           AS xact_age,
       application_name,
       client_addr,
       left(query, 80)              AS query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0
ORDER BY xact_start;

-- 2. Root blockers: sessions that block others but are not waiting themselves.
SELECT a.pid, a.state, now() - a.xact_start AS xact_age,
       a.application_name, a.client_addr, left(a.query, 80) AS last_query
FROM pg_stat_activity a
WHERE cardinality(pg_blocking_pids(a.pid)) = 0
  AND EXISTS (SELECT 1 FROM pg_stat_activity w
              WHERE a.pid = ANY (pg_blocking_pids(w.pid)));

-- 3. Lock-level detail: which object and mode. granted = false rows are waiters.
-- Reads lock-manager state; run during diagnosis, not in a tight polling loop.
SELECT l.locktype,
       l.relation::regclass AS relation,   -- NULL for e.g. transactionid locks
       l.transactionid,
       l.mode,
       l.granted,
       l.pid
FROM pg_locks l
WHERE l.pid <> pg_backend_pid()
ORDER BY l.granted, l.pid;

Three diagnostic queries. The first lists waiting sessions and their blockers. The second finds the root blocker, meaning a session that blocks others but is not waiting itself. The third reads pg_locks to show the lock type, relation, mode and granted flag behind the contention.

Why is 'idle in transaction' the most common root blocker?

When the root blocker from that query turns out to be idle, the cause is usually a transaction that took locks and then stopped issuing SQL. PostgreSQL holds row and table locks until COMMIT or ROLLBACK, no matter how long the client waits between statements. A typical sequence is BEGIN, an UPDATE on an order row, a call to a payment API or message broker, and then the commit. If the external call takes 30 seconds, every writer that needs that row waits 30 seconds. Any ALTER TABLE queued behind it then blocks readers too.

In pg_stat_activity these sessions show state = 'idle in transaction', or 'idle in transaction (aborted)' after an error the client never rolled back. The xact_start value is old, and state_change is the moment the session went quiet. The query column is misleading: it holds the last statement executed, often a harmless SELECT, not the UPDATE that took the lock. Use xact_start to judge how long the locks have been held, and don't trust query as the cause.

To list these sessions with the oldest transactions first, run:

SELECT pid, usename, application_name, client_addr, now() - xact_start AS xact_age, now() - state_change AS idle_for, query FROM pg_stat_activity WHERE state LIKE 'idle in transaction%' ORDER BY xact_start;

Use application_name and client_addr to find the service responsible, then fix the code. Common causes include:

  • ORM sessions that open a transaction implicitly on the first query and stay open for the whole request.
  • Error paths that catch an exception but never roll back.
  • Connection pools that return connections with a transaction still open.
  • Network I/O performed inside a transaction block.

Killing these sessions only clears the symptom. The bug will cause the same problem again.

import psycopg

DSN = (
    "host=db.internal dbname=shop user=orders_svc password=change-me "
    "application_name=orders-api "
    "options='-c idle_in_transaction_session_timeout=30s'"
)


def charge_order(order_id, payments):
    # autocommit=True: no implicit transaction is opened by the first query,
    # so nothing stays 'idle in transaction' between statements.
    with psycopg.connect(DSN, autocommit=True) as conn:
        row = conn.execute(
            "SELECT amount_cents FROM orders WHERE id = %s AND status = 'pending'",
            (order_id,),
        ).fetchone()
        if row is None:
            return

        # Network I/O runs with no transaction open, so no row locks are held.
        receipt = payments.charge(order_id, row[0])

        # Short explicit transaction: commits on normal exit and rolls back
        # if any exception is raised, so error paths cannot leave it open.
        with conn.transaction():
            conn.execute(
                "UPDATE orders SET status = 'paid', payment_ref = %s "
                "WHERE id = %s AND status = 'pending'",
                (receipt.id, order_id),
            )

A psycopg 3 handler that makes the payment API call outside any transaction and then commits the UPDATE in a short transaction block that rolls back on error.

pg_cancel_backend vs pg_terminate_backend: which should you use?

Both functions take a process ID and send a signal to that backend. They behave very differently when a session is holding locks.

pg_cancel_backend interrupts whatever statement the backend is currently executing. A session that is idle in transaction is not executing anything, so there is nothing to cancel. Its transaction stays open and its locks stay held. If a cancelled query was inside an explicit transaction block, that transaction moves to an aborted state. It keeps its locks until the client issues ROLLBACK.

pg_terminate_backend ends the whole backend process. PostgreSQL rolls back the open transaction, and every lock it held is released.

pg_cancel_backend pg_terminate_backend
Running query Cancelled Cancelled
Session Stays connected Disconnected
Transaction Remains open (aborted if in a block) Rolled back
Locks held Kept until the client rolls back or commits Released
Appropriate when A long-running statement is the blocker and the client can recover The blocker is idle in transaction, or cancelling did not clear the wait

Both functions return true when the signal is sent, not when it takes effect. Recheck pg_blocking_pids afterward to confirm the wait has cleared.

Terminating a session ends the incident quickly, but it has a cost. Any uncommitted work in that transaction is discarded. The client also sees a dropped connection, which it may retry. Terminating also removes the evidence you need to fix the underlying bug.

Before you terminate a session, save these fields from its row in pg_stat_activity:

  • query, the last statement the session ran, which often points to the code path that left the transaction open
  • client_addr, the client's network address
  • application_name, which identifies the service, if the client sets it
  • xact_start, which shows how long the transaction has been open
-- 1. List each waiting session alongside the session blocking it.
SELECT w.pid AS waiting_pid, b.pid AS blocker_pid, b.state,
       b.xact_start, b.application_name, b.client_addr, b.query
FROM pg_stat_activity w
CROSS JOIN LATERAL unnest(pg_blocking_pids(w.pid)) AS bp(pid)
JOIN pg_stat_activity b ON b.pid = bp.pid;

-- 2. Save the blocker's details before acting. Terminating the session destroys this evidence.
CREATE TABLE IF NOT EXISTS lock_incident_log (
  captured_at      timestamptz DEFAULT now(),
  pid              int,
  usename          name,
  state            text,
  query            text,
  client_addr      inet,
  application_name text,
  xact_start       timestamptz,
  state_change     timestamptz
);
INSERT INTO lock_incident_log (pid, usename, state, query, client_addr,
                               application_name, xact_start, state_change)
SELECT pid, usename, state, query, client_addr,
       application_name, xact_start, state_change
FROM pg_stat_activity
WHERE pid = 12345;  -- placeholder: blocker_pid from step 1

-- 3a. The blocker is actively running a statement: try a cancel first.
--     If the statement ran inside a transaction block, the locks stay held until the client rolls back.
SELECT pg_cancel_backend(12345);

-- 3b. The blocker is 'idle in transaction', or the cancel did not clear the wait:
--     terminate the session. Its transaction is rolled back and its locks are released.
SELECT pg_terminate_backend(12345);

-- 4. Both calls return true once the signal is sent, not once it takes effect.
--    Run this check until it returns no rows.
SELECT pid, pg_blocking_pids(pid) AS blocked_by, wait_event_type, query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

Finds the blocking session and records its query, client_addr, application_name and xact_start, then cancels or terminates it and checks that the lock wait has cleared.

When should you use lock_timeout, statement_timeout, and idle_in_transaction_session_timeout?

Each timeout guards a different failure, so set them narrowly rather than reaching for one global value in postgresql.conf.

Setting What it limits Typical scope Failure mode
lock_timeout Each single wait to acquire a lock SET LOCAL inside a migration transaction Statement errors out; the transaction is aborted
statement_timeout Total runtime of one statement, including lock waits ALTER ROLE app_api SET ... for latency-sensitive services Statement canceled; long jobs fail midway
idle_in_transaction_session_timeout Time a session sits idle with a transaction open Per role for application users The whole session is terminated, not just the transaction

SET lasts for the session. SET LOCAL reverts at commit or rollback, which makes it the safe choice for DDL. ALTER ROLE ... SET applies to new sessions for that role, so the web API can have tight limits while the reporting role does not.

When to avoid each

A global statement_timeout breaks reports, backfills, VACUUM-adjacent maintenance and index builds. The error looks like a cancellation, so people often mistake it for an operator killing their query.

idle_in_transaction_session_timeout kills the connection server-side. A pooler or ORM may not notice until it next hands out that connection, so the error appears in an unrelated request.

lock_timeout on hot application queries turns brief, harmless waits into errors.

Every timeout moves cost into application code. You need retry with backoff, idempotent writes, and logging that separates timeout errors from real faults. Budget for that work before you turn timeouts on.

How do you run schema migrations without causing a lock outage?

The core problem is the lock queue. An ALTER TABLE waiting for ACCESS EXCLUSIVE behind one slow reader also blocks every query that arrives after it. The fix is to make the DDL give up quickly instead of waiting.

A retryable DDL pattern

Open a transaction and run SET LOCAL lock_timeout = '2s' before the DDL statement. SET LOCAL limits the setting to that transaction, so it does not affect the rest of the session or pooled connections. If the lock is not granted in time, the statement fails, the transaction rolls back, and the queued traffic behind it resumes. Wrap the transaction in a retry loop in your migration runner. Use exponential backoff with jitter and a capped attempt count. Exhausting the attempts should fail the deploy, not page someone.

Keep the DDL transaction to the schema change itself. Run large backfills as separate batched updates, each committing on its own, so no long transaction holds locks.

Concurrent variants and windows

CREATE INDEX CONCURRENTLY avoids blocking writes. The trade-offs:

  • It is slower than a plain index build.
  • It cannot run inside a transaction block.
  • A failed build leaves an INVALID index that you must drop before retrying.

ADD CONSTRAINT ... NOT VALID followed by VALIDATE CONSTRAINT splits constraint checks the same way.

If a table has continuous long-running reads, the DDL may never get its lock. Your options are a maintenance window or cancelling those readers.

The payoff is operational. A lock timeout becomes a retryable migration failure instead of an outage that needs incident response.

How can you catch intermittent lock waits and deadlocks after the fact?

Live queries against pg_locks only show contention that is happening at that moment. A lock wait that lasts four seconds at 03:00 is gone before anyone can query for it, so you need the server to record waits as they happen.

The cheapest way to do that is two settings in postgresql.conf, applied with a configuration reload:

  • log_lock_waits = on
  • deadlock_timeout = 1s

deadlock_timeout controls how long a backend waits on a lock before it checks for a deadlock. With log_lock_waits enabled, that same check also writes a log line saying the process is still waiting. The line includes the lock mode, the object, and the PIDs holding the lock. When the wait ends, a second line records how long it took. This gives you an after-the-fact record of who blocked whom, at almost no cost when there is no contention.

Deadlocks behave differently from ordinary blocking. When the check finds a cycle, PostgreSQL cancels one participant with a "deadlock detected" error, and the others proceed. An ordinary blocking chain has no cycle to find, so it waits indefinitely unless lock_timeout or statement_timeout applies.

Lowering deadlock_timeout catches shorter waits, but the deadlock check then runs sooner and more often. Under heavy contention that adds CPU load and increases log volume.

An alternative is to sample pg_stat_activity on a schedule and store the rows where wait_event_type = 'Lock'. This shows contention trends over time, but waits shorter than the sampling interval can still fall between samples and go unrecorded.

How can this save engineering time?

Most of the time lost to lock contention goes into misdiagnosis rather than fixing the problem. A blocked query looks like a slow query, so teams spend time on the wrong work: reading query plans, adding indexes, scaling up instances, or restarting application pods. None of that helps, because the waiting session is the victim. The techniques above cut that time in five specific places:

  • Triage. Checking wait_event_type = 'Lock' separates lock waits from CPU, I/O and LWLock pressure in one query. That rules out query tuning and capacity work at the start of an incident.
  • Root cause. pg_blocking_pids and the root-blocker query replace manual inspection of every active session with a single PID. Saving its application_name, client_addr, xact_start and last query before acting gives the owning team enough to find the code path without reproducing the incident.
  • Correct remediation on the first try. Knowing that pg_cancel_backend does not release locks held by an idle-in-transaction session avoids the cycle of cancelling, seeing no change, and escalating.
  • Prevention. Short transactions with no network I/O inside them, per-role idle_in_transaction_session_timeout, and SET LOCAL lock_timeout with retries in migrations turn a recurring class of incidents into either non-events or routine retryable failures. A failed deploy that retries itself costs far less attention than a page.
  • Intermittent problems. log_lock_waits produces a record of blockers for waits nobody saw live. Without it, the usual alternative is waiting for the problem to recur while someone is watching.

To size the benefit for your own team, here is an illustrative estimate, not a measured result. All inputs are assumed:

  • Assumed frequency: 2 lock-related incidents per month.
  • Assumed cost without these tools: 3 engineers for 90 minutes each per incident, mostly spent ruling out other causes. That is 4.5 engineer-hours per incident, or 9 engineer-hours per month.
  • Assumed cost with a runbook built on the queries above: 1 engineer for 20 minutes to find and clear the root blocker, plus 1 hour for the owning team to fix the code path using the saved session details. That is about 1.3 engineer-hours per incident, or about 2.7 engineer-hours per month.
  • Assumed prevention effect: migration-caused incidents become automatic retries instead of pages, removing some incidents entirely. This is not counted above.

Under these assumed inputs, the difference is roughly 6 engineer-hours per month before any prevention effect. Your numbers will differ. Replace each input with figures from your own incident history before using an estimate like this to justify the work. Remember the costs on the other side too. Adding retry logic, idempotent writes and timeout-aware logging takes engineering time up front, as the timeout section notes.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to postgresql lock contention, this is where it helps:

Saves time

  • Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
  • One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
  • Changing a shared variable is one edit and one review instead of editing every service's .env by hand.

Saves cost

  • Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
  • Less duplicated configuration to maintain: one shared definition inherited by many services.
  • A wrong or stale value is caught at review time by the services it affects, not by a failed deploy.
  • OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.