守りたい条件を先に書く
この実験の条件は、当直者が最低一人は残ることです。二人とも勤務中なら一人は解除できますが、二人が同時に同じ判断をすると誰も残らない可能性があります。PostgreSQL18のRepeatable ReadとSerializableで、同じ二つのトランザクションを比較します。以下は二セッションの再現手順であり、実行済みのDB結果ではありません。専用の使い捨てローカルDBを用意し、本番の当直情報や共有表では試さないでください。どちらのトランザクションも、自分以外を含む当直者数を読むという前提で考えます。
二つの接続と専用の表を用意する
AとBの二つの端末を同じ実験DBへ接続します。初期設定は一回だけ行い、lab_skewという新しいスキーマに二行を作ります。既に同名がある場合は作成を止めるため、IF NOT EXISTSは付けていません。既存のスキーマを上書きせず、別の未使用名でやり直してください。コードのA、Bという注記は、それぞれ別の接続で実行する場所です。一つの接続へ全体を順番に貼り付けても、二つの並行トランザクションを再現したことにはなりません。手順の順序と接続名を記録します。
両方が読むまで更新を待つ
AとBをRepeatable Readで開始し、どちらも当直者数2を読み終えてから更新します。Aはid1だけをfalseにしてコミットし、その後Bはid2だけをfalseにしてコミットします。PostgreSQLのRepeatable Readでは、このような異なる行への更新による直列化異常が起こり得ます。両者の更新行が違うので、同じ行を書き換える競合の保護だけでは全体の条件を守れません。最後に新しい問い合わせで当直者数を確認し、二つのトランザクション内で見えていた値とは区別して記録します。
-- Setup once in a disposable local database, outside transactions.
-- CREATE SCHEMA deliberately fails if this name already exists.
CREATE SCHEMA lab_skew;
CREATE TABLE lab_skew.doctors(id integer PRIMARY KEY, on_call boolean NOT NULL);
INSERT INTO lab_skew.doctors VALUES (1,true),(2,true);
-- A:
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM lab_skew.doctors WHERE on_call;
-- B, before A changes a row:
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM lab_skew.doctors WHERE on_call;
-- A:
UPDATE lab_skew.doctors SET on_call=false WHERE id=1 AND (SELECT count(*) FROM lab_skew.doctors WHERE on_call)>1;
COMMIT;
-- B:
UPDATE lab_skew.doctors SET on_call=false WHERE id=2 AND (SELECT count(*) FROM lab_skew.doctors WHERE on_call)>1;
COMMIT;
-- Either session, outside the preceding transaction:
SELECT count(*) FROM lab_skew.doctors WHERE on_call;
-- Reset only after both sessions finish, then repeat using SERIALIZABLE:
UPDATE lab_skew.doctors SET on_call=true;
直列に並べられない理由を説明する
もしAを先に全部実行すれば、Bは当直者一人を見て解除をやめるはずです。Bを先に実行しても、Aが同じように解除をやめます。ところが両方が二人を見て解除した結果は、そのどちらの直列順序にも一致しません。これがwrite skewの考え方です。アプリが実際に「一人ならやめる」という分岐を持つことを前提に、読む対象と更新する対象の依存を考えます。単にfalseを二回書くだけなら、同じ不変条件を検査する業務操作の説明にはなりません。データの状態とアプリの判断を一組で追います。
Serializableで同じ履歴を比較する
二接続の取引が全て終わってから初期値へ戻し、両方をSerializableとして同じ読み書きの順序を試します。直列化に必要な依存が衝突すれば、どちらかが直列化失敗で中断され得ます。失敗する文や接続を、あらゆる環境で同じと固定してはいけません。アプリはその取引全体を、最初の読取りと判断からやり直す必要があります。最後のUPDATEだけの再送では、古い判断を使い回してしまいます。送信したメールなどDB外の副作用は、ロールバックで自動的に取り消されないことも別に扱います。
行ロックや制約を選ぶ場合の条件
別の対策として、判定に関係する行を規則に従ってロックする設計や、条件を表現できる形にデータモデルを変える方法も考えられます。ただし単純な各行のCHECK制約は、他行を含む当直者数をそのまま守るものではありません。どの行が存在するかも変わる場合には、行ロックだけで足りるかを再検討します。最終的に選ぶ対策について、同時実行、失敗時の再試行、追加・削除時の扱いを確認します。実験終了後に削除する場合は、自分が今回作成した専用スキーマだけを対象にします。