[PGS9.2] View for lock or/and blocking monitoring

/****************************************************************************************************************
-- Title : [PGS9.2] View for lock or/and blocking monitoring
-- Reference : wiki.postgresql.org
-- Key word : lock blocking 락 블로킹
****************************************************************************************************************/

pg_locks view

Looking at pg_locks shows you what locks are granted and what processes are waiting for locks to be acquired. A good query to start looking for lock problems:

  SELECT relation::regclass, * FROM pg_locks WHERE NOT granted;

pg_stat_activity view

• Figuring out what the processes holding or waiting for locks is easier if you cross-reference against the information inpg_stat_activity

Сombination of blocked and blocking activity

The following query may be helpful to see what processes are blocking SQL statements (these only find row-level locks, not object-level locks).

For PostgreSQL Version < 9.2:

  SELECT bl.pid          AS blocked_pid,          a.usename       AS blocked_user,          kl.pid          AS blocking_pid,          ka.usename      AS blocking_user,          a.current_query AS blocked_statement   FROM  pg_catalog.pg_locks         bl    JOIN pg_catalog.pg_stat_activity a  ON a.procpid = bl.pid    JOIN pg_catalog.pg_locks         kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid    JOIN pg_catalog.pg_stat_activity ka ON ka.procpid = kl.pid   WHERE NOT bl.granted;

For PostgreSQL Version >= 9.2:

  SELECT bl.pid     AS blocked_pid,          a.usename  AS blocked_user,          kl.pid     AS blocking_pid,          ka.usename AS blocking_user,          a.query    AS blocked_statement    FROM  pg_catalog.pg_locks         bl     JOIN pg_catalog.pg_stat_activity a  ON a.pid = bl.pid     JOIN pg_catalog.pg_locks         kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid     JOIN pg_catalog.pg_stat_activity ka ON ka.pid = kl.pid    WHERE NOT bl.granted;

Here's an alternate view of that same data that includes an idea how old the state is

  SELECT a.datname,          c.relname,          l.transactionid,          l.mode,          l.granted,          a.usename,          a.current_query,           a.query_start,          age(now(), a.query_start) AS "age",           a.procpid      FROM  pg_stat_activity a      JOIN pg_locks         l ON l.pid = a.procpid      JOIN pg_class         c ON c.oid = l.relation     ORDER BY a.query_start;

Here's almost quite the same thing but with some more details

For PostgreSQL Version < 9.2:

  SELECT bl.pid                 AS blocked_pid,          a.usename              AS blocked_user,          ka.current_query       AS blocking_statement,          now() - ka.query_start AS blocking_duration,          kl.pid                 AS blocking_pid,          ka.usename             AS blocking_user,          a.current_query        AS blocked_statement,          now() - a.query_start  AS blocked_duration   FROM  pg_catalog.pg_locks         bl    JOIN pg_catalog.pg_stat_activity a  ON a.procpid = bl.pid    JOIN pg_catalog.pg_locks         kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid    JOIN pg_catalog.pg_stat_activity ka ON ka.procpid = kl.pid   WHERE NOT bl.granted;

For PostgreSQL >= 9.2:

  SELECT bl.pid                 AS blocked_pid,          a.usename              AS blocked_user,          ka.query               AS blocking_statement,          now() - ka.query_start AS blocking_duration,          kl.pid                 AS blocking_pid,          ka.usename             AS blocking_user,          a.query                AS blocked_statement,          now() - a.query_start  AS blocked_duration   FROM  pg_catalog.pg_locks         bl    JOIN pg_catalog.pg_stat_activity a  ON a.pid = bl.pid    JOIN pg_catalog.pg_locks         kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid    JOIN pg_catalog.pg_stat_activity ka ON ka.pid = kl.pid   WHERE NOT bl.granted;

Logging for later analysis

• If you suspect intermittent locks are causing problems only sometimes, but are having trouble catching them in one of these live views, setting the log_lock_waits and related deadlock_timeout parameters can be helpful. Then slow lock acquisition will appear in the database logs for later analysis.