PostgreSQL DB Lock 발생시 조치 방법

반응형

세션을 모니터링 하다가  Lock 세션이 보인다 그러면 아래 쿼리로 확인 후  세션을 중지 시키면된다

 

 

------------------------------------------------------------------------------------------------------------------------
--1)  BLOCK & WAIT LOCK 확인 
------------------------------------------------------------------------------------------------------------------------

SELECT
    blocking_activity.usename||' ('||blocking_activity.application_name||')' AS blocking_user
    ,blocked_activity.usename||' ('||blocked_activity.application_name||')'  AS waiting_user
    ,blocking_locks.pid     AS blocking_pid
    ,waiting_locks.pid     AS waiting_pid
    ,waiting_locks.locktype AS lock_type  -- locktype wait쪽.. waiting_locks.
    ,blocking_locks.mode AS holding_mode  -- hold mode --blocking_locks.
    ,waiting_locks.mode AS request_mode  -- request mode -- waiting_locks.
    ,blocked_activity.query    AS waiting_sql
    ,blocking_activity.query   AS blocking_sql
FROM  pg_catalog.pg_locks         waiting_locks
  JOIN pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid = waiting_locks.pid
  JOIN pg_catalog.pg_locks         blocking_locks    ON blocking_locks.locktype = waiting_locks.locktype
     AND blocking_locks.DATABASE IS NOT DISTINCT FROM waiting_locks.DATABASE
     AND blocking_locks.relation IS NOT DISTINCT FROM waiting_locks.relation
     AND blocking_locks.page IS NOT DISTINCT FROM waiting_locks.page
     AND blocking_locks.tuple IS NOT DISTINCT FROM waiting_locks.tuple
     AND blocking_locks.virtualxid IS NOT DISTINCT FROM waiting_locks.virtualxid
     AND blocking_locks.transactionid IS NOT DISTINCT FROM waiting_locks.transactionid
     AND blocking_locks.classid IS NOT DISTINCT FROM waiting_locks.classid
     AND blocking_locks.objid IS NOT DISTINCT FROM waiting_locks.objid
     AND blocking_locks.objsubid IS NOT DISTINCT FROM waiting_locks.objsubid
     AND blocking_locks.pid != waiting_locks.pid
  JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT waiting_locks.GRANTED;

 
------------------------------------------------------------------------------------------------------------------------
--2) 블럭킹중인 세션 죽이는 스크립트 추출 select pg_terminate_backend(<pid>); */
------------------------------------------------------------------------------------------------------------------------

SELECT DISTINCT 'SELECT PG_TERMINATE_BACKEND('||blocking_locks.pid||');' KILL_SESS
FROM  pg_catalog.pg_locks         waiting_locks
  JOIN pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid = waiting_locks.pid
  JOIN pg_catalog.pg_locks         blocking_locks    ON blocking_locks.locktype = waiting_locks.locktype
     AND blocking_locks.DATABASE IS NOT DISTINCT FROM waiting_locks.DATABASE
     AND blocking_locks.relation IS NOT DISTINCT FROM waiting_locks.relation
     AND blocking_locks.page IS NOT DISTINCT FROM waiting_locks.page
     AND blocking_locks.tuple IS NOT DISTINCT FROM waiting_locks.tuple
     AND blocking_locks.virtualxid IS NOT DISTINCT FROM waiting_locks.virtualxid
     AND blocking_locks.transactionid IS NOT DISTINCT FROM waiting_locks.transactionid
     AND blocking_locks.classid IS NOT DISTINCT FROM waiting_locks.classid
     AND blocking_locks.objid IS NOT DISTINCT FROM waiting_locks.objid
     AND blocking_locks.objsubid IS NOT DISTINCT FROM waiting_locks.objsubid
     AND blocking_locks.pid != waiting_locks.pid
  JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT waiting_locks.GRANTED
AND blocking_locks.mode like '%Exclusive%'
;

------------------------------------------------------------------------------------------------------------------------ 
--3) 2)에서 추출된 쿼리 수행으로 blocking세션 kill시키기
------------------------------------------------------------------------------------------------------------------------


select pg_terminate_backend(xxxx);