Lock dependency information/ja

原文最終更新日：1 October 2015
時に、ロックがお互いに依存していることを見ることはとても役に立ちます。
== Flat View of Blocking ==
== ブロッキングのフラットなビュー ==
waiting_の接頭辞がついたすべてのカラムはロックを獲得していないトランザクションの情報を保持し、other_の接頭辞がついたカラムはそれぞれのトランザクションIDの同一リレーション上の他のロックについての情報を保持しています。
-- PostgreSQLバージョン9.2未満
SELECT 
 waiting.locktype AS waiting_locktype,
 waiting.relation::regclass AS waiting_table,
 waiting_stm.current_query AS waiting_query,
 waiting.mode AS waiting_mode,
 waiting.pid AS waiting_pid,
 other.locktype AS other_locktype,
 other.relation::regclass AS other_table,
 other_stm.current_query AS other_query,
 other.mode AS other_mode,
 other.pid AS other_pid,
 other.granted AS other_granted
FROM
 pg_catalog.pg_locks AS waiting
JOIN
 pg_catalog.pg_stat_activity AS waiting_stm
 ON (
 waiting_stm.procpid = waiting.pid
 )
JOIN
 pg_catalog.pg_locks AS other
 ON (
 (
 waiting."database" = other."database"
 AND waiting.relation = other.relation
 )
 OR waiting.transactionid = other.transactionid
 )
JOIN
 pg_catalog.pg_stat_activity AS other_stm
 ON (
 other_stm.procpid = other.pid
 )
WHERE
 NOT waiting.granted
AND
 waiting.pid <> other.pid
-- PostgreSQLバージョン9.2以上
SELECT 
 waiting.locktype AS waiting_locktype,
 waiting.relation::regclass AS waiting_table,
 waiting_stm.query AS waiting_query,
 waiting.mode AS waiting_mode,
 waiting.pid AS waiting_pid,
 other.locktype AS other_locktype,
 other.relation::regclass AS other_table,
 other_stm.query AS other_query,
 other.mode AS other_mode,
 other.pid AS other_pid,
 other.granted AS other_granted
FROM
 pg_catalog.pg_locks AS waiting
JOIN
 pg_catalog.pg_stat_activity AS waiting_stm
 ON (
 waiting_stm.pid = waiting.pid
 )
JOIN
 pg_catalog.pg_locks AS other
 ON (
 (
 waiting."database" = other."database"
 AND waiting.relation = other.relation
 )
 OR waiting.transactionid = other.transactionid
 )
JOIN
 pg_catalog.pg_stat_activity AS other_stm
 ON (
 other_stm.pid = other.pid
 )
WHERE
 NOT waiting.granted
AND
 waiting.pid <> other.pid
