Skip to content
DBAExperts
PostgreSQL Diagnostics

How to view and resolve locks in PostgreSQL with pg_locks and pg_stat_activity

A lock appears when one transaction waits for a resource that another one holds. The key is to identify which session is blocking which before deciding whether to wait or cancel it.

  1. 1

    Look at active sessions and the ones waiting

    pg_stat_activity shows the state of each connection. Rows with wait_event_type = Lock are waiting on a lock.

    SELECT pid, state, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE state <> 'idle';
  2. 2

    Identify who is blocking whom

    The pg_blocking_pids function returns the processes holding the lock that another session is waiting on. It is the direct way to see the blocking chain.

    SELECT pid, pg_blocking_pids(pid) AS bloqueado_por, query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;
  3. 3

    Cancel the blocking query (the gentle option)

    pg_cancel_backend tries to abort the running query without closing the connection. It is the first, least aggressive option.

    SELECT pg_cancel_backend(12345);
  4. 4

    Terminate the session if cancel is not enough

    pg_terminate_backend closes the entire connection. Use it carefully: the running transaction is rolled back and the application will see a connection error.

    SELECT pg_terminate_backend(12345);

// common mistake

Killing the wrong PID with pg_terminate_backend can take down a legitimate transaction and cause more damage than the original lock. Confirm with pg_blocking_pids which process is actually holding the lock.

// when it's worth an expert

If locks are recurring, they are usually a symptom: long transactions, inconsistent update order, or missing indexes. At dba.mx we find the root cause so they stop happening, instead of just putting out the fire of the moment, at a fixed price.

Book an assessment — from USD $550

This guide is for reference and uses example commands. In production, adapt to your version and test in a safe environment first.