PostgreSQL Lock Waits: How to Find the Blocking Query and Fix It

An ALTER TABLE that should take milliseconds hangs, and within a minute every query on that table is stuck behind it. Or requests time out while PostgreSQL CPU stays low. In both cases pg_stat_activity shows sessions with wait_event_type = 'Lock', and the logs may show canceling statement due to lock timeout. The cause is almost never the query that is waiting. It is a transaction somewhere else that holds a lock and has not finished. This guide shows how to find that transaction on PostgreSQL 17 and 18, what to do with it, and which settings stop the incident recurring.

Scope and review date: November 2026. This post covers PostgreSQL 17 and 18 (current minor releases 17.11 and 18.6), with notes on PostgreSQL 19, which is at release candidate 1 as of November 2026. Queries, defaults, and log formats were checked against the PostgreSQL documentation and source, not run in a lab; all output shown is illustrative.

Why is my PostgreSQL query waiting on a lock?

A lock wait happens when a statement needs a lock that conflicts with one held by another transaction. The key rule from the PostgreSQL explicit locking chapter is that a lock, once acquired, is normally held until the end of the transaction. A statement that finished long ago can still be holding its locks if its transaction is open.

Blocking ends when the holder commits or rolls back. A deadlock is a cycle, and PostgreSQL resolves it by aborting one transaction with ERROR: deadlock detected (SQLSTATE 40P01). Outside a deadlock, the documentation says a waiting transaction waits indefinitely for conflicting locks to be released. Nothing ends a plain lock wait unless you configure a timeout.

SELECT wait_event_type, wait_event, state, count(*)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY 1, 2, 3
ORDER BY 4 DESC;

Interpretation: rows with wait_event_type = 'Lock' are heavyweight lock waits. The wait_event names what is awaited: relation for a table or index, transactionid for another transaction to finish (the usual row-lock case), tuple for a specific row, and advisory for an advisory lock. Many idle in transaction sessions next to lock waits is the classic pattern. The event names are listed in the monitoring statistics documentation.

How do I find the sessions waiting for locks?

Run this from an autocommit session on the affected database:

SELECT
    pid,
    usename,
    application_name,
    state,
    wait_event,
    clock_timestamp() - query_start AS query_age,
    clock_timestamp() - xact_start AS transaction_age,
    left(query, 200) AS query_text
FROM pg_stat_activity
WHERE datname = current_database()
  AND wait_event_type = 'Lock'
  AND pid <> pg_backend_pid()
ORDER BY query_start;

Interpretation: a waiting session shows state = 'active' because it is executing a statement, so read state and wait_event together. query_age is time since the statement started, not time spent on the current lock; a statement can run for minutes and then start waiting. Without pg_read_all_stats, other users' query text shows as null. Redact query text before sharing it.

For lock work, two states matter: active with a Lock wait (a victim) and idle in transaction (a likely culprit). The connection states guide covers the rest.

How do I find the blocking query with pg_blocking_pids()?

pg_blocking_pids(pid) returns the process IDs blocking a given session. The system information functions documentation defines two kinds of blocker: a hard block, where another session holds a conflicting lock, and a soft block, where another session is waiting for a conflicting lock and is ahead in the queue. Self-joins on pg_locks miss the second kind, so use the function.

This recursive query calls it for every waiting session, starts from the root blockers (sessions that block others but wait on nothing), and indents each waiter under its blocker:

WITH RECURSIVE waits AS (
    SELECT DISTINCT pid, unnest(pg_blocking_pids(pid)) AS blocker
    FROM pg_stat_activity
    WHERE wait_event_type = 'Lock'
),
roots AS (
    SELECT DISTINCT blocker AS pid
    FROM waits
    WHERE blocker NOT IN (SELECT pid FROM waits)
),
tree AS (
    SELECT pid, ARRAY[pid] AS path FROM roots
    UNION ALL
    SELECT w.pid, t.path || w.pid
    FROM tree AS t
    JOIN waits AS w ON w.blocker = t.pid
    WHERE w.pid <> ALL (t.path)
)
SELECT repeat('  ', cardinality(t.path) - 1) || t.pid AS pid_tree,
       a.state,
       date_trunc('second', clock_timestamp() - a.xact_start) AS xact_age,
       left(a.query, 30) AS query
FROM tree AS t
LEFT JOIN pg_stat_activity AS a ON a.pid = t.pid
ORDER BY t.path;

Interpretation: the unindented rows are the sessions to act on; everything indented below them clears once they finish. For an idle root, query is the last statement it ran, not something it is running now. A root with a null state is PID 0, which pg_blocking_pids() uses for a prepared transaction: find it in pg_prepared_xacts. A pure deadlock cycle has no root and returns no rows; PostgreSQL breaks it after deadlock_timeout.