どれくらいの間、待機中ステートメントがブロックされているかを指し示す特別なカラムを追加すると便利でしょう。
== ブロッキングの再帰的なビュー ==
WITH RECURSIVE
 c(requested, current) AS
 ( VALUES
 ('AccessShareLock'::text, 'AccessExclusiveLock'::text),
 ('RowShareLock'::text, 'ExclusiveLock'::text),
 ('RowShareLock'::text, 'AccessExclusiveLock'::text),
 ('RowExclusiveLock'::text, 'ShareLock'::text),
 ('RowExclusiveLock'::text, 'ShareRowExclusiveLock'::text),
 ('RowExclusiveLock'::text, 'ExclusiveLock'::text),
 ('RowExclusiveLock'::text, 'AccessExclusiveLock'::text),
 ('ShareUpdateExclusiveLock'::text, 'ShareUpdateExclusiveLock'::text),
 ('ShareUpdateExclusiveLock'::text, 'ShareLock'::text),
 ('ShareUpdateExclusiveLock'::text, 'ShareRowExclusiveLock'::text),
 ('ShareUpdateExclusiveLock'::text, 'ExclusiveLock'::text),
 ('ShareUpdateExclusiveLock'::text, 'AccessExclusiveLock'::text),
 ('ShareLock'::text, 'RowExclusiveLock'::text),
 ('ShareLock'::text, 'ShareUpdateExclusiveLock'::text),
 ('ShareLock'::text, 'ShareRowExclusiveLock'::text),
 ('ShareLock'::text, 'ExclusiveLock'::text),
 ('ShareLock'::text, 'AccessExclusiveLock'::text),
 ('ShareRowExclusiveLock'::text, 'RowExclusiveLock'::text),
 ('ShareRowExclusiveLock'::text, 'ShareUpdateExclusiveLock'::text),
 ('ShareRowExclusiveLock'::text, 'ShareLock'::text),
 ('ShareRowExclusiveLock'::text, 'ShareRowExclusiveLock'::text),
 ('ShareRowExclusiveLock'::text, 'ExclusiveLock'::text),
 ('ShareRowExclusiveLock'::text, 'AccessExclusiveLock'::text),
 ('ExclusiveLock'::text, 'RowShareLock'::text),
 ('ExclusiveLock'::text, 'RowExclusiveLock'::text),
 ('ExclusiveLock'::text, 'ShareUpdateExclusiveLock'::text),
 ('ExclusiveLock'::text, 'ShareLock'::text),
 ('ExclusiveLock'::text, 'ShareRowExclusiveLock'::text),
 ('ExclusiveLock'::text, 'ExclusiveLock'::text),
 ('ExclusiveLock'::text, 'AccessExclusiveLock'::text),
 ('AccessExclusiveLock'::text, 'AccessShareLock'::text),
 ('AccessExclusiveLock'::text, 'RowShareLock'::text),
 ('AccessExclusiveLock'::text, 'RowExclusiveLock'::text),
 ('AccessExclusiveLock'::text, 'ShareUpdateExclusiveLock'::text),
 ('AccessExclusiveLock'::text, 'ShareLock'::text),
 ('AccessExclusiveLock'::text, 'ShareRowExclusiveLock'::text),
 ('AccessExclusiveLock'::text, 'ExclusiveLock'::text),
 ('AccessExclusiveLock'::text, 'AccessExclusiveLock'::text)
 ),
 l AS
 (
 SELECT
 (locktype,DATABASE,relation::regclass::text,page,tuple,virtualxid,transactionid,classid,objid,objsubid) AS target,
 virtualtransaction,
 pid,
 mode,
 granted
 FROM pg_catalog.pg_locks
 ),
 t AS
 (
 SELECT
 blocker.target AS blocker_target,
 blocker.pid AS blocker_pid,
 blocker.mode AS blocker_mode,
 blocked.target AS target,
 blocked.pid AS pid,
 blocked.mode AS mode
 FROM l blocker
 JOIN l blocked
 ON ( NOT blocked.granted
 AND blocker.granted
 AND blocked.pid != blocker.pid
 AND blocked.target IS NOT DISTINCT FROM blocker.target)
 JOIN c ON (c.requested = blocked.mode AND c.current = blocker.mode)
 ),
 r AS
 (
 SELECT
 blocker_target,
 blocker_pid,
 blocker_mode,
 '1'::int AS depth,
 target,
 pid,
 mode,
 blocker_pid::text || ',' || pid::text AS seq
 FROM t
 UNION ALL
 SELECT
 blocker.blocker_target,
 blocker.blocker_pid,
 blocker.blocker_mode,
 blocker.depth + 1,
 blocked.target,
 blocked.pid,
 blocked.mode,
 blocker.seq || ',' || blocked.pid::text
 FROM r blocker
 JOIN t blocked
 ON (blocked.blocker_pid = blocker.pid)
 WHERE blocker.depth < 1000
 )
SELECT * FROM r
 ORDER BY seq;
====== TIPS ======
* リアルタイムモニタリングをより便利にするために、階層的なレポートを生成するビューを作成することができ、以下に示すようにレポートをを更新するためのwatchコマンドが利用できます。
* 以下のビューはブロッキングツリーのセッションについての便利な情報も追加しています。
CREATE OR REPLACE VIEW blocking_tree AS
WITH RECURSIVE
 lock AS (
 SELECT pid,
 virtualtransaction,
 granted,
 mode,
 (locktype,
 CASE locktype
 WHEN 'relation' THEN concat_ws(';', 'db:'||datname, 'rel:'||relation::regclass::text)
 WHEN 'extend' THEN concat_ws(';', 'db:'||datname, 'rel:'||relation::regclass::text)
 WHEN 'page' THEN concat_ws(';', 'db:'||datname, 'rel:'||relation::regclass::text, 'page#'||page::text)
 WHEN 'tuple' THEN concat_ws(';', 'db:'||datname, 'rel:'||relation::regclass::text, 'page#'||page::text, 'tuple#'||tuple::text)
 WHEN 'transactionid' THEN transactionid::text
 WHEN 'virtualxid' THEN virtualxid::text
 WHEN 'object' THEN concat_ws(';', 'class:'||classid::regclass::text, 'objid:'||objid, 'col#'||objsubid)
 ELSE concat('db:'||datname) -- userlock and advisory
 END::text) AS target
 FROM pg_catalog.pg_locks
 LEFT JOIN pg_catalog.pg_database ON (pg_database.oid = pg_locks.database)
 )
