オブジェクトとロックモード別にロック待機を詳細に調査(pg_locks)

コマンド

SELECT DISTINCT
       w.pid AS waiting_pid,
       w.locktype,
       coalesce(w.relation::regclass::text, 'xid ' || w.transactionid, w.virtualxid, w.objid::text) AS target,
       w.mode AS wanted_mode,
       b.pid AS blocker_pid,
       b.mode AS blocker_mode,
       b.granted AS blocker_holds,
       now() - wa.state_change AS waiting_for,
       left(wa.query, 60) AS waiting_query,
       left(ba.query, 60) AS blocker_query
FROM pg_locks w
JOIN pg_locks b
  ON b.pid = ANY (pg_blocking_pids(w.pid))
 AND b.locktype = w.locktype
 AND b.database IS NOT DISTINCT FROM w.database
 AND b.relation IS NOT DISTINCT FROM w.relation
 AND b.page IS NOT DISTINCT FROM w.page
 AND b.tuple IS NOT DISTINCT FROM w.tuple
 AND b.virtualxid IS NOT DISTINCT FROM w.virtualxid
 AND b.transactionid IS NOT DISTINCT FROM w.transactionid
 AND b.classid IS NOT DISTINCT FROM w.classid
 AND b.objid IS NOT DISTINCT FROM w.objid
 AND b.objsubid IS NOT DISTINCT FROM w.objsubid
JOIN pg_stat_activity wa ON wa.pid = w.pid
JOIN pg_stat_activity ba ON ba.pid = b.pid
WHERE NOT w.granted
ORDER BY waiting_for DESC, waiting_pid;

概要

許可されていないすべてのロック要求(granted = false)について、対象オブジェクト(表、トランザクションIDなど)、要求モード、ブロックしているPIDとそのモード、ブロッカーが実際にロックを保持しているか、おおよその待機時間を表示する。

使用する場面

どの表とロックモードの競合がブロッキングの原因かを把握するため。例: AccessExclusiveLockを待つDDL、transactionid待機として現れる行ロック。

注意

照合にpg_blocking_pids()を使用するため、実際に競合する要求のみが一覧表示される。blocker_holds = falseは、ブロッカーがまだロックを保持していないが、競合するモードで先にキューに並んでいることを意味する(ロックキュー)。locktype = 'transactionid'は行レベルの競合: 待機側は行を変更したトランザクションが終了するまで待機する。行そのものはpg_locksには表示されない。大規模システムではpg_locksに数千行が含まれる場合があるため、頻繁に実行しない。waiting_forはstate_changeに基づく近似値。