세션 종료와 영향

세션 취소, 종료의 권한, timeout, 반환값을 구분하며 실제 종료, 롤백, 후속 트랜잭션 결과는 기재 없음

pg_terminate_backendpg_cancel_backend블로킹 PID종료 대상트랜잭션

#핵심 요약

잠금 장애에서 DBA가 하는 일은 찾기 → 원인 SQL 보기 → 끊기이며, 찾기와 원인 확인은 잠금과 대기 세션에서 다룸

수업은 블로킹 배열에서 홀더 PID를 뽑아 select pg_terminate_backend(PID); 문장을 문자열로 만든 뒤, 그 결과를 복사해 실행하는 방식을 씀

문자열을 만드는 SELECT는 아무것도 끊지 않고, 만들어진 문장을 다시 실행해야 세션이 끊김

세션을 끊으면 그 세션의 미커밋 트랜잭션은 롤백되고 잠금이 풀려 기다리던 세션이 진행함

#개념과 동작 원리

함수	동작
`pg_cancel_backend(pid)`	그 세션이 지금 실행 중인 쿼리만 취소, 접속은 유지
`pg_terminate_backend(pid)`	그 세션의 접속을 끊음, 미커밋 트랜잭션은 롤백

idle in transaction 홀더는 실행 중인 쿼리가 없어 pg_cancel_backend로는 잠금이 풀리지 않으므로, 홀더 본인이 COMMIT, ROLLBACK하거나 세션을 끊어야 함
두 함수는 슈퍼유저이거나 대상 세션과 같은 롤, 또는 pg_signal_backend 롤 권한이 있어야 쓸 수 있음

#주요 용어와 구성 요소

구문	의미
시그널 전송	timeout 생략, 0인 pg_terminate_backend의 true는 시그널을 보냈다는 뜻임
종료 확인	timeout을 양수 밀리초로 지정하면 실제 종료 또는 시간 초과까지 기다리는 동작임

#필수 명령어 또는 SQL

항목	실행 조건
작업 대상	현재 블로킹 홀더 PID와 해당 세션, 트랜잭션, 종료 뒤 블로킹 배열
필요 권한	대상 롤 구성원, pg_signal_backend 또는 슈퍼유저 조건을 적용하며 슈퍼유저 세션은 슈퍼유저만 종료 가능
바꿀 입력값	<HOLDER_PID>는 사용 직전에 다시 확인한 PID
결과 해석	명령 문자열 생성은 종료가 아님 종료 시그널 성공과 실제 연결 종료, 롤백 완료를 구분하며 실습 결과는 기재 없음

#끊을 명령 만들기

psql 창 C (postgres)조회만 함

select distinct 'select pg_terminate_backend(' || b.pid || ');' as kill_cmd
  from pg_stat_activity a,
       unnest(pg_blocking_pids(a.pid)) as b(pid);

결과 해석:

실습 기록: select pg_terminate_backend(32578); 한 행이 나옴

distinct로 같은 홀더가 여러 웨이터를 막을 때 문장이 여러 번 나오지 않게 함

#끊기 전 확인과 실행

psql 창 C (postgres)조회만 함

select pid, usename, application_name, client_addr, state, xact_start, left(query, 60)
  from pg_stat_activity
 where pid = <HOLDER_PID>;

psql 창 C (postgres)인스턴스 또는 서비스 중단이 필요함

select pg_terminate_backend(<HOLDER_PID>);

결과 해석:

끊기 전 홀더의 사용자, 프로그램, 접속 주소, 트랜잭션 시작 시각으로 어느 업무인지 확인하고, 가능하면 담당자에게 COMMIT, ROLLBACK을 먼저 요청함

수업 실습은 이 문장을 실행하지 않고 창 A에서 rollback으로 잠금을 푸는 것으로 마무리했으며, 그 단계의 출력은 기재 없음

pg_terminate_backend는 성공하면 t를 돌려주며, 끊긴 창 A는 다음 명령을 칠 때 연결이 끊겼다는 메시지를 받음

홀더가 사라지면 창 B의 UPDATE가 진행되며, 창 A의 변경은 롤백됐으므로 cnt는 창 B의 변경만 반영된 값이 됨

#다시 확인

psql 창 C (postgres)조회만 함

select w.pid as waiter, pg_blocking_pids(w.pid) as blocked_by
  from pg_stat_activity w
 where cardinality(pg_blocking_pids(w.pid)) > 0;

결과 해석:

결과가 없으면 남은 블로킹 관계가 없는 것이며, 웨이터가 여럿 줄을 섰던 경우에는 다음 홀더가 새로 나올 수 있어 다시 확인함

#구문과 옵션 설명

pg_cancel_backend는 실행 중인 쿼리를 취소하고 pg_terminate_backend는 연결된 세션을 종료함
대상 롤의 구성원이거나 pg_signal_backend 권한이 있어도 슈퍼유저 세션을 취소, 종료하려면 슈퍼유저여야 함
timeout이 양수이면 종료 확인 시 true, 시간 초과 시 경고와 false가 반환됨

#사용 조건과 주의사항

세션 종료는 진행 중인 업무 트랜잭션을 롤백시키므로 조회 결과에 나왔다는 이유만으로 끊지 않음

대용량 변경을 하던 세션을 끊으면 롤백에도 시간이 걸릴 수 있음

같은 상황이 반복되면 애플리케이션의 트랜잭션 관리와 idle_in_transaction_session_timeout 설정을 함께 검토함