What does pg_locks actually show about lock contention?
pg_locks is a live view of the lock manager. Each row is one lock that a backend process holds or is waiting for. The pid column identifies the session. The mode column names the lock mode. The granted column is the one to check first: true means the session holds the lock, and false means it is waiting in a queue. Every blocked query shows up as at least one row with granted = false.
The locktype column tells you what kind of object is locked:
| locktype | What is locked | What a wait usually means |
|---|---|---|
relation |
A table, index, sequence or view | DDL or an explicit LOCK TABLE conflicts with ordinary queries |
transactionid |
Another transaction's ID | A row-level conflict: you must wait for that transaction to commit or roll back |
tuple |
A specific row version | Several sessions are queued for the same row |
advisory |
An application-chosen key | The application's own locking logic is serializing work |
The transactionid waits often confuse people. Postgres does not record row locks in shared memory. It writes them into the row's header on disk. A session that needs a locked row cannot wait on the row directly. Instead, it waits for the holder's transaction to end. This keeps row locking cheap at any scale. The trade-off is that pg_locks rarely tells you which row is contested, only which transaction is holding it.
At the table level, three modes cover most incidents:
SELECTtakesACCESS SHARE.INSERT,UPDATEandDELETEtakeROW EXCLUSIVE.- Most
ALTER TABLEforms andDROPtakeACCESS EXCLUSIVE, which conflicts with every other mode.
For any other pair of modes, check the lock conflict table in the Postgres documentation rather than relying on memory.
Who should use pg_locks to diagnose blocked queries?
Anyone who has to explain why a Postgres query is stuck should know how to read pg_locks. In practice that means:
- On-call engineers and SREs who get paged for latency spikes, exhausted connection pools or timeouts, where the database looks idle on CPU but requests are not completing.
- Application and backend engineers who write transactions, ship schema migrations or own code that holds a transaction open across slow work.
- Platform and tooling teams who build migration runners, job queues or deploy pipelines and need them to fail safely when they cannot get a lock.
- DBAs who review migrations and tune settings such as
lock_timeout,deadlock_timeoutandidle_in_transaction_session_timeout.
You do not need superuser to query pg_locks. You do need enough privilege to see other sessions' details in pg_stat_activity. Without superuser or membership in pg_read_all_stats, the query column of sessions owned by other roles is hidden, and you see the waiting pids but not what they are running. To cancel or terminate other sessions you need the privileges described in the section on pg_cancel_backend and pg_terminate_backend.
The techniques here are less useful if your problem is not lock waits. A query that is slow because of a bad plan, I/O or CPU shows a different wait_event_type, or none at all, and pg_locks will not explain it.
How does Postgres lock management work?
Every statement acquires locks before it touches data. A SELECT asks for ACCESS SHARE on each table it reads. An UPDATE asks for ROW EXCLUSIVE on the table and then marks the rows it changes. The lock manager compares each request against the locks other sessions hold. If the modes are compatible, the lock is granted right away. If they conflict, the requesting backend sleeps in a wait queue for that object until the conflicting locks are released.
Three properties of this design explain most of what you see in pg_locks:
- Locks last until the transaction ends. Table and row locks taken by a statement are not released when the statement finishes. They are released at
COMMITorROLLBACK. A transaction that ran one quickUPDATEand then stayed open still holds that lock. Session-level advisory locks are the exception: they last until explicitly released or until the session ends. - Table-level locks live in shared memory, and row locks do not. Table, index and advisory locks are entries in a shared lock table, which is what
pg_locksreports. Row locks are written into the tuple header. A waiter on a row therefore shows up as a wait on the holder'stransactionid, as described above. - Waits are queued. Requests for the same object wait in order. A new request must be compatible with both the granted locks and the requests already queued ahead of it. This is why one waiting DDL statement can block readers that are compatible with every lock currently held, as the migration section below shows.
pg_locks exposes this state by joining the lock manager's entries with information about each backend. The fastpath column marks weak table locks that a backend recorded in its own fast-path slots instead of the main shared table, which Postgres does to reduce contention on the lock manager for common operations. If you run serializable transactions, the view also includes predicate locks with mode SIReadLock. These never block anyone. They are used to detect serialization conflicts, so ignore them when looking for blocked queries.
Reading the view is not free. To return a consistent picture, Postgres briefly locks the lock manager's internal structures. Querying pg_locks or calling pg_blocking_pids() occasionally is harmless. Polling them at high frequency on a busy server adds load to the component you are trying to observe.
How do you find which session is blocking a query?
The fastest way to answer that is to ask Postgres directly instead of reading raw pg_locks rows by hand. pg_blocking_pids(pid) returns an array of the process IDs that are preventing a given backend from acquiring its lock. Join pg_stat_activity to itself on that array, filtering to sessions where wait_event_type = 'Lock'. You get one row per waiting session, with its blocker's pid, state, query, xact_start and wait_event beside it.
Follow the chain to the root
Blockers are often waiting themselves. A migration queued for ACCESS EXCLUSIVE blocks every later reader, and pg_blocking_pids() reports it because it reports processes ahead in the lock queue, not only those holding the lock. Keep following blocker pids until you reach a session with an empty blocker array. That session is the root blocker. It is frequently idle in transaction, and an old xact_start shows how long it has held its locks. Cancelling sessions in the middle of the chain only moves the queue forward by one place. The root is the session to fix.
Trade-offs
These views are point-in-time snapshots. The chain can change between two runs of the query, or while you are reading the output, so re-check before you act. pg_blocking_pids() briefly locks the shared lock manager. Calling it in a tight monitoring loop on a busy server adds the contention you are trying to measure. Run it when you are diagnosing a problem, not every second.
One query that names the root blocker and shows its transaction age replaces guesswork and killing sessions at random. Random kills can roll back work that was unrelated to the problem and still leave the real blocker running.
-- 1) Each waiting session next to the session(s) blocking it.
-- A diagnostic snapshot: run it by hand, not in a tight loop.
SELECT w.pid AS waiting_pid,
w.wait_event AS waiting_on,
left(w.query, 60) AS waiting_query,
b.pid AS blocker_pid,
b.state AS blocker_state,
b.wait_event_type AS blocker_wait_type, -- 'Lock' = blocker is queued too
now() - b.xact_start AS blocker_xact_age,
left(b.query, 60) AS blocker_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
WHERE w.wait_event_type = 'Lock'
ORDER BY blocker_xact_age DESC NULLS LAST;
-- 2) Root blockers: they block someone but are not waiting on a lock themselves.
-- pg_blocking_pids() is called once per waiting session, not for every backend.
WITH waits AS (
SELECT pid, pg_blocking_pids(pid) AS blockers
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
),
blockers AS (
-- DISTINCT: parallel queries can repeat a leader pid in the array
SELECT DISTINCT unnest(blockers) AS pid FROM waits
)
SELECT a.pid, a.state, a.usename, a.application_name,
a.xact_start, now() - a.xact_start AS xact_age,
left(a.query, 80) AS last_query
FROM blockers bl
JOIN pg_stat_activity a ON a.pid = bl.pid
WHERE a.pid NOT IN (SELECT pid FROM waits WHERE cardinality(blockers) > 0)
ORDER BY xact_age DESC NULLS LAST;
-- Re-run query 2 right before acting: the chain may have changed.
-- SELECT pg_cancel_backend(12345); -- try cancelling the root's query first
-- SELECT pg_terminate_backend(12345); -- needed for 'idle in transaction' roots
The first query lists each lock-waiting session beside its blocker's pid, state, wait type, transaction age and query. The second query keeps only root blockers, the sessions that block others without waiting on a lock themselves, and calls pg_blocking_pids() once per waiting session.
Why can one short migration stall an entire table?
Postgres grants locks in queue order, not just by compatibility. A new request has to be compatible with the locks already held and with any requests already waiting ahead of it. This fairness rule keeps DDL from being starved forever, but it also lets one waiting request block every request that arrives after it.
Most forms of ALTER TABLE need an ACCESS EXCLUSIVE lock, which conflicts with every other mode, including the ACCESS SHARE lock a plain SELECT takes. Suppose a transaction read the table and is still open. It may be a slow report, or a session sitting idle in transaction. That session keeps its ACCESS SHARE lock until it commits or rolls back. The migration queues behind it, and new readers then queue behind the migration:
orders table lock queue
GRANTED pid 4120 ACCESS SHARE (txn open 47 min, idle in transaction)
------------------------------------------------------------------------
WAITING pid 5301 ACCESS EXCLUSIVE ALTER TABLE orders ADD COLUMN ...
WAITING pid 5302 ACCESS SHARE SELECT ... FROM orders
WAITING pid 5303 ACCESS SHARE SELECT ... FROM orders
WAITING pid 5304 ROW EXCLUSIVE UPDATE orders ...
WAITING ... (every new query on orders)
The ALTER itself might finish in milliseconds once it runs. The table still freezes for as long as pid 4120 stays open. Application connection pools fill with stuck queries, and the outage spreads to endpoints that only read orders.
This is why lock diagnosis is most urgent during deploys and schema changes. In pg_locks, the symptom is many ungranted rows on one relation. The fix is usually to end the old transaction or cancel the waiting DDL, not to kill the readers.
orders table: lock queue (oldest request first)
+------------------------------------------+
| GRANTED pid 4120 ACCESS SHARE | <-- fix: COMMIT/ROLLBACK
| idle in transaction, open 47 min | or terminate this txn
+------------------------------------------+
^
| conflicts: ACCESS EXCLUSIVE vs ACCESS SHARE
|
+------------------------------------------+
| WAITING pid 5301 ACCESS EXCLUSIVE | <-- or: cancel the DDL
| ALTER TABLE orders ADD COLUMN ... |
| needs ms to run, cannot start |
+------------------------------------------+
^
| queued behind the waiting DDL, even when
| compatible with the granted ACCESS SHARE
+------------------------------------------+
| WAITING pid 5302 ACCESS SHARE SELECT |
| WAITING pid 5303 ACCESS SHARE SELECT |
| WAITING pid 5304 ROW EXCLUSIVE UPDATE |
| WAITING ... every new query on orders |
+------------------------------------------+
|
v
app pools fill with stuck queries -> read-only endpoints stall too
An idle-in-transaction session holds ACCESS SHARE on orders, so the ALTER TABLE waiting for ACCESS EXCLUSIVE sits in the queue, and every later query lines up behind it even when its lock mode is compatible with the granted holder.
What usually causes blocked queries in Postgres?
The same few patterns account for most incidents. The key diagnostic signal is the gap between xact_start and query_start in pg_stat_activity. A large gap means the transaction has outlived its most recent statement, so whatever locks it acquired earlier are still held.
Idle in transaction. A session ran a statement, never committed, and went quiet. It holds every lock it took until the transaction ends. Because it is waiting on its client and not on the database, nothing inside Postgres will finish it.
Long-running analytical queries. A report or export holds AccessShareLock for its entire runtime. That lock conflicts with nothing in normal DML, but it does conflict with ACCESS EXCLUSIVE DDL. The DDL then queues behind it, and new readers queue behind the DDL.
Unindexed foreign keys. Deleting or updating a referenced key in the parent forces Postgres to check the child table for matching rows. Without an index on the child's referencing column, that check is a sequential scan, run while the parent row locks are held. Each statement holds its locks far longer than it should.
Network calls inside a transaction. The application opens a transaction, writes a row, then calls a payment API or a queue. The row lock is held for the network round trip, plus any retries and timeouts.
| Cause | state |
wait_event on holder |
xact_start age |
|---|---|---|---|
| Idle in transaction | idle in transaction |
ClientRead |
Large and growing |
| Analytical query | active |
Often empty or I/O-related | Equals query runtime |
| Unindexed FK | active (DELETE/UPDATE) |
Often empty or I/O-related | Moderate, repeated across many sessions |
| Network call | Alternates active / idle in transaction |
ClientRead while idle |
Seconds, recurring |
Interpret pid with care behind a pooler in transaction mode. One backend serves many clients in turn, so client_addr shows the pooler's address, not the application's. application_name may reflect whichever client last set it, not the one that opened the current transaction. Correlate using the query text and timestamps instead.
How do lock_timeout, statement_timeout and idle_in_transaction_session_timeout differ?
Running DDL with SET LOCAL lock_timeout inside the migration's transaction (for example, '3s') changes how it fails. Without it, an ALTER TABLE that cannot get its lock waits in the queue, and every query arriving after it waits too. With it, the statement aborts after the specified time spent waiting for a lock, the transaction rolls back, and normal traffic resumes. SET LOCAL reverts at commit or rollback, so pooled connections never inherit the value. The migration runner should catch the lock-timeout error, sleep with jittered backoff, and retry up to a fixed cap.
| Setting | What it limits | What happens on expiry | Typical scope |
|---|---|---|---|
lock_timeout |
Time a statement waits to acquire a lock | Statement errors, transaction aborts | SET LOCAL per migration |
statement_timeout |
Total statement runtime, including lock waits | Statement errors, transaction aborts | Role or session |
idle_in_transaction_session_timeout |
Idle time inside an open transaction | Session is terminated | Role or database |
Aggressive values have costs. On a hot table, a 1-second lock_timeout might succeed only rarely. If each attempt succeeds with probability p, the expected number of attempts is 1/p.
Illustrative estimate (assumed numbers, not a benchmark): with p = 0.1 and a 1 s timeout plus about 4 s of backoff per failed attempt, you can expect around 10 attempts, or 50 seconds.
Tooling must also fail loudly when it reaches the retry cap rather than reporting partial success. A short idle_in_transaction_session_timeout will also terminate legitimate sessions, such as interactive psql users or batch jobs that pause between steps.
Building retry handling once costs a few days of engineering work. A single queued ACCESS EXCLUSIVE lock can cause a full outage on a core table.
import random
import time
import psycopg2
from psycopg2 import errors
DSN = "dbname=app user=migrator host=db.internal" # placeholder
DDL = "ALTER TABLE orders ADD COLUMN note text"
MAX_ATTEMPTS = 10
def run_migration():
conn = psycopg2.connect(DSN)
try:
for attempt in range(1, MAX_ATTEMPTS + 1):
try:
with conn.cursor() as cur:
# psycopg2 opens a transaction implicitly, so SET LOCAL
# applies only to this attempt and reverts on commit/rollback.
cur.execute("SET LOCAL lock_timeout = '3s'")
cur.execute(DDL)
conn.commit()
print(f"migration applied on attempt {attempt}")
return
except errors.LockNotAvailable: # SQLSTATE 55P03
# Give up the lock request so queued traffic can proceed.
conn.rollback()
# Capped exponential backoff with jitter so that retries
# from multiple runners do not line up.
delay = min(30, 2 ** attempt) * random.uniform(0.5, 1.0)
print(f"attempt {attempt}: lock timeout, retrying in {delay:.1f}s")
time.sleep(delay)
# Fail loudly at the cap instead of reporting partial success.
raise RuntimeError(f"could not acquire lock after {MAX_ATTEMPTS} attempts")
finally:
conn.close()
if __name__ == "__main__":
run_migration()
A migration runner sets lock_timeout with SET LOCAL for each attempt, catches the lock-timeout error (SQLSTATE 55P03), rolls back, retries with jittered backoff, and raises an error once it reaches the retry cap.
How do you debug a Postgres deadlock?
Postgres does not check for deadlocks on every lock request. When a backend has waited on a lock for deadlock_timeout (one second by default), it runs the deadlock detector, which looks for a cycle in the waits-for graph. If it finds one, it aborts one transaction in the cycle with ERROR: deadlock detected. The other participants then proceed. Your application sees the error only on the aborted session, so treat it as retryable.
The client error is terse. The server log has the detail. Each line names a process id, the lock mode it wants, the object or transaction it waits on, and the process blocking it. For example: "Process 4121 waits for ShareLock on transaction 9087; blocked by process 4133." The log also lists the statement each process was running. Match the process ids against your application logs to find the two code paths involved. A ShareLock on a transaction id usually means both sessions are waiting on rows the other has already updated.
Turn on log_lock_waits as well. With it enabled, any wait longer than deadlock_timeout is logged even when no deadlock exists. The entry names the holder and the wait queue. This catches contention spikes that clear before a pg_locks polling query would ever see them.
The setting involves a trade-off:
- Lower
deadlock_timeoutsurfaces deadlocks faster, but the detector runs on more ordinary short waits, which adds overhead. - Higher
deadlock_timeoutleaves deadlocked sessions hanging longer.
Because log_lock_waits uses the same threshold, raising deadlock_timeout also makes lock-wait logging less sensitive.
The fix is to acquire locks in a consistent order on every code path. For example, update rows sorted by primary key, or lock the parent row before the child rows. A cycle cannot form when every transaction requests locks in the same sequence.
-- 1. Log any lock wait longer than deadlock_timeout, even when it is not a deadlock.
-- Both settings can be applied with a reload; no restart needed.
ALTER SYSTEM SET log_lock_waits = on;
ALTER SYSTEM SET deadlock_timeout = '1s'; -- default; also the log_lock_waits threshold
SELECT pg_reload_conf();
SHOW log_lock_waits;
SHOW deadlock_timeout;
-- 2. Deadlock-prone pattern: two transfers touching the same rows in opposite order.
-- Session A: UPDATE id = 17, then id = 42
-- Session B: UPDATE id = 42, then id = 17
-- Each ends up waiting on a row the other already holds, which forms a cycle.
-- 3. Fix: every code path locks the rows it needs up front, in primary-key order.
BEGIN;
-- Lock both rows in ascending id order before modifying either one.
-- Every session takes id 17 before id 42, so no session can hold 42 while waiting on 17.
SELECT id
FROM accounts
WHERE id IN (42, 17)
ORDER BY id
FOR UPDATE;
-- The row locks are already held, so the order of these statements no longer matters.
UPDATE accounts SET balance = balance - 100 WHERE id = 42;
UPDATE accounts SET balance = balance + 100 WHERE id = 17;
COMMIT;
-- If a deadlock still occurs, the aborted session gets SQLSTATE 40P01
-- (deadlock_detected). Roll back and retry the whole transaction.
Turning on log_lock_waits with an explicit deadlock_timeout, then avoiding a deadlock cycle by locking rows in primary-key order with SELECT ... ORDER BY id FOR UPDATE before updating them.
Should you use pg_cancel_backend or pg_terminate_backend?
Once you have the root blocker's pid, the remedies go from least to most destructive. Use the mildest one that will clear the queue.
Wait or fix the application. If the blocker is a legitimate long write that will finish soon, waiting is often cheapest. If it is a session left idle in a transaction, the real fix is in the code path that opened the transaction and never committed it.
pg_cancel_backend(pid) cancels the query the backend is running now. The session survives. Whether its transaction can continue depends on the client: it may roll back, or it may roll back to a savepoint. Cancellation has no effect on a session that is idle in transaction, because no query is running. Those locks stay held.
pg_terminate_backend(pid) closes the connection. Postgres rolls back the entire transaction and releases every lock it held. All uncommitted work is lost, not only the current statement. The client sees a dropped connection. Its retry logic may immediately run the same transaction again and rebuild the same queue. If the transaction had already sent an email, called an API or published a message, those effects remain. The database rows that should match them are now gone.
| Option | Effect | What is lost | When appropriate |
|---|---|---|---|
| Wait / fix app | None immediately | Nothing | Blocker will finish soon; recurring cause in code |
pg_cancel_backend |
Stops current query | That statement, possibly the transaction | Blocker is actively running a query |
pg_terminate_backend |
Ends session, full rollback | Whole transaction; connection | Idle-in-transaction blocker, or cancel failed |
Both functions return a boolean. They require superuser, the same role as the target, or membership in pg_signal_backend.
What are the alternatives to waiting on locks, and how do you prevent contention?
The most reliable prevention is keeping transactions short. Locks are held until commit or rollback, so do network calls, file I/O and user-facing waits outside the transaction, not between BEGIN and COMMIT.
Lock explicitly, in a consistent order
When a transaction must update several rows, take row locks up front with SELECT ... FOR UPDATE, and always in the same order, such as by primary key ascending. Deadlocks need two sessions acquiring the same locks in opposite orders. A deterministic order removes that possibility.
NOWAIT and SKIP LOCKED
NOWAIT raises an error immediately instead of queueing. SKIP LOCKED silently omits rows that are already locked. Both fit queue-style workloads, where several workers claim jobs and any free job will do.
Both also change semantics. With NOWAIT, callers must catch the lock-not-available error and decide whether to retry or give up. With SKIP LOCKED, the result set is deliberately inconsistent, so do not use it for reports, totals or any query that must see every matching row.
Advisory locks
For coordination that does not map to a row, such as "only one instance runs this cron job," use advisory locks like pg_try_advisory_lock. Postgres tracks them, but only your application gives them meaning.
Stage schema changes
Split migrations so the ACCESS EXCLUSIVE step is brief. Use CREATE INDEX CONCURRENTLY. Add constraints as NOT VALID, then run VALIDATE CONSTRAINT separately. Set a low lock_timeout so a migration that can't get its lock fails and retries instead of queueing traffic behind it.
Each of these habits removes a common class of incident. That means fewer pages, shorter on-call shifts and less time spent reconstructing lock graphs after the fact.
How can reading pg_locks save engineering time?
Most of the time lost in a lock incident goes to working out who is blocking whom. Without lock data, engineers tend to restart application pods, raise pool sizes or kill whichever sessions look slow. None of these target the cause, and each can make things worse. The approach in this article saves time in several concrete ways.
Faster diagnosis during an incident. The root-blocker query turns "the database is slow" into a single pid with a state, a transaction age and the last statement it ran. The on-call engineer can see in one step whether the problem is an idle-in-transaction session, a long report or a queued migration, and choose the right remedy from the cancel-or-terminate table instead of experimenting.
Smaller blast radius. Ending only the root blocker leaves unrelated work alone. Killing sessions at random can roll back legitimate transactions, trigger client retries that rebuild the same queue, and create follow-up work to reconcile side effects such as sent emails or API calls.
Fewer incidents in the first place. A lock_timeout with retries in the migration runner turns a potential table-wide stall into a migration that waits its turn. An idle_in_transaction_session_timeout on application roles closes forgotten transactions before they block a deploy. Consistent lock ordering removes a class of deadlocks entirely. Each of these is a one-time change that prevents a recurring kind of page.
Better post-incident review. With log_lock_waits enabled, the server log records who waited on whom, even for spikes that cleared before anyone looked. The review can name the code path that held the lock instead of reconstructing the timeline from application metrics.
Shared vocabulary across teams. When application engineers, SREs and DBAs all read granted, mode, pg_blocking_pids() and xact_start the same way, handoffs during an incident are shorter and fixes land in the right code path.
Illustrative estimate (assumed inputs, not a measured result): assume a team has a lock-related incident about once a month. Assume that without a prepared query each incident takes two engineers about an hour to diagnose, and that with the root-blocker query saved in a runbook diagnosis takes about ten minutes. Under those assumptions the team recovers roughly 20 engineer-hours a year on diagnosis alone. This does not count incidents prevented by timeouts and lock ordering. Substitute your own incident rate and diagnosis times to get a figure that fits your situation.
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 postgres lock contention pg_locks, 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 route's URL or method automatically notifies the services that consume it — no hunting through repos for callers.
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.
- Consumers learn about a breaking change at review time rather than from production errors.
- 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.