Postgres pg_locks: Reading the Real-Time Lock Graph

The pg_locks system view shows every lock currently held or waiting in the database. It is one of the most important diagnostic surfaces in Postgres and one of the least frequently consulted between incidents. Most teams reach for it the first time during an outage, discover that the columns are not obviously interpretable, and end up copying a query from Stack Overflow that returns something they cannot fully explain. The structure is worth understanding before the next incident rather than during it.

What the view actually shows

Each row in pg_locks represents one lock that some backend process either holds or is waiting to acquire. The columns describe what kind of lock it is, what resource it targets, which backend owns or wants it, and whether the lock has been granted. The view is a snapshot of the in-memory lock manager state and reflects the situation at the instant the query reads it.

The most important columns are locktype (the kind of resource being locked: relation, tuple, transactionid, virtualxid, advisory, page, object), mode (the lock strength: AccessShare, RowShare, RowExclusive, ShareUpdateExclusive, Share, ShareRowExclusive, Exclusive, AccessExclusive), granted (true if the lock is held, false if waiting), pid (the backend that owns or wants the lock), and the relation or transaction identifiers that locate the specific resource.

The structure is normalized in a way that is unusual for diagnostic views. A backend waiting for a row lock will have one row in pg_locks for the relation lock (likely already granted), another for the transactionid lock it is waiting on (granted = false), and possibly others for other resources it touched in the same transaction. Reading the view requires aggregating across multiple rows to understand what any one backend is actually doing.

The blocking-pid diagnostic

The single most useful query against pg_locks is the blocking-pid join, which identifies which backend is waiting for which other backend. The modern shortcut is pg_blocking_pids(pid), a function that returns the list of PIDs blocking a given backend. Combined with pg_stat_activity, it produces a readable view of the lock contention:

SELECT blocked.pid AS blocked_pid,
       blocked.query AS blocked_query,
       blocked.wait_event_type,
       blocked.wait_event,
       blocking.pid AS blocking_pid,
       blocking.query AS blocking_query,
       blocking.state AS blocking_state,
       blocking.xact_start AS blocking_xact_start
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE blocked.wait_event_type = 'Lock';

The query returns one row per blocked-blocking pair. The most common shape is a single long transaction holding a lock that several other transactions are waiting for, which appears as several rows all pointing at the same blocking pid. The blocking transaction often shows state = 'idle in transaction', which is the canonical bad state where a transaction has done work and is waiting on the client to send the next statement or commit.

The pre-9.6 way to do this same diagnostic involved a self-join on pg_locks comparing transactionid and relation columns. The modern pg_blocking_pids handles all the lock-type cases uniformly and should be preferred.

Distinguishing the lock types that bite

The lock modes in pg_locks.mode fall into a hierarchy of conflicts. Two locks on the same resource conflict if their modes appear together in the conflict matrix from the Postgres documentation. Reading the matrix carefully is worth doing once; the practical summary is that AccessShare (taken by SELECT) and RowShare (taken by SELECT FOR SHARE) conflict only with AccessExclusive (taken by DDL like DROP TABLE, ALTER TABLE for many cases, TRUNCATE). RowExclusive (taken by INSERT, UPDATE, DELETE) conflicts with Share, ShareRowExclusive, Exclusive, AccessExclusive. Two RowExclusive locks on the same table do not conflict at the table level.

The row-level locks taken by UPDATE and DELETE are represented as transactionid locks in pg_locks, not as row-level locks in the usual sense. The actual conflict is "transaction B is waiting for transaction A to commit or abort because A holds the lock on a row B wants to modify." This is why the blocking-pid diagnostic is more useful than reading individual pg_locks rows: the row-level conflict structure is encoded across multiple rows and is awkward to reconstruct from the view alone.

The wait events that complement pg_locks

The wait_event_type and wait_event columns in pg_stat_activity give the human-readable reason a backend is waiting. The most important categories for lock diagnosis are Lock (the backend is waiting for a heavyweight lock visible in pg_locks), LWLock (waiting for an internal lightweight lock not visible in pg_locks, usually buffer or WAL contention), and Client (waiting for the client to send the next message, often the idle-in-transaction state).

The Lock wait events with the specific lock type (Lock:transactionid, Lock:relation, Lock:tuple) tell you what kind of contention is happening without requiring you to read pg_locks. LWLock wait events (LWLock:BufferContent, LWLock:WALWrite) indicate internal contention that does not appear in pg_locks and that usually points at workload patterns or configuration issues rather than transaction structure.

The autovacuum-blocked-by-DDL trap

One of the more confusing patterns in pg_locks is autovacuum being blocked by an application transaction. Autovacuum needs a lock (mode varies depending on what it is doing) on the table to clean up dead tuples. If an application transaction holds a conflicting lock on the table, autovacuum waits. If the application transaction is long-running, autovacuum can be blocked for hours, accumulating dead tuples and eventually contributing to wraparound risk.

The diagnostic is to look for autovacuum entries in pg_stat_activity (the backend_type column is 'autovacuum worker') and check whether they appear as blocked in the blocking-pid view. The mitigation is to identify and either complete or terminate the application transaction that is blocking autovacuum. The structural fix is to keep transactions short and to monitor idle-in-transaction durations.

