[9.2] View for lock or/and blocking monitoring

/****************************************************************************************************************
-- Title : [9.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.