lock_timeout과 재시도로 DDL 안전하게 적용(lock queue 방지)

명령어

-- Single attempt: give up if the lock is not acquired within 3 s instead of queueing and blocking traffic
SET lock_timeout = '3s';
ALTER TABLE customer ADD COLUMN IF NOT EXISTS segment text;
RESET lock_timeout;

-- Automatic retry: up to 5 attempts, 3 s apart
DO $$
DECLARE
  attempt int := 0;
BEGIN
  LOOP
    attempt := attempt + 1;
    BEGIN
      SET LOCAL lock_timeout = '2s';
      ALTER TABLE customer ADD COLUMN IF NOT EXISTS segment text;
      RAISE NOTICE 'OK, attempt %', attempt;
      EXIT;
    EXCEPTION WHEN lock_not_available THEN
      RAISE NOTICE 'lock not acquired, attempt %', attempt;
      IF attempt >= 5 THEN RAISE; END IF;
      PERFORM pg_sleep(3);
    END;
  END LOOP;
END $$;

하는 일

ACCESS EXCLUSIVE가 필요한 DDL이 락을 얻지 못할 때 무한정 대기하거나 뒤에 대기하는 모든 쿼리를 블로킹하는 것(lock queue)을 막고, 짧은 간격으로 재시도한다.

사용 시점

운영 환경의 바쁜 테이블에 ALTER TABLE, CREATE/DROP TRIGGER, ATTACH PARTITION, RENAME처럼 빠르지만 강한 락을 잡는 변경을 적용할 때.

주의

lock_timeout이 없으면 장시간 SELECT 하나 뒤에 대기하는 ALTER TABLE이 이후의 모든 읽기/쓰기도 대기하게 만들어 몇 분 안에 커넥션 풀을 고갈시킬 수 있다. lock_timeout은 락 대기만 제한하며 테이블을 다시 쓰는 DDL(예: 컬럼 타입 변경)의 실행 시간은 제한하지 않는다. DO 블록은 단일 트랜잭션이며 시도 간 스냅샷을 유지하므로 긴 재시도 시나리오에서는 외부 셸 루프를 권장한다.