, waiting_lock AS (
 SELECT
 blocker.pid AS blocker_pid,
 blocked.pid AS pid,
 concat(blocked.mode,blocked.target) AS lock_target
 FROM lock blocker
 JOIN lock blocked
 ON ( NOT blocked.granted
 AND blocker.granted
 AND blocked.pid != blocker.pid
 AND blocked.target IS NOT DISTINCT FROM blocker.target)
 JOIN lock_composite c ON (c.requested = blocked.mode AND c.current = blocker.mode)
 )
, acquired_lock AS (
 WITH waiting AS (
 SELECT lock_target, count(lock_target) AS wait_count FROM waiting_lock GROUP BY lock_target
 )
 SELECT
 pid,
 array_agg(concat(mode,target,' + '||wait_count) ORDER BY wait_count DESC NULLS LAST) AS locks_acquired
 FROM lock
 LEFT JOIN waiting ON waiting.lock_target = concat(mode,target)
 WHERE granted
 GROUP BY pid
 )
, blocking_lock AS (
 SELECT
 ARRAY[date_part('epoch', query_start)::int, pid] AS seq,
 0::int AS depth,
 -1::int AS blocker_pid,
 pid,
 concat('Connect: ',usename,' ',datname,' ',coalesce(host(client_addr)||':'||client_port, 'local')
 , E'\nSQL: ',replace(substr(coalesce(query,'N/A'), 1, 60), E'\n', ' ')
 , E'\nAcquired:\n '
 , array_to_string(locks_acquired[1:5] ||
 CASE WHEN array_upper(locks_acquired,1) > 5
 THEN '... '||(array_upper(locks_acquired,1) - 5)::text||' more ...'
 END,
 E'\n ')
 ) AS lock_info,
 concat(to_char(query_start, CASE WHEN age(query_start) > '24h' THEN 'Day DD Mon' ELSE 'HH24:MI:SS' END),E' started\n'
 ,CASE WHEN waiting THEN 'waiting' ELSE state END,E'\n'
 ,date_trunc('second',age(now(),query_start)),' ago'
 ) AS lock_state
 FROM acquired_lock blocker
 LEFT JOIN pg_stat_activity act USING (pid)
 WHERE EXISTS -- The root of the tree should blocks one or more sessions.
 (SELECT 'x' FROM waiting_lock blocked WHERE blocked.blocker_pid = blocker.pid)
 AND NOT EXISTS -- The root of the tree should not be a blocked session.
 (SELECT 'x' FROM waiting_lock blocked WHERE blocked.pid = blocker.pid)
UNION ALL
 SELECT
 blocker.seq || blocked.pid,
 blocker.depth + 1,
 blocker.pid,
 blocked.pid,
 concat('Connect: ',usename,' ',datname,' ',coalesce(host(client_addr)||':'||client_port, 'local')
 , E'\nSQL: ',replace(substr(coalesce(query,'N/A'), 1, 60), E'\n', ' ')
 , E'\nWaiting: ',blocked.lock_target
 , CASE WHEN locks_acquired IS NOT NULL
 THEN E'\nAcquired:\n ' ||
 array_to_string(locks_acquired[1:5] ||
 CASE WHEN array_upper(locks_acquired,1) > 5
 THEN '... '||(array_upper(locks_acquired,1) - 5)::text||' more ...'
 END,
 E'\n ')
 END
 ) AS lock_info,
 concat(to_char(query_start, CASE WHEN age(query_start) > '24h' THEN 'Day DD Mon' ELSE 'HH24:MI:SS' END),E' started\n'
 ,CASE WHEN waiting THEN 'waiting' ELSE state END,E'\n'
 ,date_trunc('second',age(now(),query_start)),' ago'
 ) AS lock_state
 FROM blocking_lock blocker
 JOIN waiting_lock blocked
 ON (blocked.blocker_pid = blocker.pid)
 LEFT JOIN pg_stat_activity act ON (act.pid = blocked.pid)
 LEFT JOIN acquired_lock acq ON (acq.pid = blocked.pid)
 WHERE blocker.depth < 5
 )
SELECT concat(lpad('=> ', 4*depth, ' '),pid::text) AS "PID"
, lock_info AS "Lock Info"
, lock_state AS "State"
FROM blocking_lock
ORDER BY seq;
Category:Administration
Category:Performance
Category:Monitoring
Category:Japanese