プロンプトと適用されるコンテキスト
テーブル doctor_shifts(team_id, shift_date, doctor_id, on_call) はオンコールのステータスを保存します。ビジネスルールでは、各チームおよびシフトで少なくとも 1 人のオンコール医師を維持する必要があります。Alice と Bob は共にアクティブであり、2 つのリクエストが同時に異なる医師をオンコールから外そうとします。各トランザクションはまずアクティブな医師の数をカウントします。カウントが 1 より大きい場合、自身の医師の行を更新します。
この問題の対象範囲は PostgreSQL 18 です。初期のアクティブ数は 2 です。両方のトランザクションはいずれかがコミットする前に読み取りを完了し、その後異なる行を更新します。数値とスキーマは面接用の前提条件であり、汎用的な医療データモデルではありません。核心的な問題は複数行の述語 count(on_call) >= 1 にあり、承認ワークフロー、タイムゾーン、認可は対象外です。
同じパターンは、在庫が決してマイナスにならない、アカウントに少なくとも 1 人の承認者を維持する、クラスタに少なくとも 1 つのプライマリを維持する、といったルールにも現れます。「トランザクションに入れればよい」という回答は不十分です。原子性（Atomicity）は単一のトランザクションを不可分な単位として成功または失敗させるだけです。分離レベル（Isolation Level）こそが、並行トランザクションが何を観察できるか、そして直列実行（シリアル実行）で説明がつかない結果をデータベースが拒絶するかどうかを決定します。
面接官が評価するポイント
第一の評価ポイントは、ライトスキュー（Write Skew） を認識できるかどうかです。両方のトランザクションは同じ述語を読み取りますが、書き込む行が異なるため、通常の同一行に対する書き込み競合は発生しません。各トランザクションは単体ではチェックを通過しますが、合算された結果としてアクティブな医師が 0 人になってしまいます。これをダーティリードや単なるロストアップデートと呼ぶと、誤った修正につながります。
第二の評価ポイントは、標準規格の名称とデータベースの実装を区別して理解しているかです。PostgreSQL の Read Committed はステートメントごとに新しいスナップショットを取得します。Repeatable Read は安定したトランザクションスナップショットを使用し、スナップショット分離（Snapshot Isolation）として実装されていますが、依然として直列化アノマリーを許容します。Serializable は、同様のスナップショットベースの読み取りに加えて読み書きの依存関係を追跡し、結果がいかなる直列順序とも一致し得ない場合にトランザクションをアボートします。同じ名前の分離レベルであっても、別のデータベースでは異なるロックやスナップショットのセマンティクスが採用されている場合があります。
第三の評価ポイントは、不変条件から適切な並行性の境界を選択できるかです。不変条件が複数行にまたがる場合、「更新しようとしている医師の行」をロックしても 2 つのトランザクションが衝突することはありません。安全な設計にするには、ロック可能な単一のオブジェクトで競合させるか、ルールを単一行に対するアトミックな条件に落とし込むか、Serializable に衝突を検出させる必要があります。
最後に、面接官は失敗時の処理を確認します。Serializable と明示的ロックの双方がトランザクションをアボートさせる可能性があります。一般的な PostgreSQL の直列化エラーコードは 40001 です。また、ロック取得順序の不整合によって 40P01 が発生することもあります。優れた回答では、トランザクション全体をリトライし、試行回数に上限を設け、メール送信などの外部副作用はコミット成功後の冪等なパスに移動させます。
回答前に明確にすべき質問
- 使用しているデータベースとバージョンは何か？ この回答は PostgreSQL 18 を対象としています。MySQL、SQL Server、分散データベースでは、同名の分離レベルやリトライ時のエラーについて改めて確認が必要です。
- ルールが対象とするのは厳密に 1 つのシフトか？
(team_id, shift_date)をキーとする不変条件であれば、シフトごとに 1 つの安定した競合ポイントを持つことができます。リージョンやデータベースをまたぐルールの場合、1 つのデータベース内の行ロックだけでは保護できません。 - オンコールステータスを変更可能なパスはどれか？ 休暇申請、シフト交代、インポート、管理者による修正など、すべてが同じプロトコルに従う必要があります。1 つの API だけを修正しても、カウンターを破壊したりロックをバイパスしたりする書き込みパスが残ってしまいます。
- 競合は散発的か、それとも常に高頻度（ホット）か？ 低い競合率では、リトライを前提とした Serializable が最も明快であることが多いです。単一行の条件付き更新は、アクセスの集中するホットなシフトでの無駄な作業を回避できますが、そのシフトを直列化されたホットスポットに変えることにもなります。
- リクエストはどの程度待機可能か？ 悲観的ロックはロック保持者の完了を待ちます。厳しいレイテンシ要件がある場合は、短いトランザクション、ロック待ち時間の上限、および「状態が変更されたためリトライ」という明示的な結果が必要です。
- トランザクションは外部への副作用を伴うか？ リトライ時にはトランザクションロジックが再実行されます。メール送信、当番表サービスへの呼び出し、非トランザクショナルなメッセージングは、コミット後に実行するか、トランザクショナルアウトボックスパターンと冪等なコンシューマーを使用する必要があります。
30秒で答える要約フレームワーク
「これはライトスキューです。両方のトランザクションが同一のオンコール集合をもとに判断しながら異なる医師の行を更新するため、安定した Repeatable Read スナップショットであっても両方のコミットが通ってしまいます。PostgreSQL の Serializable はこの読み書き依存関係を追跡し、少なくとも一方のトランザクションをアボートします。アプリケーションは 40001 を受けて判断全体を再実行する必要があります。Serializable を使わない場合は、各シフトを 1 行の当番表レコードに対応させ、Read Committed のもとで active_count > 1 の場合にのみアトミックにデクリメントし、同一トランザクション内で医師のステータスを更新します。もう 1 つの選択肢は、再カウント前に当番表の行をロックすることです。テストでは 2 つの接続を使用し、双方の読み取り後にバリアを設けることで、弱い分離レベルでは 0 人になり、安全な設計では厳密に 1 人が維持されることを実証します。」
ステップごとの詳細解説
まずは並行実行の履歴から確認します。初期状態は Alice=true, Bob=true です：
T1: read count(on_call) = 2
T2: read count(on_call) = 2
T1: update Alice to false
T2: update Bob to false
T1: commit
T2: commit直列実行で T1 が先に実行された場合、T2 は 1 を読み取って変更を拒否します。T2 が先に実行された場合、T1 が拒否します。実際の結果は 0 となり、どちらの直列実行の順序とも等価ではないため、これは直列化アノマリー（Serialization Anomaly）です。ロストアップデートとの決定的な違いは、T1 と T2 が同一の行を上書きすることがないため、通常の行レベルの排他ロックでは自然に衝突しない点にあります。
Read Committed では、各カウント文はそのステートメントが開始された時点でコミット済みのデータを参照します。プロンプトにあるバリアにより、両ステートメントはどちらかの更新が行われる前に 2 を読み取ります。UPDATE 文は異なる行を対象としているため、両方ともコミットできてしまいます。後続のステートメントはより新しいスナップショットを受け取りますが、PostgreSQL がすでに行われたアプリケーションの判断を遡って無効化することはありません。
PostgreSQL の Repeatable Read は、トランザクション全体で 1 つのスナップショットを維持します。この実装内での非再現リード（Non-repeatable read）やファントムリード（Phantom read）は防止されますが、直列化アノマリーは防げません。2 つのトランザクションは異なるバージョンチェーンを更新するため、同一行の並行更新によるロールバックはトリガーされず、両方コミットされる可能性があります。MVCC はリーダーがライターをブロックしない仕組みを説明するものであり、Serializable と同義ではなく、「少なくとも 1 人の医師がオンコールに残る」というビジネスルールを推論することもありません。
安全な第一の選択肢は Serializable です：
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) AS active_count
FROM doctor_shifts
WHERE team_id = 42
AND shift_date = DATE '2026-07-20'
AND on_call;
-- Reject when active_count <= 1.
UPDATE doctor_shifts
SET on_call = false
WHERE team_id = 42
AND shift_date = DATE '2026-07-20'
AND doctor_id = 101
AND on_call;
COMMIT;リクエストが重複すると、PostgreSQL は述語読み取りと並行書き込みが直列化不可能な依存関係を形成していることを検出します。両方のトランザクションをコミットさせることはできません。失敗したトランザクションは SQLSTATE 40001 を返します。リトライは BEGIN の前から開始し、カウントと更新の判断を再実行しなければなりません。最後の UPDATE のみをリトライしても、古い決定を維持してしまうことになります。高競合下ではリトライが再び衝突する可能性があるため、ジッター付きバックオフ（Jittered Backoff）、最大試行回数、および全体のタイムアウトを設定してください。
Serializable を使用すると、アプリケーションはすべての不変条件に対して手動でロックを設計する代わりに、実際の述語に基づいてルールを表現できます。その代償は、依存関係追跡のオーバーヘッドと衝突時のリトライです。読み取りセットが大きく、トランザクションが長く、競合が集中するほど、重複の可能性は高くなります。(team_id, shift_date) のインデックス、短いトランザクション、コミット前のネットワーク待機をなくすことで、そのウィンドウを狭めることができます。
第二の選択肢は、複数行の不変条件を単一の行 on_call_rosters(team_id, shift_date, active_count) に射影し、Read Committed のもとでアトミックな更新を使用することです：
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
UPDATE on_call_rosters
SET active_count = active_count - 1
WHERE team_id = 42
AND shift_date = DATE '2026-07-20'
AND active_count > 1
RETURNING active_count;
-- Continue only when exactly one roster row was returned.
UPDATE doctor_shifts
SET on_call = false
WHERE team_id = 42
AND shift_date = DATE '2026-07-20'
AND doctor_id = 101
AND on_call;
-- Commit only when both updates changed exactly one row; otherwise roll back.
COMMIT;両方のリクエストは同一の当番表行で競合するようになります。PostgreSQL の Read Committed で並行更新の完了を待機した後、更新処理は新しい行バージョンに対して active_count > 1 を再チェックします。最初のリクエストは 2 を 1 に変更します。2 番目のリクエストは条件に合致せず、行を返しません。行レベルの CHECK (active_count >= 1) を設定することで最終的な防壁とすることもできます。この方法のトレードオフは、アクティブ化、非アクティブ化、インポート、修正のすべてのパスにおいて、同一トランザクション内でカウンターを維持しなければならない点です。また、システムはカウンターと詳細行を照合し、暗黙的に上書きするのではなく不整合を検知してアラートを出すべきです。
カウンターの管理が望ましくない場合は、シフトごとに安定した当番表レコードを保持します。Read Committed トランザクションの最初のステップとして、その行に対して SELECT ... FOR UPDATE を実行し、その後に詳細行をカウントして医師の行を更新します。2 番目のリクエストが待機した後の次の SELECT は、最初のコミットを含むスナップショットを取得します。すべての書き込みパスは最初に同じ当番表行をロックする必要があり、トランザクションは短く保たなければなりません。注意すべき安全でない実装として、古い Repeatable Read スナップショットを確立した後に誰も更新しないガード行をロックするパターンがあります。FOR UPDATE を取得してもトランザクションスナップショットは更新されません。
現在アクティブなすべての医師の行を直接ロックする方法も注意が必要です。ロック対象のセットは述語によって決定されるため、新規行の挿入、異なるクエリ順序、またはバイパス書き込みによって安全性の前提が崩れる可能性があります。また、複数行ロックにわたる順序の不整合はデッドロックを引き起こす可能性があります。この設計が必要な場合は、安定した主キー順でロックを取得し、40P01 発生時にはトランザクション全体を再実行してください。「自分がオンコールから外そうとしている医師」のみをロックするのは、リクエストが依然として異なる行をロックするため、明らかに不十分です。
この検証を 2 つの API を順次呼び出す形で行ってはいけません。2 つの独立したデータベース接続とテストバリアを使用します。両方のセッションが Repeatable Read を開始し、行をカウントして、双方が 2 を観測したことをアサートした上で、更新とコミットに進みます。ベースラインでは確実に 2 つのコミットが発生し、最終カウントが 0 になるはずです。Serializable のもとでは、両方の成功が不可能であることをアサートします。一方のトランザクションが 40001 を受け取り、その完全なリトライが 1 を観測して変更を拒否します。当番表設計のもとでは、厳密に 1 つの条件付き更新のみが行を返し、詳細と active_count の双方が最終的に 1 になることをアサートします。
コミット順序を入れ替えて並行テストを繰り返します。同一医師に対する重複リクエスト、3 人目の医師の追加、2 つの更新間でのロールバック、ロック待ちタイムアウト、デッドロックのケースを追加します。本番環境では、40001、40P01、リトライ試行回数、ロック待機時間、トランザクション実行時間、最終的な失敗率、および当番表カウンターと詳細行の差異を監視します。衝突率の急激な増加は、通常ホットスポット、長いトランザクション、または境界外からの新しい書き込みパスの混入を示唆しています。無制限のリトライは問題を隠蔽し、増幅させるだけです。
高評価な模範回答
「まず、これをライトスキュー（Write Skew）として特定します。両方のトランザクションは同じ複数行にまたがるルールをチェックしますが、Alice と Bob はそれぞれ自身の行を更新するため、行レベルの排他ロックは衝突しません。双方が最初に 2 を読み取るスケジュールの場合、Read Committed では両方のコミットが成功してしまいます。PostgreSQL の Repeatable Read は各トランザクションに安定したスナップショットを提供しますが、それでも直列実行では説明できない最終値 0 を生成する可能性があります。
私の第一の選択肢は、ルールを Serializable トランザクションで表現することです。シフトのアクティブな医師をカウントし、カウントが 1 より大きい場合にのみ更新します。PostgreSQL は述語の読み取りと並行書き込みの間の依存関係を追跡します。この場合、両方のトランザクションが同時にコミットすることはできず、一方が 40001 を受け取ります。アプリケーションはそのエラーをキャッチし、上限付きのリトライポリシーに従って最初からカウントと更新を再実行します。通知などの処理はリトライ可能なトランザクションの外に置き、コミット後に冪等なアウトボックスから処理します。
持続的な競合が発生するシフトに対しては、on_call_rosters 行の導入を検討します。Read Committed のもとで、離脱トランザクションは UPDATE ... SET active_count = active_count - 1 WHERE active_count > 1 RETURNING ... を使用してその単一行で競合させ、その後に医師の詳細を更新します。いずれかのステートメントが厳密に 1 行の変更に失敗した場合、トランザクション全体がロールバックされます。2 番目のリクエストは最新の行バージョンに対して条件を再チェックし、失敗します。トレードオフは、すべての書き込みパスでカウンターを維持しなければならない点です。
設計の検証は、2 つの接続とインターリーブを固定するバリアを用いて行います。まず、Repeatable Read で両セッションが 2 を読み取って 0 に到達できることを証明します。次に、Serializable が厳密に 1 つのトランザクションをアボートし、そのリトライが変更を拒否することを証明します。カウンター設計では、1 つの条件付き更新のみが許可されることを確認します。最後に、シリアライゼーション失敗、デッドロック、ロック待ち、およびカウンターの不整合を監視し、安全性の主張が偶然のテストスケジュールに依存しないようにします。」
よくある間違い
- 「トランザクション内にあるので安全である」 → 原子性は並行トランザクション間の可視性やコミット順序については何も保証しません → 分離レベルを明記し、不変条件を破壊するインターリーブを記述してください。
- ライトスキューをロストアップデートとして扱う → トランザクションは異なる行に書き込むため、単一行のバージョンチェックでは共有述語をカバーできません → 複数行にまたがる不変条件を特定し、共通の競合ポイントまたは Serializable を使用してください。
- Repeatable Read は Serializable と等価であると主張する → 安定したスナップショットであっても、直列順序で説明できない複合結果を生み出す可能性があります → PostgreSQL の Repeatable Read で許可される直列化アノマリーを説明してください。
- 各トランザクションの医師の行をロックする → Alice と Bob のロックは衝突しません → 1 つの当番表行をロックするか、1 つのカウンター行を更新するか、SSI に述語依存関係を検出させてください。
- 古い Repeatable Read スナップショットの後に
FOR UPDATEを追加する → 変更されていないガード行をロックしても、トランザクションスナップショットは更新されません → Read Committed のもとでロックして再読み取りするか、Serializable または可変カウンター行を使用してください。 40001の後にCOMMITまたは最後の SQL 文のみをリトライする → ビジネス判断は依然として古いスナップショットに基づいています → 完全なトランザクションと、SQL を選択するすべてのアプリケーションロジックを再実行してください。- 上限なしで即座にリトライする → ホットなリクエストが同期して衝突し、データベースの負荷を増大させます → ジッター付きバックオフ、最大試行回数、および全体のタイムアウトを使用してください。
- コミット前に通知を送信する → 直列化リトライにより、通知が複数回送信される可能性があります → 外部への副作用を遅延させ、冪等なコンシューマーを備えたトランザクショナルアウトボックスを使用してください。
- 詳細行へのバイパス書き込みを許可しながらカウンターを維持する →
active_countが実際のオンコール数から乖離し、条件文が無意味になります → すべての書き込みパスに同一のトランザクションプロトコルを使用させ、継続的に整合性を確認してください。
フォローアップの質問と回答
フォローアップ 1: 単一のアトミックな UPDATE で在庫のマイナスは防げるのに、なぜこの問題は自動的に解決できないのか？
単一の在庫行であれば UPDATE inventory SET stock = stock - 1 WHERE stock > 0 を使用できます。条件と書き込み対象が同一行であるため、並行して更新を行う処理は最新バージョンに対して条件を再チェックします。一方、本問の条件は複数行にまたがるカウントであり、書き込みは 1 人の医師を対象としています。同様の性質を得るには、カウントを 1 行の当番表レコードに実体化するか、Serializable を使用して述語の依存関係を検出する必要があります。
フォローアップ 2: Serializable の失敗率が高い場合はどうするか？
まず、トランザクションを短縮し、述語にインデックスを貼り、トランザクション内からネットワーク呼び出しを排除し、シフトごとの競合を測定します。一部のホットなシフトが 40001 エラーの大半を占めている場合は、それらの書き込みのみを当番表の条件付き更新に移行して単一行で競合をキューイングさせます。すべてのシフトで競合が激しい場合は、バッチ処理やトランザクションの境界を見直します。リトライ上限の引き上げは、キャパシティ分析の代替にはなりません。
フォローアップ 3: CHECK 制約のみで医師が 1 人残ることを保証できるか？
1 つの doctor_shifts 行に配置されたチェック制約はその行のフィールドを検証できますが、その行自体が同一シフトで別の医師がアクティブであることを単独で証明することはできません。不変条件を当番表の active_count に射影した後は、CHECK (active_count >= 1) がデータベースレベルのガードになります。詳細行とカウンターは依然として同一トランザクション内で変更し、整合性を維持する必要があります。
フォローアップ 4: 当番表のカウントをデクリメントした後にサービスがクラッシュした場合はどうなるか？
カウンターと医師のステータスが単一のデータベーストランザクション内にある場合、セッション切断により未コミットのトランザクションはロールバックされるため、変更が半分だけ永続化することはありません。コミットが成功したもののクライアントがレスポンスを受信できなかった場合、結果は不明となります。リトライ処理にはビジネス操作 ID を含め、同一の離脱アクションを再カウントする前に医師の現在の状態を確認する必要があります。
フォローアップ 5: ルールが 2 つのデータベースに分かれて保存されている医師を対象とする場合はどうするか？
単一データベースの Serializable や行ロックは、そのデータベースから見えるデータのみを保護します。強力な整合性を持つ当番表サービスを設け、その状態を他のデータベースへ非同期に射影するなど、信頼できる単一の書き込み境界を選択するか、分散トランザクションを導入してその調整・可用性・レイテンシのコストを受け入れます。双方がローカルで判断して非同期にマージする場合、設計上明示的に一時的な違反を許容し、補償処理を定義する必要があります。単一データベースにおける安全性の証明はもはや適用されません。