pg_blocking_pids()가 빈 배열을 돌려주는데 쿼리는 여전히 느립니다.

락 대기가 아니라는 뜻이다. pg_stat_activity에서 해당 세션의 wait_event_type을 확인한다. LWLock이면 내부 경합, IO면 디스크 대기, Lock이 아니라 비어 있으면 그냥 CPU를 쓰며 실행 중인 느린 쿼리다. 이 경우는 pg_stat_statements나 EXPLAIN으로 접근해야 한다.

pg_cancel_backend와 pg_terminate_backend 중 무엇을 써야 하나요?

실행 중인 쿼리 하나만 멈추고 연결은 살리려면 pg_cancel_backend를 쓴다. 쿼리가 없는 idle in transaction 세션은 취소할 쿼리가 없어 cancel이 안 먹히므로 pg_terminate_backend로 연결을 끊어 트랜잭션을 롤백시킨다.

lock_timeout을 postgresql.conf에 전역으로 걸어도 되나요?

권하지 않는다. 모든 세션에 일괄 적용되면 정상적인 긴 작업까지 중단된다. 공식 문서도 전역 설정을 비권장한다. 세션, 역할, 또는 트랜잭션 단위로 걸고, RDS에서는 파라미터 그룹 기본값을 0으로 두고 SET LOCAL로 거는 방식이 안전하다.

deadlock_timeout을 줄이면 데드락이 더 빨리 풀리나요?

데드락 감지는 빨라지지만 그만큼 감지 연산이 자주 돌아 CPU 부담이 커진다. 기본값 1초는 대부분의 환경에 적절하다. 데드락이 잦다면 값을 줄이기보다 애플리케이션에서 여러 행을 건드리는 순서를 통일하는 것이 근본 해결이다.

lock_timeout과 statement_timeout을 같이 쓰면 어떻게 되나요?

statement_timeout이 0이 아니고 lock_timeout과 같거나 더 작으면 문장 타임아웃이 먼저 걸려 lock_timeout이 무의미해진다. 락 대기만 따로 끊고 싶으면 lock_timeout을 statement_timeout보다 작게 설정한다.

PostgreSQL 쿼리가 락에 걸려 멈출 때

RDS PostgreSQL을 운영하다 보면 평소 수십 밀리초에 끝나던 UPDATE가 갑자기 몇 분씩 응답이 없는 상황을 만난다. CPU도 멀쩡하고 디스크 I/O도 한가한데 특정 테이블을 건드리는 쿼리만 줄줄이 쌓인다. 예전에 pg_stat_statements로 느린 쿼리 뽑던 글을 쓴 적이 있는데, 그건 느려도 끝나는 쿼리를 잡는 도구다. 아예 멈춰 있는 쿼리는 다른 각도에서 봐야 한다.

결론부터 말하자면, 이런 멈춤은 대부분 락 대기다. 어떤 세션이 먼저 잡은 락과 충돌해 뒤 세션이 큐에서 기다리는 상태다. pg_stat_activity와 pg_blocking_pids()로 누가 누구를 막는지 한 번에 특정하고, 급하면 해당 세션을 끊은 뒤, lock_timeout과 deadlock_timeout으로 재발을 통제한다.

1. 락 대기가 무엇인가

PostgreSQL은 같은 데이터를 동시에 바꾸지 못하도록 락을 건다. 한 트랜잭션이 어떤 행을 UPDATE하면 그 행에 쓰기 락이 걸리고, 같은 행을 바꾸려는 다른 트랜잭션은 앞 트랜잭션이 커밋하거나 롤백할 때까지 기다린다. 이 기다림이 락 대기다.

문제는 대기 중인 세션이 앞 세션을 재촉할 방법이 없다는 점이다. 앞 세션이 BEGIN만 해놓고 커밋을 안 한 채 방치되면, 뒤 세션은 하염없이 큐에 머문다. 애플리케이션 입장에서는 그냥 쿼리가 안 끝나는 것으로 보인다.

락의 현재 상태는 pg_locks 뷰에 들어 있다. 이 뷰의 granted 컬럼이 false인 행이 바로 획득을 기다리는 락이다. 다만 pg_locks만 보면 어떤 PID가 어떤 PID를 막는지 연결하기가 번거롭다. 그래서 9.6부터 들어온 pg_blocking_pids()를 함께 쓴다.

2. 지금 누가 막고 있나 특정하기

pg_blocking_pids(pid)는 지정한 프로세스가 락을 못 얻도록 막고 있는 세션들의 PID 배열을 돌려준다. 막는 세션이 없으면 빈 배열이다. 공식 문서 기준으로 이 함수는 직접 락을 쥔 하드 블록뿐 아니라, 대기 큐에서 앞서 있어 결과적으로 막는 소프트 블록까지 잡아낸다.

