PostgreSQL 블로킹 락 확인 (누가 누굴 막는지)

진단 SQL

SELECT
  blocked_locks.pid AS blocked_pid,
  blocked_activity.usename AS blocked_user,
  blocked_activity.query AS blocked_query,
  blocked_locks.mode AS blocked_mode,
  blocking_locks.pid AS blocking_pid,
  blocking_activity.usename AS blocking_user,
  blocking_activity.query AS blocking_query,
  blocking_locks.mode AS blocking_mode,
  round(extract(epoch FROM (now() - blocked_locks.waitstart))::numeric, 1) AS wait_seconds
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
ORDER BY wait_seconds DESC NULLS LAST;

SQL 복사

복사한 SQL 을 psql --csv -f-(권장 — 파싱이 가장 견고합니다)로 실행하거나, psql 안에서 그대로 실행한 기본 출력도 지원합니다. 출력 전체를 아래에 붙여넣으세요.

필요 권한/확장 — 확장 불필요. pg_stat_activity 의 타 세션 query·usename 은 슈퍼유저 또는 pg_read_all_stats(pg_monitor 포함) 권한이 없으면 비어 보일 수 있음.

대기 중(granted=false)인 락만 나옵니다. 행 하나가 blocked_pid 하나가 blocking_pid 하나에게 막힌 간선 하나 — 체인은 blocking_pid 를 따라가며 재구성합니다.