PostgreSQL 16~18 락 대기 진단: pg_locks·pg_stat_activity로 차단 세션 찾는 순서

PostgreSQL에서 쿼리나 DDL이 오래 멈추면 먼저 대기 세션의 pid를 찾고 pg_blocking_pids(pid)로 차단 PID를 확인한다. 그다음 pg_stat_activity에서 차단 세션의 트랜잭션 시작 시각·상태·실행문을 확인한 뒤, 업무 영향이 확인된 경우에만 취소 또는 종료를 검토한다.

이 순서는 PostgreSQL 16~18의 일반적인 운영 진단에 적용한다. pg_locks만 직접 조인해 차단 관계를 추측하는 방법보다, 대기열까지 고려하는 pg_blocking_pids()를 출발점으로 삼는 편이 안전하다. 준비 트랜잭션은 차단 PID가 0으로 보일 수 있고, 병렬 쿼리는 같은 PID가 반복될 수 있다는 예외도 함께 봐야 한다.

적용 범위와 첫 판단

락 대기를 읽는 방식

PostgreSQL은 MVCC를 사용하므로 단순 조회가 언제나 쓰기를 막는 것은 아니다. 다만 행을 수정하거나 SELECT ... FOR UPDATE로 잠그는 세션, 그리고 일부 ALTER TABLE·VACUUM FULL 같은 DDL은 충돌하는 잠금을 요구한다. 잠금은 보통 트랜잭션이 끝날 때까지 유지되므로, 짧은 UPDATE라도 커밋하지 않은 채 연결을 유지하면 뒤의 작업이 길게 기다릴 수 있다.

pg_locks는 현재 잠금 관리자에 있는 요청을 보여 주지만, 차단 관계에는 이미 대기열 앞에 서 있는 세션도 영향을 준다. pg_blocking_pids()는 충돌 잠금을 보유한 세션뿐 아니라 대기열상 앞선 소프트 차단자도 반환한다. 그래서 운영 화면에서 ‘막힌 세션’을 찾을 때는 이 함수를 먼저 쓰고, 객체·잠금 모드를 설명해야 할 때 pg_locks를 보조로 붙인다.

1. 대기 세션을 좁힌다

SELECT
    a.pid,
    a.usename,
    a.application_name,
    a.client_addr,
    a.backend_type,
    a.state,
    a.wait_event_type,
    a.wait_event,
    a.xact_start,
    a.query_start,
    now() - a.query_start AS query_age,
    a.query
FROM pg_stat_activity AS a
WHERE a.backend_type = 'client backend'
  AND a.wait_event_type = 'Lock'
ORDER BY a.query_start ASC;

state = 'active'와 wait_event_type = 'Lock'이 함께 보인다고 해서 실행문 전체가 실패한 것은 아니다. 단지 현재 잠금 획득을 기다린다는 뜻이다. 여러 행이 있으면 먼저 업무 영향이 큰 요청과 가장 오래 기다린 요청을 분리해 기록한다. 모든 대기를 한꺼번에 종료하면 차단 사슬의 원인을 잃는다.

2. 대기자와 차단자를 연결한다

SELECT
    waiter.pid AS waiting_pid,
    waiter.usename AS waiting_user,
    waiter.application_name AS waiting_app,
    waiter.query_start AS waiting_since,
    blocker.pid AS blocking_pid,
    blocker.usename AS blocking_user,
    blocker.application_name AS blocking_app,
    blocker.state AS blocking_state,
    blocker.xact_start AS blocking_xact_start,
    blocker.query_start AS blocking_query_start,
    blocker.query AS blocking_query
FROM pg_stat_activity AS waiter
CROSS JOIN LATERAL unnest(pg_blocking_pids(waiter.pid)) AS b(blocking_pid)
LEFT JOIN pg_stat_activity AS blocker
  ON blocker.pid = b.blocking_pid
WHERE waiter.backend_type = 'client backend'
  AND waiter.wait_event_type = 'Lock'
ORDER BY waiter.query_start ASC, blocker.xact_start ASC NULLS LAST;

차단 세션이 idle in transaction이면 마지막 문장이 끝났더라도 트랜잭션은 끝나지 않았을 가능성이 있다. 반대로 active라면 큰 UPDATE나 DDL이 정상 진행 중일 수 있다. 두 상태를 같은 위험도로 취급하지 말고, xact_start와 애플리케이션 이름, 연결 원격지, 배치 창을 함께 확인한다.

