How to diagnose Postgres lock issues with pg_stat_activity and pg_locks

PostgreSQL rarely alerts teams to lock contention through errors—instead, applications slow down, and pg_stat_activity shows wait_event_type = 'Lock'. When an incident strikes, teams need answers fast: which processes are waiting, what locks they’re blocked on, and which processes are holding those locks. Alone, neither pg_stat_activity nor pg_locks gives the full picture. Only by joining these views—and layering in pg_blocking_pids()—can you build a complete blocking tree and resolve issues in minutes instead of half an hour.

How PostgreSQL locking works under the hood

PostgreSQL’s lock manager runs in shared memory, tracking every lock request—whether granted or still pending. The pg_locks view exposes this data directly. Key columns include:

• locktype – the type of lock (relation, transactionid, tuple, advisory, etc.)
• relation – the object ID (joinable with pg_class for table names)
• mode – the lock strength (e.g., AccessShareLock, RowExclusiveLock, AccessExclusiveLock)
• granted – whether the lock has been issued
• pid – the process ID holding or waiting for the lock
• waitstart – timestamp when the lock began waiting (available in PostgreSQL 14+)

Meanwhile, pg_stat_activity reveals runtime status: what query each process is running, how long its transaction has persisted, and whether it’s actively waiting or stuck idle. These views complement each other—pg_locks shows what is locked, while pg_stat_activity shows who is involved and why.

The function pg_blocking_pids(pid) simplifies root-cause analysis by returning an array of PIDs blocking the specified process. It automatically resolves lock conflicts across different lock types—critical for avoiding manual errors with transactionid, advisory, or tuple-level locks.

Building a live blocking tree with a single query

Combining these sources into a coherent blocking tree requires joining pg_stat_activity with itself via pg_blocking_pids(). A proven approach, adapted from the PostgreSQL Wiki’s lock monitoring guide, looks like this:

SELECT
    blocked.pid AS blocked_pid,
    blocked.usename AS blocked_user,
    blocked.application_name AS blocked_app,
    blocked.wait_event_type,
    blocked.wait_event,
    now() - blocked.query_start AS blocked_for,
    left(blocked.query, 200) AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.usename AS blocking_user,
    blocking.application_name AS blocking_app,
    blocking.state AS blocking_state,
    now() - blocking.xact_start AS blocking_xact_age,
    left(blocking.query, 200) AS blocking_query
FROM pg_stat_activity AS blocked
JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS bpid ON true
JOIN pg_stat_activity AS blocking ON blocking.pid = bpid
WHERE blocked.wait_event_type = 'Lock'
ORDER BY blocked_for DESC;

Each row represents a live edge in the wait-for graph. Sorting by blocked_for surfaces the longest-waiting processes—typically the root cause. A well-designed dashboard displaying this query can cut incident resolution from half an hour to under three minutes.

Common production pitfalls and how to fix them

Problem: Idle transactions holding locks indefinitely

A backend starts a transaction with BEGIN; UPDATE ...; but never commits, leaving the connection idle in the connection pool. Row-level and transaction ID locks remain held, freezing any subsequent UPDATE or DELETE on the row. Symptoms include specific endpoints timing out while others function normally. Without lock monitoring, this scenario is hard to diagnose because the process shows state = 'idle in transaction', leaving no trace in query logs.

A typical anti-pattern in application code:

# Unsafe: inline external API call inside transaction
def update_order(order_id, payload):
    conn = pool.getconn()
    try:
        conn.execute("BEGIN")
        conn.execute("UPDATE orders SET status='processing' WHERE id=%s", (order_id,))
        # Row-level lock acquired here
        resp = http_client.post(
            "
            json=payload,
            timeout=120
        )
        conn.execute("UPDATE orders SET status=%s WHERE id=%s", (resp.status, order_id))
        conn.execute("COMMIT")
    finally:
        pool.putconn(conn)

When the external call stalls due to DNS delays or upstream rate limiting, the transaction remains open, locking the row. The fix: separate network I/O from the transaction. Commit an intermediate state (e.g., 'pending') before making the external call, then reopen the transaction afterward. Additionally, set guardrails at the database level:

ALTER ROLE app_user SET idle_in_transaction_session_timeout = '30s';
ALTER ROLE app_user SET statement_timeout = '10s';
ALTER ROLE app_user SET lock_timeout = '3s';

Problem: DDL blocking all traffic during peak hours

PostgreSQL’s lock queue follows a first-in-first-out model. When an ALTER TABLE ... ADD COLUMN ... NOT NULL DEFAULT ... request for an AccessExclusiveLock is queued behind a long-running SELECT, every subsequent query targeting the same table also gets stuck—even before the DDL lock is granted. The result: all endpoints interacting with the table freeze simultaneously, mimicking a database outage.

Avoid running heavy migrations during peak traffic. Use LOCK_TIMEOUT to prevent DDL from hanging indefinitely:

-- Run migrations with timeout during off-peak hours
SET LOCAL lock_timeout = '30s';
ALTER TABLE orders ADD COLUMN tax_code VARCHAR(10) NOT NULL DEFAULT '0000';

For large tables, consider using CONCURRENTLY with smaller batches, or schedule migrations during maintenance windows.

Building a proactive lock monitoring strategy

Lock contention rarely announces itself until it’s too late. Teams that embed blocking tree queries into their incident response playbooks can diagnose root causes in seconds rather than minutes. Pair this with proactive measures—setting timeouts, avoiding long-running transactions, and scheduling schema changes carefully—to keep PostgreSQL running smoothly even under pressure.

With the right observability in place, the next production freeze might resolve itself before users even notice.