(PostgreSQL) Lock 경합 상태 확인하기

 

Block된 SQL 프로세스 조회 SQL

SELECT blocked_locks.pid         AS blocked_pid, 
       blocked_activity.usename  AS blocked_user, 
       blocking_locks.pid        AS blocking_pid, 
       blocking_activity.usename AS blocking_user, 
       blocked_activity.query    AS blocked_statement, 
       blocking_activity.query   AS current_statement_in_blocking_process 
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;

 

조회 결과

 blocked_pid | blocking_pid |           blocked_statement           | current_statement_in_blocking_process 
-------------+--------------+----------------------------------------+----------------------------------------
       15317 |        15206 | UPDATE test SET y = 10 WHERE x = '2'; | UPDATE test SET y = 5 WHERE x = '2';
       15242 |        15206 | ALTER TABLE test ADD COLUMN a int;    | UPDATE test SET y = 5 WHERE x = '2';
       15242 |        15317 | ALTER TABLE test ADD COLUMN a int;    | UPDATE test SET y = 10 WHERE x = '2';
(3 rows)

 

Lock 전체 목록 조회 SQL

SELECT relation :: regclass, mode, granted, pid, * FROM pg_locks; 

 

SELECT a.datname,
         l.mode,
         a.query,
         a.query_start,
         age(now(), a.query_start) AS "age" ,
         a.pid
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.pid
where a.query_start >= '2019-05-29 06:00:00'
ORDER BY a.query_start desc;

 

참고

PostgreSQL WIKI - Lock Monitoring

PostgreSQL rocks, except when it blocks: Understanding locks