What pg_locks adds

pg_locks adds which lock is requested and how long the current wait has lasted:

SELECT
    l.pid,
    l.locktype,
    l.mode AS requested_mode,
    l.relation::regclass AS relation_name,
    l.transactionid,
    clock_timestamp() - l.waitstart AS current_lock_wait
FROM pg_locks AS l
JOIN pg_stat_activity AS a ON a.pid = l.pid
WHERE NOT l.granted
  AND a.datname = current_database()
ORDER BY l.waitstart NULLS LAST;

Interpretation: a row conflict appears as a wait on transactionid with no relation. The pg_locks documentation explains why: row locks are stored on disk in the rows themselves, so a session waiting for a row lock usually shows up as waiting for the permanent transaction ID of the row's current holder. Querying pg_locks or calling pg_blocking_pids() briefly locks the lock manager's shared state, so do not poll them every second from a dashboard.

Which PostgreSQL lock modes conflict with each other?

PostgreSQL has eight table-level lock modes, and every statement takes one automatically. The conflict table decides who waits; the card below shows it for common statements.

The documentation's tip to remember: only an ACCESS EXCLUSIVE lock blocks a plain SELECT. Row-level locks never block readers, only writers and lockers of the same rows.

Why does a waiting ALTER TABLE block every SELECT behind it?

Lock requests on a table form a queue. A new request that conflicts with one already waiting queues behind it, even if it conflicts with no held lock: the soft block pg_blocking_pids() reports. So a long transaction that holds a harmless ACCESS SHARE lock can stop all traffic on a table: the ALTER TABLE waits for that transaction, and every new SELECT, INSERT, and UPDATE waits for the ALTER TABLE.

To watch the queue form, use three connections to a disposable database:

-- Setup, once
CREATE TABLE lock_wait_demo (id integer PRIMARY KEY, quantity integer NOT NULL);
INSERT INTO lock_wait_demo VALUES (1, 10);

-- Connection A: leave the transaction open
BEGIN;
SELECT quantity FROM lock_wait_demo WHERE id = 1;

-- Connection B: waits for A
ALTER TABLE lock_wait_demo ADD COLUMN note text;

-- Connection C: waits for B
SELECT quantity FROM lock_wait_demo WHERE id = 1;

From a fourth connection, list the locks on the table in queue order:

SELECT l.pid, l.mode, l.granted, a.state, left(a.query, 40) AS query
FROM pg_locks AS l
JOIN pg_stat_activity AS a ON a.pid = l.pid
WHERE l.relation = 'lock_wait_demo'::regclass
ORDER BY l.granted DESC, l.waitstart;

Interpretation: expect A with a granted AccessShareLock, B with an ungranted AccessExclusiveLock, and C with an ungranted AccessShareLock. pg_blocking_pids() for C returns B, not A. Run ROLLBACK; in A and both complete; then drop the table. With an UPDATE of the same row in A and B instead, B waits on transactionid.

Should I use pg_cancel_backend or pg_terminate_backend?

The server signaling functions do different things. pg_cancel_backend(pid) cancels the session's current query and leaves the connection open. pg_terminate_backend(pid, timeout) ends the session, and its open transaction is rolled back. An idle in transaction session has no current query, so cancelling it releases nothing.

-- Cancel the waiting ALTER TABLE (PID from the blocking tree)
SELECT pg_cancel_backend(4388);

-- Terminate the idle root blocker and wait up to 5 seconds for it to exit
SELECT pg_terminate_backend(4120, 5000);

Interpretation: both return true when the signal was sent, which does not prove the problem is gone. With a timeout above zero, pg_terminate_backend() waits for the process to exit and returns false with a warning if it does not. Non-superusers need membership in the target's role or pg_signal_backend, and cannot signal superuser sessions. A prepared transaction (PID 0) cannot be signalled at all; its coordinator must run COMMIT PREPARED or ROLLBACK PREPARED.

Which timeouts stop lock waits from piling up?

All four default to 0 (off). Descriptions are in the client connection defaults; error strings are from the PostgreSQL 18 source.

lock_timeout applies to each lock acquisition separately. If statement_timeout is the same or lower, it always fires first. The documentation advises against setting statement_timeout, lock_timeout, or transaction_timeout in postgresql.conf, because they would hit every session. Set them per role or per transaction:

-- Application role: never hold locks while idle for more than 5 minutes
ALTER ROLE app_rw SET idle_in_transaction_session_timeout = '5min';

-- Migration role: give up on a lock after 3 seconds
ALTER ROLE migrator SET lock_timeout = '3s';

