마이그레이션 DDL 잠금 대기를 pg_blocking_pids로 찾고 lock_timeout으로 제한

문제 상황

배포의 ALTER TABLE이 한참 걸려 있고 뒤따르는 쿼리까지 밀린다. 누가 잠금을 쥐고 있는지, 어떻게 무한 대기를 막을지 본다.

전제

• PostgreSQL 16, psql 세션 2개(A, B) + 관찰 세션 1개
• 전용 disposable 스키마 사용(운영 아님)

CREATE SCHEMA lock_lab; -- 이미 존재하면 중단: 기존 스키마 재사용 금지
CREATE TABLE lock_lab.t (id int PRIMARY KEY, v int);
INSERT INTO lock_lab.t VALUES (1, 10);

재현

세션 A — 잠금을 쥔다:

BEGIN;
UPDATE lock_lab.t SET v = 11 WHERE id = 1;   -- 커밋하지 않음

세션 B — DDL이 잠금 대기에 걸린다. 무한 대기 대신 범위를 제한한다:

BEGIN;
SET LOCAL lock_timeout = '2s';       -- 잠금 획득 대기 상한
SET LOCAL statement_timeout = '15s'; -- 문장 전체 실행 상한
ALTER TABLE lock_lab.t ADD COLUMN w int;

2초 안에 잠금을 못 얻으면 canceling statement due to lock timeout 오류가 난다. 이때 세션 B는 실패 트랜잭션 상태이므로 반드시 ROLLBACK:

ROLLBACK;

각 timeout 범위 구분:

• lock_timeout: 잠금 획득 대기 구간만 제한.
• statement_timeout: 잠금을 얻은 뒤라도 문장 총 실행 시간을 제한.
  둘은 서로 다른 구간을 지킨다. DDL엔 보통 lock_timeout을 짧게 둔다.

누가 막고 있나 (관찰 세션)

2초 대기는 수동 관찰 전에 끝날 수 있다. 아래 SELECT를 관찰 세션에 미리 입력해 두고 psql의 \watch 1로 1초마다 반복한 상태에서 B의 ALTER TABLE을 실행한다. 관찰이 끝나면 Ctrl+C로 반복을 중단한다. timeout 이후 행이 안 보이는 것은 차단 세션이 없었다는 뜻이 아니라 해당 대기가 이미 끝났다는 뜻일 수 있다.

SELECT pid,
       pg_blocking_pids(pid) AS blocked_by,
       state,
       wait_event_type
FROM pg_stat_activity
WHERE datname = current_database()
  AND wait_event_type = 'Lock';

pg_blocking_pids(pid)가 세션 B를 막는 세션 A의 pid를 배열로 준다. query 텍스트에는 민감정보가 있을 수 있어 여기서는 조회하지 않았다. 필요 시 최소 컬럼만 본다. 참고로 다른 세션의 정보를 보려면 권한이 필요하다(일반 사용자는 자기 것만 보일 수 있음).

잠금 해소 후 검증

A에서 ROLLBACK한 다음 B에서 새 트랜잭션을 시작한다. SET LOCAL 값은 앞 트랜잭션이 끝나면 사라지므로 다시 지정한다.

BEGIN;
SET LOCAL lock_timeout = '2s';
SET LOCAL statement_timeout = '15s';
ALTER TABLE lock_lab.t ADD COLUMN w int;
COMMIT;
SELECT column_name FROM information_schema.columns
WHERE table_schema = 'lock_lab' AND table_name = 't'
ORDER BY ordinal_position;

처음 실패한 DDL은 반영되지 않았고, 재시도 후 id, v, w가 보이면 성공이다. statement_timeout에는 잠금 대기 시간도 포함된다. lock_timeout은 각 잠금 획득 대기에 적용되므로 여러 잠금을 얻는 문장 전체 시간을 대신하지 않는다.

절대 자동화하지 말 것

막는 pid를 찾았다고 pg_terminate_backend(pid)를 스크립트로 자동 실행하지 말 것. 트랜잭션을 강제 종료하면 진행 중 작업이 유실될 수 있다. 종료는 사람이 상황을 확인한 뒤 개별 판단으로 한다.

되돌리기 / 한계

• 세션 A·B의 열린 트랜잭션을 모두 종료한다. 이번 실습에서 새로 만든 테이블에 한해 DROP TABLE lock_lab.t; 후 DROP SCHEMA lock_lab;로 정리한다. 예상 밖 객체가 있으면 스키마 삭제가 실패하도록 CASCADE를 쓰지 않는다.
• 위 시간 값(2s/15s)은 가상 예시다. 실제 값은 트래픽·DDL 성격에 맞춰 정한다.

출처

• client config(lock_timeout 등)
• info functions(pg_blocking_pids 등)