Lock Monitoring/ja

原文最終更新日：8 April 2016

現在のロックトランザクションをオンラインでビューにより確認する

pg_locksビュー

pg_locksを参照すると、ロックが付与されたり、プロセスが獲得されたロックのために待たされていることを確認できます。ロックの問題を探し始めるために良いクエリは以下となります：

  select relation::regclass, * from pg_locks where not granted;

pg_stat_activityビュー

• プロセスが掴んでいる又はロックを待っていることを探し出すには、pg_stat_activityの情報を相互参照するのが簡単です。

ブロックされた又はブロック中の活動の組み合わせ

以下のクエリは、プロセスがSQLステートメントをブロックしていることを確認する手助けとなるでしょう（これらは行レベルのロックを発見するのみであり、オブジェクトレベルのロックは発見できません）。

PostgreSQLバージョン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;

PostgreSQLバージョン9.2以上:

  SELECT blocked_locks.pid     AS blocked_pid,
         blocked_activity.usename  AS blocked_user,
         blocking_locks.pid     AS blocking_pid,
         blocking_activity.usename AS blocking_user,
         blocked_activity.query    AS blocked_statement,
         blocking_activity.query   AS current_statement_in_blocking_process
   FROM  pg_catalog.pg_locks         blocked_locks
    JOIN pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid = blocked_locks.pid
    JOIN pg_catalog.pg_locks         blocking_locks 
        ON blocking_locks.locktype = blocked_locks.locktype
        AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
        AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
        AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
        AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
        AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
        AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
        AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
        AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
        AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
        AND blocking_locks.pid != blocked_locks.pid

    JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
   WHERE NOT blocked_locks.granted;

これはアプリケーション名を含んだ同じデータを示す拡張ビューです

それぞれのトランザクションの最初でapplication_name変数をセットすることでどの論理プロセスがほかのプロセスをブロックするかを示すことができます。トランザクションを開始するソースコード行又はコード内でapplication_nameが合致することによる他のどれかの情報が有効な情報となりえます。（訳注：それぞれのトランザクション処理に別のapplication_nameを設定することでブロックしている処理がピンポイントで識別可能となります。）

SET application_name='%your_logical_name%';

PostgreSQLバージョン9.2以上:

SELECT blocked_locks.pid     AS blocked_pid,
         blocked_activity.usename  AS blocked_user,
         blocking_locks.pid     AS blocking_pid,
         blocking_activity.usename AS blocking_user,
         blocked_activity.query    AS blocked_statement,
         blocking_activity.query   AS current_statement_in_blocking_process,
         blocked_activity.application_name AS blocked_application,
         blocking_activity.application_name AS blocking_application
   FROM  pg_catalog.pg_locks         blocked_locks
    JOIN pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid = blocked_locks.pid
    JOIN pg_catalog.pg_locks         blocking_locks 
        ON blocking_locks.locktype = blocked_locks.locktype
        AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
        AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
        AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
        AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
        AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
        AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
        AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
        AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
        AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
        AND blocking_locks.pid != blocked_locks.pid
 
    JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
   WHERE NOT blocked_locks.GRANTED;

これはどれくらい状態が古いかのアイディアを含んだ同じデータを示す拡張ビューです

  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;

これはほとんどまったく同じ内容のビューですが、いくつかより多くの詳細情報を伴います

PostgreSQLバージョン9.2未満:

  select bl.pid                 as blocked_pid,
         a.usename              as blocked_user,
         ka.current_query       as current_statement_in_blocking_process,
         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;

PostgreSQLバージョン9.2以上:

  select bl.pid                 as blocked_pid,
         a.usename              as blocked_user,
         ka.query               as current_or_recent_statement_in_blocking_process,
         ka.state               as state_of_blocking_process,
         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;

後で分析するためのロギング

• たまに問題を起こすような断続的なロックを疑っている場合、しかし上記のオンラインビューでそれらを確認することに問題があるなら、log_lock_waitsを設定したり、deadlock_timeoutに関連するパラメータが手助けとなります。ロックの取得を遅くすることによって、後の解析のためのデータベースログを取得できます。

以下も参照してください

• Lock dependency information ロックの依存情報