PostgreSQLのpartial unique indexでrace conditionによる二重実行を防ぐ

結論

学校管理者向けの管理画面にある「翌年度へ複製」のような一括処理ボタンは、アプリ層で既存行を確認してから挿入する「graceful skip」だけでは、二重クリックや別タブでの再実行による重複行の生成を防げない。 READ COMMITTEDの下では、2つのトランザクションが同じタイミングで存在確認のSELECTを実行すればどちらも「0行」を見てしまい、両方がINSERTに進む。このphantom raceはアプリ層からは観測できないため、最終的な直列化はDBの部分unique indexに委ね、競合したINSERTは23505として検知してconflictとして返す設計にした。

症状

対象はclassesテーブルで、ある学年・クラス構成を翌年度にコピーする一括処理アクションが用意されていた。処理自体は次の2つの関数で構成される。

// hub-core.ts:291-292
export function classDupKey(gradeId: string, name: string): string {
  return `${gradeId} ${name}`;
}

// hub-core.ts:298-308（nextDuplicationYears）
export function nextDuplicationYears(
  rows: ClassYearRow[],
): { sourceYear: number; targetYear: number } | null {
  if (rows.length === 0) {
    return null;
  }
  const sourceYear = Math.max(...rows.map((r) => r.academicYear));
  return { sourceYear, targetYear: sourceYear + 1 };
}

sourceYearは常に「現在ある中で最新の年度」、targetYearはその翌年度として決まる。つまりこの処理は実行するたびに1年進む・冪等ではない設計で、同じボタンを2回押すと2回分進んでしまう余地がある。これを防ぐため、planNextYearDuplicationはexistingTargetKeys（target年度に既に存在するクラスの集合）を受け取り、既にある分を複製対象から除外する。

// hub-core.ts:310-331（planNextYearDuplication、コメントは原文のまま）
/**
 * 現クラス一覧から「翌年度へ複製すべきクラス」を算出する純関数。クラスが無ければ null。
 *
 * @param rows 自校の全クラス（全年度）。source=最新年度 / target=source+1 を決める。
 * @param existingTargetKeys 既に target 年度に存在するクラスの classDupKey 集合。冪等化のため除外する
 *   （並行コミットを RLS tx 内で観測できた場合に重複 insert を避け graceful skip するための防御。観測
 *    できない phantom race の恒久ガードは DB の部分 unique index ux_classes_school_year_grade_name）。
 *   未指定は空集合（除外なし＝従来挙動）。
 */
export function planNextYearDuplication(
  rows: ClassYearRow[],
  existingTargetKeys: ReadonlySet<string> = new Set(),
): NextYearPlan | null {
  const years = nextDuplicationYears(rows);
  if (!years) {
    return null;
  }
  const { sourceYear, targetYear } = years;
  const toCreate = rows
    .filter((r) => r.academicYear === sourceYear && r.gradeId)
    .filter((r) => !existingTargetKeys.has(classDupKey(r.gradeId as string, r.name)))
    .map((r) => ({
      gradeId: r.gradeId as string,
      name: r.name,
      grade: r.grade,
      academicYear: targetYear,

コメントが自ら明言している通り、このexistingTargetKeysによる除外は「並行コミットをRLSトランザクション内で観測できた場合」の防御であって、観測できない並行実行（phantom race）までは塞げないことが設計時点で分かっていた。

原因

二重クリックや別タブでの再実行は、ほぼ同じタイミングで2つのトランザクションを立ち上げる。READ COMMITTEDでは、各SELECTはその時点でコミット済みの行だけを見るため、どちらのトランザクションもまだ相手がINSERTしていない「target年度に0行」という状態を観測できる。結果、existingTargetKeysは両方のトランザクションで空集合になり、planNextYearDuplicationは両方で「全件複製対象」という同じ計画を返す。どちらのSELECTも嘘をついたわけではなく、その時点では事実だった。問題は「SELECTして確認してからINSERTする」という手順そのものに、確認とINSERTの間に別トランザクションが入り込める隙間があることだった。

直し方

アプリ層の事前チェックはそのまま残し、最終防御としてDBに部分unique indexを追加した。

-- 20260614024542_chilly_guardsmen.sql:1-27（コメントは原文のまま）
-- 「新年度へ複製」(duplicateClassesToNextYearAction) の並行実行 / 別タブ再実行による翌年度クラスの
-- 重複生成を **DB レベルで封じる**部分 UNIQUE index（classes に school×年度×学年×クラス名の一意制約）。
-- 同一校・同一年度・同一学年(grade_id)・同名クラスは 1 行のみに直列化する。
--
-- なぜ部分 index（WHERE grade_id IS NOT NULL）か:
--   - 複製対象は grade_id を持つクラスのみ（planNextYearDuplication が gradeId=null を除外）で、
--     学年未割当クラスは掲示階層に乗らない。そこに一意制約を課す意味が無い。
--   - そもそも Postgres は NULL を distinct 扱いするため、grade_id を鍵に含めても NULL 行同士は
--     衝突しない。部分 index で NULL 行を index 対象外にして意図を明示する。
--
-- なぜ school_id を鍵に含めるか:
--   classes はマルチテナント（全校の行を 1 テーブルに保持）。テナント整合のため一意性は校内に閉じる。
--   RLS は SELECT/書込みを自校に限定するが、index は物理的に全テナント横断なので school_id が必須。
--
-- app 層との関係:
--   duplicateClassesToNextYearAction は planNextYearDuplication + 既存 target 除外で graceful skip を
--   狙うが、READ COMMITTED の phantom race（並行 tx が双方 SELECT で 0 行→双方 INSERT）は app 層では
--   塞げない。本 index を直列化の真の砦にし、競合 INSERT は 23505 → finish の conflict 写像で graceful に返す。
CREATE UNIQUE INDEX "ux_classes_school_year_grade_name" ON "classes" USING btree ("school_id","academic_year","grade_id","name") WHERE "classes"."grade_id" IS NOT NULL;

キーは(school_id, academic_year, grade_id, name)の4列。school_idを含めるのは、classesが全校の行を1テーブルに持つマルチテナント構成だからだ。RLSのポリシーはSELECTや書き込みを自校の行に絞るが、unique indexそのものは物理的にテーブル全体を見て一意性を判定するため、school_idを明示的に含めないと他校の同名クラスとまで衝突判定されてしまう。WHERE grade_id IS NOT NULLで部分indexにしているのは、複製の対象自体がgrade_idを持つクラスに限られており（planNextYearDuplicationの.filter((r) => ... && r.gradeId)）、学年未割当のクラスに一意制約を課す理由が無いためだ。コメントにもある通り、PostgreSQLはNULLをdistinct扱いするのでNULL行同士はそもそも衝突しないが、部分indexの条件として明示することで「複製対象だけを直列化する」という意図をスキーマに残している。

これにより、2つのトランザクションが同時にINSERTを試みても、片方は23505（unique_violation）を返される。アプリ層はこれを異常なエラーではなく、通常の競合（conflict）として扱う前提に設計を変えた。

再発防止

このギャップが生まれた理由は、アプリ層の「SELECTしてから書き込む」という防御が、トランザクション分離レベルの隙間を塞ぐものではないという点が、existingTargetKeysを導入した時点では明示されていなかったことにある。コメントは最終的に「observed できた並行コミットの防御」と「observed できないphantom raceの恒久ガード」を明確に分けて書いており、一括処理ボタンに「存在確認→挿入」という形のロジックを追加するときは、その確認が防げるのはどこまでで、防げない分をDBのどの制約に委ねるのかを、実装時点で切り分けておく必要がある。