[PostgreSQL - DBA] Lock 걸린 테이블 조회 및 Kill

PostgreSQL DDL을 갈기다 보면 가끔 AccessShareLock 때문에 DDL이 안된다.

AccessShareLock은 아래 참고.

AccessShareLock 이란?

Oracle에서는 SELECT 동안 락을 잡지 않는다. 다만 SELECT 되는 동안 해당 테이블의 구조(컬럼변경, DDL 등) 를 바꾸지 못하도록 해당 테이블에 TM LOCK 을 거는데 딱히 성능에 지장을 주는 Lock이 아니다. 

PostgreSQL에서 TM LOCK에 해당하는 것이 AccessShareLock 이다.

성능에 지장을 주는 Lock이 아니기 때문에 크게 상관 안써도 되지만, 테스트 과정에서 DDL변경이 Block되기 때문에 Lock을 해제해줘야 한다.

 

따라서 지금과 같이 프로젝트를 진행 중에 DDL을 날릴 일이 많다면 저놈의 AccesShareLock이 허구한날 걸린다.

그래서 가끔 DDL이 안된다면, 아래  순서대로 진행한다.

 

1. 쿼리를 조회해서 테이블이 Lock 상태인지 확인

select t.relname
     , l.locktype
     , page
     , virtualtransaction
     , pid
     , mode
     , granted
  from pg_locks l,
	 pg_stat_all_tables t
 where l.relation = t.relid
   and relname = :table_name
  ;

 

2. Lock 상태이면서 kill 해도 되는 거면 아래 쿼리로 바로 삭제(파라미터는 위의 쿼리에서 조회된 pid)

select pg_cancel_backend(:PID);

! 2-1. 위의 방법으로 해결이 안될경우, PID에 연관된 상위Query들이 존재한다고 볼 수 있다.

 

3. 해당 쿼리가 뭔지 좀더 보고 싶다면 아래 쿼리로 조회. 그리고 pg_terminate_backend()로 kill 할 수 있다.

-- 실행중인 Query 상태 조회
select * 
  from pg_stat_activity
 where pid = :PID;

-- Lock 과 관련된 상위 Query까지 해제
select pg_terminate_backend(pid) 
  from pg_stat_activity 
 where pid = :PID;

* 위의 pg_cancel_backend 경우 해당 PID만 중지시키고, pg_terminate_backend의 경우 PID와 연관된 모든 상위 Query도 같이 중지 시킨다.