Querious Docs
Account

Server Locks View — PostgreSQL

Who is blocking whom, right now.

The Locks view shows the lock acquisitions in progress on the server. Use it to find lock contention, where one connection holding a lock keeps another connection from obtaining that lock and doing what it needs to do.

The Locks view
The Locks view

Requirements #

To see another user's query text, you must be a superuser or a member of the role pg_read_all_stats. Otherwise, the Query column is blank.

Refreshing #

By default the Locks view automatically refreshes every few seconds so you can watch how problems develop. Use the Pause button in the bottom bar to stop it and study the current locks in more detail, or the Refresh button to reload immediately.

The Charts #

At the top of the Locks view, current locks are summarized by Status, Mode, and Type. The segmented control in the view's toolbar switches between donuts and stacked horizontal bars. Hover a segment for its count and percentage.

Status

  • Granted: The number of locks currently held by a connection.
  • Waiting: The number of locks requested by a connection that can't proceed until another connection releases what it holds.

Mode

  • Each slice is one lock mode that currently exists, using the names listed under Mode in the table below.

Type

  • Each slice is one lock type, described under Lock Types below.

The Lock List #

The Lock List shows each lock, sorted so that blockers come first, with the longest Duration at the top. Beneath each blocker are the connections waiting on it, so a blocking chain reads from top to bottom. Locks that aren't involved in any contention come after them.

Column What it shows
PID The backend process ID of the connection holding or waiting for the lock. Red if the lock is waiting, or amber with an "idle" badge if the connection is idle in transaction (holding locks with no active query).
Blocked By For a waiting lock, the PID of the connection holding the conflicting lock.
Object What's locked (table, page, tuple, transaction ID, etc).
Mode The lock mode, such as RowExclusiveLock.
Status

Waiting: The lock is not yet granted.

Blocking: The lock is held, with others waiting on the same resource.

Granted: The lock is held without contention.
Waiters The number of other connections waiting on the same resource.
Duration How long the connection's current query has run, as a bar relative to the longest in the list.
Query The connection's most recent query (the first 200 characters). (Empty if you don't have permission to see it.)

Lock Types #

The Type of a lock is what kind of thing is locked.

Type What it locks
relation A whole table or index.
page A single page of a table.
tuple

A single row.

Row locks themselves don't appear. A tuple lock shows up only while a connection is queued to lock a row that another connection holds.
transactionid, virtualxid

A transaction. Other connections wait on it until that transaction finishes.

A connection waiting for a locked row shows as waiting on the holder's transactionid lock.
advisory A lock your application takes on purpose.

Reading the Lock Modes #

The Mode column tells you why a connection is blocked. Two connections can't hold *conflicting* lock modes on the same table at once, so the later connection waits. Most lock modes don't conflict with each other, so most locks you see are harmless.

The ordinary lock modes taken by SELECT, INSERT, UPDATE, and DELETE (AccessShareLock and RowExclusiveLock), never block one another. Contention typically comes from schema changes and maintenance commands such as ALTER TABLE and DROP TABLE, because those actions take stronger modes, like AccessExclusiveLock, which blocks everything on the table, or ShareLock, which blocks writes.

See Explicit Locking in the PostgreSQL manual for all of the lock modes and which ones conflict.

Recipes #

To find what's blocking everything

  1. Open the Locks view. If the Status chart shows any Waiting locks, some connections are blocked.

  2. Find the blocker in the first row.

    It has the highest Duration, with its waiters beneath it. Its Waiters count is how many connections are stuck behind it.

  3. Select the blocker and read its query.

    If its PID shows the idle badge, the connection is sitting in an open transaction and isn't running anything. The query shown is the last one it ran, not a slow query.

  4. Note the PID. That's the connection to act on.

To decide whether to wait or kill

  1. Check the blocker's Duration. A few seconds on a query that's actually executing usually clears itself.
  2. An idle blocker won't finish on its own. It holds its locks until the application commits or the connection ends.
  3. One waiter is an inconvenience. A dozen means the queue is growing faster than it drains.

  4. To intervene, find that PID in Activity and stop it.

    Stop Query cancels a running query, but does nothing to an idle connection. Use Kill Connection to end the connection and roll back its open transaction.

To find out why a migration is stuck

  1. Find the migration's row. Its Status is Waiting, and its Query is the ALTER TABLE or other statement being run.

  2. Read Blocked By to see which connection holds what it wants.

    That is often an older transaction that only read the table and was never committed, or a long-running query. Even a plain SELECT keeps its lock until its transaction ends.

  3. Decide whether to wait or kill that connection, as described above.

To catch a blocker that keeps coming back

  1. Leave auto-refresh on and watch the blocker's PID across refreshes.
  2. The same PID each time is one stuck connection. A different PID each time is a workload pattern.
  3. For a pattern, take the query text to Statements to see how often it runs and how long it takes.