lock_timeoutとリトライでDDLを安全に適用(ロックキューの回避)

コマンド

-- 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が、ロックを取得できない場合に無期限に待機し、その後ろに並ぶすべてのクエリをブロックする(ロックキュー)ことを防ぐ。短い間隔でリトライする。

使用する場面

本番の高負荷の表に、ALTER TABLE、CREATE/DROP TRIGGER、ATTACH PARTITION、RENAMEなど、短時間だが強いロックを伴う変更を適用するときに。

注意

lock_timeoutがない場合、1つの長いSELECTの後ろに並んだALTER TABLEは、後続のすべての読み取り/書き込みも待機させ、数分で接続プールを枯渇させる可能性がある。lock_timeoutはロック待機のみを制限し、表を書き直すDDL(例: 列の型の変更)の実行時間は制限しない。DOブロックは単一のトランザクションであり、試行をまたいでスナップショットを保持する。長いリトライシナリオでは外部のシェルループを推奨する。