먼저 락을 기다리는 세션이 있는지부터 본다.

SELECT pid, usename, state, wait_event_type, wait_event,
       now() - query_start AS waiting_for, query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
ORDER BY query_start;

wait_event_type이 Lock인 행이 락 대기 중인 세션이다. waiting_for가 수십 초, 수 분으로 찍히면 그게 멈춰 있는 쿼리다. 여기서 한 번 헷갈린 적이 있는데, wait_event_type이 LWLock이나 IO로 나오면 그건 테이블 락 충돌이 아니라 내부 경합이나 디스크 대기다. 지금 다루는 블로킹과는 다른 문제다.

대기 세션을 확인했으면, 막는 쪽과 막히는 쪽을 한 줄로 이어 본다.

SELECT blocked.pid          AS blocked_pid,
       blocked.query        AS blocked_query,
       blocking.pid         AS blocking_pid,
       blocking.state       AS blocking_state,
       now() - blocking.state_change AS blocking_idle_for,
       blocking.query       AS blocking_query
FROM pg_stat_activity AS blocked
JOIN pg_stat_activity AS blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE blocked.wait_event_type = 'Lock';

대표적인 출력은 이런 모양이다.

 blocked_pid |        blocked_query        | blocking_pid | blocking_state | blocking_idle_for |      blocking_query
-------------+-----------------------------+--------------+----------------+-------------------+--------------------------
       24871 | UPDATE orders SET status... |        24712 | idle in trans. | 00:03:41          | UPDATE orders SET status...
(1 row)

여기서 핵심은 blocking_state다. 이 값이 idle in transaction이면 범인은 명확하다. 앞 세션이 트랜잭션을 열어 락을 쥔 채로 아무 쿼리도 안 보내고 놀고 있다는 뜻이다. 커밋을 안 하는 애플리케이션 버그이거나, 연결 풀이 트랜잭션을 반납 안 한 경우가 많다. blocking_idle_for가 수 분이면 사람이 심리창을 열어둔 psql일 수도 있다.

반대로 blocking_state가 active이고 blocking_query가 무거운 배치성 쿼리라면, 그건 버그가 아니라 그냥 오래 걸리는 작업이 락을 오래 쥐고 있는 것이다. 이 둘은 대응이 다르다.

3. 급할 때 세션 끊기

장애 대응 중이라 지금 당장 풀어야 한다면 막는 세션을 끊는다. 두 가지 함수가 있고 쓰임이 다르다.

pg_cancel_backend(pid)

해당 세션에서 실행 중인 쿼리 하나만 취소한다. 연결은 유지된다. active 상태로 오래 걸리는 쿼리를 멈추고 싶을 때 먼저 이걸 쓴다.

SELECT pg_cancel_backend(24712);

pg_terminate_backend(pid)

세션 연결 자체를 끊는다. idle in transaction처럼 실행 중인 쿼리가 없어 취소가 안 먹히는 경우에 쓴다. 열려 있던 트랜잭션은 롤백되고 락이 즉시 풀린다.

SELECT pg_terminate_backend(24712);

RDS나 Aurora에서는 슈퍼유저 권한이 없어 다른 사용자 세션을 끊을 때 권한 에러가 날 수 있다. 이때는 접속 계정이 rds_superuser 역할을 갖고 있어야 한다. 끊었는데 permission denied to terminate process가 뜨면 역할부터 확인한다.

SELECT rolname FROM pg_roles WHERE pg_has_role('rds_superuser', 'member');

세션을 끊는 건 증상 완화지 원인 제거가 아니다. 커밋 안 하는 트랜잭션을 그대로 두면 다음 피크에 또 막힌다. 그래서 끊은 뒤에는 반드시 재발 방지로 넘어간다.

4. lock_timeout으로 무한 대기 자르기

lock_timeout은 락을 기다리는 시간이 지정한 값을 넘으면 그 문장을 중단시킨다. 기본값은 0, 즉 무제한 대기다. 단위는 밀리초이고, 락 획득 시도마다 따로 적용된다. 전체 실행 시간을 재는 statement_timeout과 달리 오직 락을 기다리는 구간에만 작동한다.

운영 트랜잭션에서는 세션 단위로 거는 걸 권한다. 애플리케이션이 트랜잭션을 시작할 때 다음 한 줄을 먼저 보내면, 락을 3초 넘게 못 얻는 쿼리는 깔끔하게 실패한다.

SET lock_timeout = '3s';
UPDATE orders SET status = 'paid' WHERE id = 100;

대기가 길어지면 이렇게 끝난다.