3. 어떤 객체와 잠금 모드가 얽혔는지 확인한다

SELECT
    l.pid,
    n.nspname AS schema_name,
    c.relname AS relation_name,
    l.locktype,
    l.mode,
    l.granted,
    a.usename,
    a.application_name,
    a.state,
    a.xact_start
FROM pg_locks AS l
LEFT JOIN pg_class AS c
  ON c.oid = l.relation
LEFT JOIN pg_namespace AS n
  ON n.oid = c.relnamespace
LEFT JOIN pg_stat_activity AS a
  ON a.pid = l.pid
WHERE l.pid IN (12345, 12346)
ORDER BY l.pid, l.granted, n.nspname, c.relname, l.mode;

relation이 NULL인 행도 오류가 아니다. transactionid, virtualxid, advisory 같은 잠금은 테이블 이름으로 바로 풀리지 않는다. 특히 행 수준 충돌은 pg_locks에서 특정 행 번호로 보이지 않고 트랜잭션 ID 대기로 나타날 수 있다. 이럴 때는 객체명 하나만 보고 원인을 단정하지 말고 차단 세션의 트랜잭션과 SQL을 다시 확인한다.

작업 전 확인과 조치 기준

예를 들어 배포 직전에 ALTER TABLE이 기다린다면, 차단자를 종료하기 전에 배포를 늦추는 편이 더 안전할 수 있다. 반대로 앱 연결이 idle in transaction으로 수십 분째 남아 있고 업무 소유자가 확인됐다면, 해당 연결의 현재 작업을 취소하거나 종료하는 판단이 가능하다. Oracle에서 차단 세션을 확인하는 순서와 마찬가지로, DB 제품마다 뷰는 달라도 ‘증거 확인 뒤 최소 조치’라는 운영 원칙은 같다.

4. 종료보다 취소를 먼저 검토한다

SELECT pg_cancel_backend(12345);

SELECT pg_terminate_backend(12345, 5000);

조치 뒤에는 같은 기준으로 다시 본다

SELECT
    a.pid,
    a.state,
    a.wait_event_type,
    a.wait_event,
    now() - a.query_start AS query_age,
    pg_blocking_pids(a.pid) AS blocking_pids
FROM pg_stat_activity AS a
WHERE a.backend_type = 'client backend'
  AND a.wait_event_type = 'Lock'
ORDER BY a.query_start ASC;

차단자가 사라졌는데도 요청이 느리다면 락 문제는 해소됐고 다른 병목을 찾아야 한다. 오래 열린 트랜잭션이 반복된다면 커넥션 풀의 트랜잭션 경계, 예외 처리 뒤 ROLLBACK 누락, 배치 단위와 타임아웃을 점검한다. MySQL의 metadata lock처럼 DDL이 멈추는 경우도 별도 진단이 필요하며, MySQL 8.4 metadata lock 대기 진단에서 제품별 차이를 확인할 수 있다.

흔한 실수

• 대기 세션부터 종료하기: 기다리는 쪽을 끊어도 원래 차단자가 남아 다음 요청을 다시 막는다.
• PID만 보고 조치하기: PID는 재사용될 수 있다. 실행 직전에 pg_stat_activity에서 사용자·앱·시작 시각을 다시 확인한다.
• pg_locks의 relation NULL을 무시하기: transactionid·virtualxid·advisory 잠금도 경합 원인이 된다.
• true를 해결 완료로 해석하기: 취소·종료 함수의 성공 반환 뒤에도 대기 목록과 애플리케이션 상태를 재조회해야 한다.
• 원인 없는 재시도: 잠금 순서와 트랜잭션 경계를 고치지 않으면 같은 대기가 다시 발생한다.

마무리

PostgreSQL 락 장애에서는 ‘누가 기다리는가’보다 ‘누가 왜 막고 있는가’를 먼저 확정해야 한다. pg_blocking_pids()로 사슬을 만들고, pg_stat_activity와 pg_locks로 업무·트랜잭션·객체를 확인한 뒤 최소 조치를 택하면 불필요한 세션 종료와 데이터 작업 중단을 줄일 수 있다.

공식 출처

• PostgreSQL 18 문서: Viewing Locks
• PostgreSQL 18 문서: pg_blocking_pids()
• PostgreSQL 18 문서: Explicit Locking
• PostgreSQL 18 문서: pg_cancel_backend()와 pg_terminate_backend()