Fullmoon System

PostgreSQL 잠금 장애: 차단 세션과 긴 트랜잭션을 구분하는 방법

AI Editter

평소 10ms에 끝나던 UPDATE가 갑자기 수십 초를 기다린다면 인덱스보다 잠금 대기를 먼저 볼 상황일 수 있습니다. 기다리는 쿼리를 종료해도 잠금을 쥔 트랜잭션이 남으면 다음 요청이 같은 곳에서 기다립니다.

장애 중에는 대기 세션과 차단 세션을 구분하고, 차단 세션이 어느 앱·업무에 속하는지 확인한 뒤 조치해야 합니다. 긴 시간 실행됐다는 이유만으로 모든 세션을 일괄 종료하지 않습니다.

현재 상태를 조회하는 첫 쿼리

다음은 조회용 예제입니다. 다른 사용자의 세션 상세를 보려면 적절한 모니터링 권한이 필요합니다. query에는 고객 값이나 비밀정보가 포함될 수 있으므로 공유 전에 가립니다.

SELECT pid, usename, application_name, state,
       clock_timestamp() - xact_start AS transaction_age,
       clock_timestamp() - query_start AS query_age,
       wait_event_type, wait_event,
       pg_blocking_pids(pid) AS blocking_pids,
       left(query, 300) AS query_sample
FROM pg_stat_activity
WHERE datname = current_database()
  AND pid <> pg_backend_pid()
ORDER BY xact_start NULLS LAST;

transaction_age는 트랜잭션이 열린 시간, query_age는 현재 또는 마지막 쿼리의 시작 이후 시간입니다. idle 세션에서는 마지막 쿼리 문자열이 현재 실행 중이라는 뜻이 아닙니다.

관찰해석의 출발점
wait_event_type=Lock잠금 대기와 차단 주체 조사
active, I/O 관련 대기저장소·쿼리 작업량 조사
idle in transaction열린 트랜잭션이 정리되지 않았는지
오래된 xact_start앱의 트랜잭션 범위와 외부 대기 조사
blocking_pids 여러 개여러 차단 관계 또는 대기열 확인

기다리는 세션과 막는 세션을 한 표로

SELECT w.pid AS waiting_pid,
       w.application_name AS waiting_app,
       b.pid AS blocking_pid,
       b.application_name AS blocking_app,
       b.state AS blocking_state,
       clock_timestamp() - b.xact_start AS blocking_xact_age,
       left(w.query, 200) AS waiting_query,
       left(b.query, 200) AS blocking_query
FROM pg_stat_activity w
CROSS JOIN LATERAL unnest(pg_blocking_pids(w.pid)) AS x(pid)
JOIN pg_stat_activity b ON b.pid = x.pid
ORDER BY b.xact_start NULLS LAST, w.pid;

이 조회는 일반적인 backend 간 차단 관계를 보기 위한 예제입니다. prepared transaction이나 병렬 실행 등에서는 추가 조사가 필요할 수 있습니다. PostgreSQL 시스템 정보 함수에서 pg_blocking_pids의 의미와 비용을 확인합니다. 매우 짧은 간격으로 무제한 호출하지 않습니다.

상위 차단 세션 자체가 다른 세션을 기다리는지도 봅니다. 연결 고리를 따라가야 여러 요청을 막는 시작점을 찾을 수 있습니다.

시험 DB에서 두 세션으로 재현

격리된 시험 DB에만 다음 테이블을 만듭니다.

CREATE TABLE lock_demo (
  id integer PRIMARY KEY,
  value integer NOT NULL
);
INSERT INTO lock_demo VALUES (1, 0);

세션 A에서 BEGIN 후 UPDATE lock_demo SET value = value + 1 WHERE id = 1;을 실행하고 트랜잭션을 열어 둡니다. 세션 B에서는 BEGIN, SET LOCAL lock_timeout = '3s';를 실행한 뒤 같은 UPDATE를 실행합니다. B는 잠금을 기다리다 설정한 시간 제한에 도달하면 오류가 날 수 있습니다.

세션 A를 ROLLBACK한 뒤 B도 ROLLBACK해 실패한 트랜잭션을 정리합니다. 테스트 결과는 원래 값 0으로 돌아가야 합니다. 오류 후 트랜잭션을 정리하지 않는 앱은 별도 장애를 만들 수 있습니다.

취소와 연결 종료를 구분

pg_cancel_backend는 실행 중인 쿼리의 취소를 요청하고, pg_terminate_backend는 세션 종료를 요청합니다. 권한과 대상 확인이 필요하며 효과를 같게 취급하지 않습니다. 특히 idle in transaction은 실행 중인 쿼리가 없어서 취소만으로 문제를 해결하기 어렵습니다.

앱 담당자와 어떤 업무가 롤백되는지 확인하고 가능한 경우 정상적인 COMMIT·ROLLBACK 경로로 정리합니다. 연결 종료가 필요하다면 PID만 보지 말고 사용자·앱·시작 시각을 재확인합니다. 이전에 본 PID가 이후 다른 상태로 바뀔 수 있습니다.

OS에서 PostgreSQL 프로세스에 kill -9를 보내는 식의 대응은 일반적인 해결 절차로 삼지 않습니다. 서버 신호 설명은 비정상 종료가 서버 전체 복구로 이어질 수 있음을 설명합니다.

재발을 줄이는 앱 변경

트랜잭션 안에서 외부 API 응답을 기다리지 않는지, 커넥션을 풀에 반환하기 전에 종료하는지, 여러 테이블을 일관된 순서로 갱신하는지 확인합니다. 무조건 재시도하면 차단 대기열을 더 키울 수 있으므로 대기 시간·재시도 간격·최대 횟수를 정합니다.

lock_timeout과 statement_timeout은 역할이 다릅니다. 전자는 잠금 획득 대기, 후자는 문장 실행 시간을 제한하는 데 사용합니다. 모든 업무에 동일한 짧은 전역 값을 주기보다 요청 유형별로 시험합니다. 조치 후에는 대기 건수뿐 아니라 실패한 업무가 중복 없이 재처리됐는지 확인합니다.

관련 절차: PostgreSQL 느린 쿼리 분석: EXPLAIN ANALYZE를 읽는 순서