ERROR:  canceling statement due to lock timeout
CONTEXT:  while updating tuple (0,12) in relation "orders"

무한 대기로 커넥션 풀이 잠기는 것보다, 짧게 실패하고 애플리케이션이 재시도하거나 사용자에게 알려주는 편이 운영에 유리하다. 공식 문서도 짚듯이 postgresql.conf에 전역으로 lock_timeout을 거는 건 권하지 않는다. 모든 세션에 일괄 적용되면 정상적인 긴 작업까지 잘려 나간다. 세션이나 역할, 또는 트랜잭션 단위로 거는 게 맞다.

한 가지 주의할 점은 statement_timeout과의 관계다. statement_timeout이 0이 아니고 lock_timeout과 같거나 더 작게 잡혀 있으면 문장 타임아웃이 먼저 걸려 lock_timeout은 의미가 없어진다. 락 대기만 따로 자르고 싶으면 lock_timeout을 statement_timeout보다 작게 둔다.

5. deadlock_timeout과 대기 로그

데드락은 두 트랜잭션이 서로가 쥔 락을 맞물려 기다려 영원히 못 풀리는 상태다. 세션 A가 1번 행을, B가 2번 행을 쥔 채 서로 상대 행을 추가로 요구하면 아무도 진행 못 한다. PostgreSQL은 이걸 자동으로 감지해 한쪽을 롤백시킨다.

deadlock_timeout은 그 감지를 언제 돌릴지 정하는 값이다. 기본값은 1s다. 락 대기가 시작돼도 즉시 데드락 검사를 하지 않고, 이 시간만큼 기다린 뒤에야 검사한다. 데드락 검사가 비싼 연산이라 매번 돌리지 않으려는 설계다. 대부분의 락 대기는 1초 안에 저절로 풀리므로 기본값을 건드릴 일은 많지 않다.

데드락이 실제로 터지면 이렇게 로그에 남는다.

ERROR:  deadlock detected
DETAIL:  Process 24871 waits for ShareLock on transaction 5123; blocked by process 24712.
         Process 24712 waits for ShareLock on transaction 5124; blocked by process 24871.
HINT:  See server log for query details.

이 값을 줄이면 데드락을 빨리 잡지만 CPU 부담이 커지고, 늘리면 반대다. 데드락이 잦다면 값을 줄이기 전에 애플리케이션에서 여러 행을 건드리는 순서를 통일하는 게 근본 처방이다.

deadlock_timeout은 한 가지 더 쓸모가 있다. log_lock_waits를 켜면, 이 시간을 넘긴 락 대기가 서버 로그에 기록된다. 데드락은 아니지만 오래 걸리는 락 대기를 사후에 추적할 때 유용하다.

SET log_lock_waits = on;
-- 로그 예시
LOG:  process 24871 still waiting for ShareLock on transaction 5123 after 1000.123 ms

6. RDS 파라미터 그룹에 반영하기

RDS와 Aurora에서는 postgresql.conf를 직접 못 고친다. 대신 DB 파라미터 그룹에서 값을 바꾼다. lock_timeout과 deadlock_timeout, log_lock_waits는 모두 동적 파라미터라 재부팅 없이 적용된다.

aws rds modify-db-parameter-group \
  --db-parameter-group-name sarc-pg16 \
  --parameters "ParameterName=log_lock_waits,ParameterValue=1,ApplyMethod=immediate" \
              "ParameterName=deadlock_timeout,ParameterValue=1000,ApplyMethod=immediate"

다만 lock_timeout은 전역으로 걸면 앞서 말한 부작용이 있으니, 파라미터 그룹 기본값은 0으로 두고 애플리케이션 트랜잭션에서 SET LOCAL lock_timeout으로 거는 걸 권한다. SET LOCAL은 그 트랜잭션이 끝나면 자동으로 원복되므로 커넥션 풀에서 값이 새어 나갈 걱정이 없다.

BEGIN;
SET LOCAL lock_timeout = '3s';
UPDATE orders SET status = 'paid' WHERE id = 100;
COMMIT;

7. 정리

쿼리가 멈추면 먼저 pg_stat_activity에서 wait_event_type = 'Lock'을 찾아 대기 세션을 확인하고, pg_blocking_pids() 조인으로 막는 세션의 PID와 상태를 특정한다. idle in transaction이 범인이면 pg_terminate_backend()로 끊어 급한 불을 끈다. 재발은 트랜잭션 단위 lock_timeout으로 무한 대기를 자르고, 데드락이 잦으면 접근 순서를 통일하는 것으로 막는다. RDS에서는 동적 파라미터와 SET LOCAL을 조합해 적용한다.