PostgreSQL ロック待ちの原因となっているSQLのセッションを特定する

ロックの状態を確認するのはpg_loksというビューを見れば現在の状況が分かります。
そのセッションがロックを「保持している」のかまたは「ロック待ちが発生している」のかはgrantedという項目を参照します。
「PostgreSQL 9.3.2文章」pg_locks
この辺りの情報はマニュアルに譲るとして、、
このpg_locksというビューを表示しただけではズラーっとロックを保持しているセッションが表示されて非常に見づらく、
実際にロックが発生した時にはこれだけではあまり役に立ちません。
では実際の運用中にロック待ちが発生してトランザクションが流れなくなってしまった場合にどうするか？

【おすすめ参考書】

内部構造から学ぶPostgreSQL 設計・運用計画の鉄則 (Software Design plus)

posted with amazlet at 14.10.26

勝俣智成佐伯昌樹原田登志
技術評論社
売り上げランキング: 9,206

Amazon.co.jpで詳細を見る

ロックを確認するSQLも色々ネットで公開されているが、いまいちどれも分かりにくく実際の運用では使いにくい。
ということで作ってみたのがこれ

SELECT
 a.datname, l.pid, a.client_addr, c.relname, l.locktype, l.mode, (current_timestamp - xact_start)::interval(3) AS duration, a.query AS query
FROM
 pg_locks l LEFT OUTER JOIN pg_stat_activity a
ON l.pid = a.pid LEFT OUTER JOIN pg_class c ON l.relation = c.oid
WHERE  l.pid?!= pg_backend_pid() AND l.locktype in ( 'relation','transactionid','tuple' ) AND l.granted
AND c.relname = ( SELECT DISTINCT c.relname 
                  FROM pg_locks l
                    LEFT OUTER JOIN pg_stat_activity a
                      ON l.pid = a.pid 
                         LEFT OUTER JOIN pg_class c ON l.relation = c.oid
                  WHERE l.pid?!= pg_backend_pid() 
                      AND c.relname IS NOT NULL
                      AND l.locktype in ( 'relation','transactionid','tuple' ) 
                      AND l.granted 
                      AND l.pid=( SELECT l.pid 
                                  FROM pg_locks l 
                                     LEFT OUTER JOIN pg_stat_activity a ON l.pid = a.pid
                                  WHERE l.pid?!= pg_backend_pid() 
                                      AND NOT l.granted
                                  ORDER BY (current_timestamp - xact_start)::interval(3) desc limit 1) )
ORDER BY duration DESC LIMIT 1;

このSQLで何をやっているかを簡単に説明すると、ロック待ちが最も長時間発生しているプロセスIDのオブジェクトを調べて、その同じオブジェクトにロックを掛けているトランザクションのうち一番長時間流れているSQLを出力します。これがロック待ちを発生させている原因であることはほぼ間違いないと思います。

実行結果はこんな感じで出力されます。キャンセルしても問題ないようであればpidを指定してキャンセルします。

   datname   |  pid  | client_addr  | relname  | locktype |     mode     |   duration   |               query
-------------+-------+--------------+----------+----------+--------------+--------------+------------------------------------
    test_db  | 18705 | 123.45.67.89 | pass_tbl | relation | RowShareLock | 00:00:15.815 | select * from pass_tbl for update;

ロック待ちが発生して、トランザクションが動かなくなった場合はこいつを一発流せばその原因となるSQLが分かります。
実行する場合は問題の発生しているデータベースに接続して実行して下さい。ちなみみ対象バージョンは9.2以上です。

photo by Yuko Honda