-- One transaction only
BEGIN;
SET LOCAL lock_timeout = '3s';
SET LOCAL statement_timeout = '60s';
-- reviewed migration statement here
COMMIT;

Interpretation: role settings apply when a session starts, so pooled connections keep the old values until they reconnect. The values are examples; derive yours from real transaction and migration durations. An idle-in-transaction timeout closes the connection, so test that your pool discards it cleanly.

How do I log lock waits and deadlocks?

Most lock incidents end before anyone runs a query. log_lock_waits writes a log line when a session has waited longer than deadlock_timeout (default 1s) for a lock, and a second line when it gets the lock. It is off by default in PostgreSQL 17 and 18. The PostgreSQL 19 release notes list "Enable server variable log_lock_waits by default", and the PostgreSQL 19 logging documentation gives the default as on. PostgreSQL 19 is still a release candidate as of November 2026.

ALTER SYSTEM SET log_lock_waits = on;
SELECT pg_reload_conf();
SHOW log_lock_waits;
SHOW deadlock_timeout;

Interpretation: a reload is enough. deadlock_timeout is also the delay before the deadlock check, and the lock management documentation calls 1s about the smallest sensible value. It never cancels a waiting statement.

Illustrative log lines, in the PostgreSQL 18 source format:

LOG:  process 4388 still waiting for AccessExclusiveLock on relation 16402 of database 16384 after 1000.112 ms
DETAIL:  Process holding the lock: 4120. Wait queue: 4388, 4401, 4417.
STATEMENT:  ALTER TABLE orders ADD COLUMN note text;

Interpretation: the DETAIL line names the holder and the whole queue, which is the blocking tree reconstructed after the fact. PostgreSQL 18 also adds log_lock_failures (default off), which logs the holder when a SELECT ... NOWAIT fails to get a lock. pgBadger can summarize these lines.

PostgreSQL 19 also adds a pg_stat_lock view with per-lock-type statistics and raises the default max_locks_per_transaction from 64 to 128; its release notes say existing settings must be doubled to keep the same capacity.

How do I run DDL without causing a lock queue?

Two habits remove most DDL incidents: never let a schema change wait long (lock_timeout plus retries), and prefer forms that take weaker locks or skip scans, as the ALTER TABLE reference documents per subcommand.

#!/usr/bin/env bash
# Retry a short DDL statement that must not queue for long
for attempt in 1 2 3 4 5; do
  if psql "$DATABASE_URL" -v ON_ERROR_STOP=1 \
       -c "SET lock_timeout = '3s'" \
       -c "ALTER TABLE orders ADD COLUMN note text"; then
    exit 0
  fi
  sleep $((attempt * 10))
done
echo "gave up: table stayed busy" >&2
exit 1

Interpretation: each attempt queues other traffic behind it for at most about 3 seconds of waiting. Repeated -c options run in one session, so the SET applies to the ALTER.

-- Build an index without blocking writes (SHARE UPDATE EXCLUSIVE; not in a transaction block)
CREATE INDEX CONCURRENTLY orders_customer_id_idx ON orders (customer_id);

-- Add a foreign key without the long validation scan under a strong lock
ALTER TABLE orders
    ADD CONSTRAINT orders_customer_fk FOREIGN KEY (customer_id)
    REFERENCES customers (id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk;  -- SHARE UPDATE EXCLUSIVE

-- A constant default needs no table rewrite; a volatile one rewrites the table
ALTER TABLE orders ADD COLUMN source text DEFAULT 'web';

Interpretation: CREATE INDEX CONCURRENTLY still waits for existing transactions that could use the index, so a long transaction delays it, usually as a virtualxid wait. ADD FOREIGN KEY takes SHARE ROW EXCLUSIVE on both tables, and VALIDATE CONSTRAINT takes only SHARE UPDATE EXCLUSIVE, so reads and writes continue during the scan. A volatile default such as clock_timestamp(), a stored generated column, or an identity column rewrites the whole table under ACCESS EXCLUSIVE. The safe DDL guide covers each pattern in depth.

Frequently asked questions

Key takeaways

Resolve recurring PostgreSQL lock contention with JusDB

JusDB's PostgreSQL performance tuning team can review your lock waits, transaction patterns, and timeout settings, trace recurring blockers to the code paths that leave transactions open, and help plan schema changes with lock timeouts and retries.

Bring your PostgreSQL version, lock wait log lines or blocking tree captures from recent incidents, and your migration tooling. Your team approves every production change.

Related reading

• PostgreSQL wait events: understanding what slows your database
• Zero-downtime schema changes in PostgreSQL: safe DDL without locking
• PostgreSQL advisory locks: patterns and pitfalls
• PostgreSQL documentation: Explicit Locking