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 ロックの依存情報
Category:Administration
Category:Performance
Category:Monitoring
Category:Japanese