객체 및 lock mode별 락 대기 상세 확인(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)에 대해 대상 객체(테이블, transaction id 등), 요청 모드, 블로킹 PID와 그 모드, blocker가 실제로 락을 보유하는지 여부, 대략적인 대기 시간을 보여 준다.

사용 시점

어떤 테이블과 lock mode 충돌이 블로킹을 일으키는지 파악하기 위해. 예: AccessExclusiveLock을 기다리는 DDL, transactionid 대기로 나타나는 행 락.

주의

매칭에 pg_blocking_pids()를 사용하므로 실제로 충돌하는 요청만 나열된다. blocker_holds = false는 blocker가 아직 락을 보유하지 않았지만 충돌하는 모드로 앞에 대기 중임을 뜻한다(lock queue). locktype = 'transactionid'는 행 수준 충돌이다. 대기자는 해당 행을 수정한 트랜잭션이 끝날 때까지 기다리며, 행 자체는 pg_locks에 보이지 않는다. 대형 시스템에서 pg_locks는 수천 행일 수 있으므로 자주 실행하지 않는다. waiting_for는 state_change 기준의 근사값이다.