The idle-in-transaction long-running query

The query to identify backends that have been in the idle-in-transaction state for more than a configurable threshold is one of the most useful for ongoing health monitoring:

SELECT pid,
       usename,
       application_name,
       state,
       NOW() - xact_start AS xact_duration,
       NOW() - state_change AS idle_duration,
       query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND NOW() - state_change > INTERVAL '5 minutes';

The thresholds for alerting depend on the workload. For online transactional workloads, five minutes is a reasonable warning threshold and fifteen minutes is a reasonable termination threshold. The configuration knob idle_in_transaction_session_timeout can be set to terminate offending backends automatically, which is the right default for any production database.

Diagnosing under pressure

The challenge with pg_locks during an incident is that the view itself is contention-sensitive. Running expensive queries against pg_locks during a lock storm can make the storm worse. The discipline is to have the diagnostic queries written, tested, and ready to run as named saved queries or as scripts, so that the during-incident operation is just executing them rather than constructing them.

The pattern of "save the diagnostic, do not write it from scratch in the moment" applies to most database operational tooling. The autovacuum-blocked query, the blocking-pid query, the idle-in-transaction query, and a handful of others should be in every team's runbook. The marginal cost of having them is low and the value during an incident is substantial.

The pg_cancel_backend vs pg_terminate_backend escalation

When the diagnosis points at a specific backend that needs to stop, the two options are pg_cancel_backend(pid) (interrupts the current query but keeps the connection alive) and pg_terminate_backend(pid) (closes the connection entirely). Cancel is the right first attempt because it leaves the connection open and allows clean teardown. Terminate is the escalation when cancel does not work, usually because the backend is in a state where it cannot respond to the signal.

The application-side consequence of pg_terminate_backend depends on the client library. Most libraries see the connection close and either return an error to the caller or transparently reconnect. The choice between cancel and terminate depends on whether you want the application transaction to fail with an explicit cancellation error (cancel) or with a connection error (terminate). Cancel is more polite and should be the default.

The connection across our products

Our four products (DocuMint for PDF invoice generation, CronPing for cron job monitoring, FlagBit for feature flags, and WebhookVault for webhook debugging) are SQLite-based at our current scale and the pg_locks diagnostic does not apply directly. SQLite has a simpler concurrency model (one writer at a time, multiple readers) with much less surface for lock diagnosis. The equivalent diagnostic in SQLite is reading the busy and locked error returns and the schema-version metadata, which is much less rich than what pg_locks offers.

Our planned Postgres migration will inherit the lock-diagnostic surface, and the runbook for the migrated services will include the queries described above. The investment in understanding pg_locks ahead of the migration is one of the small disciplines that prevents the migration from becoming a learning experience under production pressure.

What pg_locks does not show

The view does not show LWLock contention, which is internal Postgres synchronization for things like shared buffer access and WAL writing. LWLock contention shows up in pg_stat_activity.wait_event but not in pg_locks, and the diagnosis is qualitatively different (usually requires looking at workload patterns and configuration rather than at specific transactions).

The view does not show advisory locks unless you set the appropriate flag. By default, pg_locks shows heavyweight locks; advisory locks taken via pg_advisory_lock appear when present but are easy to overlook unless you specifically filter for them.

The view does not show historical lock contention. The current state is visible but there is no timestamp on individual lock acquisitions, and the view changes continuously. Capturing historical lock contention requires sampling pg_locks at regular intervals and storing the snapshots, which most teams do not do until they have an incident that motivates it.

Three observations

First: pg_locks is the kind of system view that rewards investment when you do not need it. Reading the documentation and running the diagnostic queries against a healthy database, when nothing is wrong, builds the mental model that makes the during-incident usage fast and correct. The cost of doing this is an hour or two of attention; the value during an incident is the difference between a quick diagnosis and a long one.

Second: the conflict matrix between lock modes is one of the foundational pieces of knowledge that distinguishes engineers who can debug Postgres lock issues from engineers who cannot. The matrix is small (eight modes in two dimensions), and once internalized it makes most lock-contention diagnoses straightforward. The discipline of reading the matrix until you can predict which modes conflict pays back broadly across operational work.

Third: the most common production lock contention is not the dramatic deadlock or the sudden lock storm. It is the steady drip of long-running transactions holding row locks that other transactions need, producing elevated latency on a small subset of operations without producing obvious errors. The diagnostic surface for this pattern is the idle-in-transaction monitoring described above, and the prevention is the configuration of idle_in_transaction_session_timeout as a safety net. The dramatic cases are easier to notice; the steady cases require the discipline of monitoring the right metrics.

The deeper observation is that databases are systems whose performance characteristics depend on operational discipline as much as on schema design. Schema design is the work done before the system runs in production; operational discipline is the work that happens continuously while the system runs. The split is similar to architecture-vs-maintenance in any long-lived system, and the long-running databases that perform well are the ones where the operational discipline is taken as seriously as the initial design. Reading pg_locks is a small piece of that discipline, and the small pieces add up to the difference between a database that works and one that does not.