PostgreSQL 쿼리 무한 로딩 idle in transaction 해결

한줄 요약: pid 조회하고 select pg_cancel_backend(pid);로 세션 취소

 

 

 

DROP INDEX 인덱스명; 을 실행했더니 무한 로딩이 걸린다.

 

Oracle에서 update문을 실행하다가 고생한 경험을 떠올렸다.

네트워크 연결이 불안정한 작업 환경에서 운영 DB에 커밋을 하다가 무한 대기가 걸려서 아주 많이 당황했었고

lock이 원인이란 걸 파악한 뒤 DBA에게 session kill을 요청하여 해결했었다.

 

그러니 우선 현재 실행 중인 프로세스의 lock 정보를 보여주는 pg_locks view를 조회해보자.

 

 

Lock 정보 조회

select *
from pg_locks l, pg_stat_all_tables t
where l.relation = t.relid
order by relation ASC;

조회해보니 뭔가 나오긴 하지만 쿼리에 대한 정보가 필요하다.

실행 중인 쿼리 정보를 보여주는 pg_stat_activity view를 확인해보자.

 

 

실행 중인 쿼리 조회

Lock 상태인 쿼리 조회

select *
from pg_stat_activity
where datname = 'DB명'
and  wait_event_type = 'Lock'
order by query_start desc;

 

결과값이 없다. Lock이 아닌가?

 

전체 쿼리 조회

select *
from pg_stat_activity
where datname = 'DB명'
order by query_start desc;

 

조회해보니 state가 idle in transaction인 쿼리가 눈에 띈다.

 

idle in transaction은 트랜잭션을 시작한 뒤 작업을 실행하지 않고 대기 중인 상태이다.

대기 시간이 길어질 경우 자원이 낭비되므로 종료가 필요하다.

조회한 pid를 가지고 해당 프로세스를 취소/종료해주자.

 

 

프로세스 취소/종료

select pg_cancel_backend(pid);

 

위 쿼리로 세션이 취소되지 않을 경우 상위 프로세스까지 종료해주는 아래 쿼리를 실행하면 된다.

 

select pg_terminate_backend(pid)
from pg_stat_activity
where state = 'idle in transaction'
and current_timestamp - query_start > '2 min';  // 시간 조건 조절

 

idle in transaction 자동 종료 설정

9.6 버전 이상에서 일정 시간이 지나면 자동으로 종료를 해주는 idle_in_transaction_session_timeout 설정이 가능하다.