TT Lab
はじめる
学ぶ 学習パス コース

良いサービスを作る CS — 教科書の概念を計測で学び直す

制約で防ぎ、40001 だけを再試行する

TT Labで続きを見る

目標

確認してから挿入するコードが、2つのセッションで二重登録を作る場面を再現したあと、既存の重複を整理して、UNIQUEとINSERT … ON CONFLICTで防ぎます。予約の重なりは排他制約(EXCLUDE)で、座席の入れ替えはDEFERRABLE UNIQUEで解決します。複数の行にまたがるルールは、psycopg 3でSERIALIZABLEのリトライのループを書いて守り、最後に、不変条件ごとに置く場所を整理します。

なぜ重要なのか

アプリケーションが確認してから書くルールは、確認と書き込みの間の隙間で壊れ、その隙間は、負荷が集中したときにだけ表に出ます。データベースの制約は、分離レベルと無関係に常に成り立ち、あとから合流したコードが迂回することもできません。制約で書けないルールはSERIALIZABLEに任せますが、リトライのループが、どのエラーをやり直し、どのエラーを上へ送るかが、正確性を分けます。そのため採点ツールは、あなたが書いたファイルだけを見るのではなく、制約がカタログに設定されているかを確認し、反例の行を実際に入れてみてから元に戻します。リトライのループは、わざとエラーを起こす別のデータベースで呼び出してみます。

環境

PostgreSQL 16がPodの中で動いています。接続はpsql -h 127.0.0.1 -U lab -d labdb、パスワードはlabです(export PGPASSWORD=lab)。作業ファイルはすべて/root/svccs/invariants/に置きます。テーブルはすべてinvスキーマに作成します。Pythonは、python3とpsycopg 3が用意されています。

ステップ

  1. /root/svccs/invariants/schema.sqlを作成してロードします。DROP SCHEMA IF EXISTS inv CASCADE; CREATE SCHEMA inv;で始めて、下の「テーブル」を作成し、行を入れます。このステップでは、テーブルに書かれているもの以外の制約は設定しません。
  2. /root/svccs/invariants/signup_naive.sqlを作成します。トランザクションの中で、inv.signups_naiveにrace@example.comがいくつあるかを数え(\gset)、pg_sleep(2)で待ってから、0だったときだけ挿入します。2つのセッションを重ねて実行して重複を作り、そのメールアドレスの行数を、/root/svccs/invariants/02-race.txtにrows <수>の形式で書きます(プレースホルダーは数です)。
  3. inv.membersの既存の重複を整理し(最小のidを残す)、members_email_keyという名前でUNIQUE (email)を設定します(遅延不可)。/root/svccs/invariants/signup.sqlに、psql変数:'email'を使う文1つ(BEGIN/COMMITなし)で、ON CONFLICT (email) DO NOTHING RETURNING idの登録処理を書きます。race@example.comで2つのセッションを重ねて実行し、/root/svccs/invariants/03-unique.txtに、returned_a、returned_b(各セッションが受け取った行数)、rows(そのメールアドレスの行数)を1行ずつ書きます。
  4. /root/svccs/invariants/touch.sqlに、1つの文でupsertを書きます。存在しなければ挿入し(visits 1)、存在すれば既存のvisitsに1を加え、どちらの場合もRETURNING id, visitsでその行を返します。すでに存在するkim@example.comに、signup.sqlとtouch.sqlをそれぞれ実行したとき(元に戻す)に受け取った行数を、/root/svccs/invariants/04-upsert.txtに、do_nothing_rows <수>、do_update_rows <수>として書きます(プレースホルダーは数です)。
  5. btree_gist拡張を作成し、同じ部屋で[)の範囲が重なる既存の予約のペアの数を数えます。重なるペアのうち、idが大きいほうをstatus = 'cancelled'に変更し(削除しない)、bookings_no_overlapという名前で、アクティブ(status = 'active')な予約にだけかける排他制約を作成します。部屋が同じで、tstzrange(starts_at, ends_at, '[)')が重なってはいけません。/root/svccs/invariants/05-exclude.txtに、overlaps_found <센 쌍 수>、cancelled <취소 상태 행 수>、sqlstate <겹치는 예약을 넣었을 때 받은 코드>を書きます(プレースホルダーは、順に、数えたペアの数、キャンセル状態の行数、重なる予約を入れたときに受け取ったコードです)。
  6. /root/svccs/invariants/swap.sqlに、1つのトランザクションで、kimの座席を14に、leeの座席を12に変更するUPDATEを2つ書きます。現在のseats_seat_no_keyで実行して受け取ったSQLSTATEを、/root/svccs/invariants/06-swap.txtにimmediate_error <코드>として書き(プレースホルダーはコードです)、同じ名前の制約をDEFERRABLE INITIALLY DEFERREDで作り直したあと、swap.sqlを実行して、実際に入れ替えます。
  7. /root/svccs/invariants/retry.pyにwithdraw(conninfo, wallet_id, amount, max_attempts=8)を作成します。ルールは、下の「出金のルール」のとおりです。同じファイルのif __name__ == "__main__":で、kim家のウォレット2つを100に戻し、スレッド6個が同時に(開始を揃えて)ウォレット1・2から交互に80ずつ出金したあと、/root/svccs/invariants/07-retry.jsonに、workers、committed、rejected、retries(全試行数 − スレッド数)、final_sum(kim家の合計)を書きます。
  8. /root/svccs/invariants/map.jsonに、下の6つの不変条件ごとに、{"where": "constraint" | "isolation" | "application", "how": "쓴 도구 한 줄"}を書きます(韓国語で「使ったツールを1行で」を意味する語です)。採点ツールは、constraintと書いたものが、現在データベースに実際に設定されているかどうかも確認します。

テーブル

inv.members        id bigserial PK, email text NOT NULL, visits int NOT NULL DEFAULT 1
                   행: kim@example.com, lee@example.com, legacy@example.com, legacy@example.com
inv.signups_naive  id bigserial PK, email text NOT NULL                (행 없음, 끝까지 제약 없음)
inv.bookings       id bigserial PK, room text, starts_at timestamptz, ends_at timestamptz, who text,
                   status text NOT NULL DEFAULT 'active',
                   CONSTRAINT bookings_positive_length CHECK (starts_at < ends_at)
                   행: river 10:00~11:00 kim · river 11:00~12:00 lee · river 11:30~12:30 park
                       · hill 10:00~11:00 choi   (모두 2026-10-01, 시간대 +09)
inv.seats          id int PK, passenger text NOT NULL, seat_no int NOT NULL,
                   CONSTRAINT seats_seat_no_key UNIQUE (seat_no)
                   행: (1, kim, 12) (2, lee, 14) (3, park, 15) (4, choi, 16)
inv.wallets        id int PK, family text, owner text, balance int   (모두 NOT NULL)
                   행: (1, kim, kim-a, 100) (2, kim, kim-b, 100) (3, lee, lee-a, 50) (4, lee, lee-b, 50)

このコードブロックの韓国語の説明は、順に、inv.membersは既存の4行(kim・lee・legacyが2行)を持つこと、inv.signups_naiveは行がなく最後まで制約がないこと、inv.bookingsは予約4行(riverの10:00から11:00がkim、11:00から12:00がlee、11:30から12:30がpark、hillの10:00から11:00がchoi、すべて2026-10-01でタイムゾーンは+09)を持つこと、inv.seatsは座席4行、inv.walletsはfamilyとownerごとの残高4行で、すべてNOT NULLであること、を述べています。

出金のルール

연결은 conninfo 로 새로 연다. 격리 수준은 SERIALIZABLE.
한 트랜잭션: wallet_id 의 family 를 읽고 → 그 family 의 balance 합계를 읽고 →
  합계 < amount 면 아무것도 바꾸지 않고 {"status": "rejected", "attempts": n} 을 돌려준다
  아니면 UPDATE inv.wallets SET balance = balance - amount WHERE id = wallet_id 후 커밋하고
  {"status": "committed", "attempts": n} 을 돌려준다     (n = 이번 호출에서 시도한 횟수)
SQLSTATE 40001·40P01 이면 트랜잭션 전체를 처음부터 다시 한다(짧은 무작위 대기, 합계 1초 이내).
그 밖의 오류는 다시 하지 않고 그대로 올려보낸다. max_attempts 번 모두 실패하면 마지막 오류를 올려보낸다.

このコードブロックの韓国語の説明は、順に、接続はconninfoで新しく開き、分離レベルはSERIALIZABLEであること、1つのトランザクションで、wallet_idのfamilyを読み、そのfamilyのbalanceの合計を読み、合計がamountより小さければ何も変更せずにstatusがrejectedとattemptsを持つ結果を返し、そうでなければUPDATE inv.wallets SET balance = balance - amount WHERE id = wallet_idを行ってコミットし、statusがcommittedとattemptsを持つ結果を返すこと(nはこの呼び出しで試行した回数)、SQLSTATEが40001か40P01ならトランザクション全体を最初からやり直すこと(短いランダムな待機、合計1秒以内)、それ以外のエラーはやり直さずにそのまま上へ送り、max_attempts回すべて失敗したら最後のエラーを上へ送ること、を述べています。

6つの不変条件(ステップ8)

member_email_unique       이메일 하나에 회원 하나
room_no_overlap           같은 방의 활성 예약 시간이 겹치지 않는다
seat_unique               좌석 하나에 승객 하나(맞바꾸는 동안은 잠깐 깨져도 된다)
booking_positive_length   예약은 끝이 시작보다 뒤다
family_sum_nonnegative    가족 지갑 합계가 음수가 되지 않는다(여러 행에 걸친 조건)
welcome_mail_once         가입 환영 메일(외부 메일 서비스 호출)은 한 번만 보낸다

このコードブロックの韓国語の説明は、順に、member_email_uniqueはメールアドレス1つにつき会員1人、room_no_overlapは同じ部屋のアクティブな予約時間が重ならないこと、seat_uniqueは座席1つにつき乗客1人(入れ替えの間は一時的に壊れてもよい)、booking_positive_lengthは予約の終了が開始より後であること、family_sum_nonnegativeは家族のウォレットの合計が負にならないこと(複数の行にまたがる条件)、welcome_mail_onceは登録のウェルカムメール(外部メールサービスの呼び出し)を1回だけ送ること、を述べています。

参考

制約のないテーブルを作る

/root/svccs/invariants/schema.sqlを作成してロードしてください。invスキーマに、members・signups_naive・bookings・seats・walletsを、指示文の「テーブル」のとおりに作成します。

DROP SCHEMA IF EXISTS inv CASCADEで始めると、何度ロードし直しても同じ状態になります。psqlはエラーが出ても次の文を実行し続けるので、-v ON_ERROR_STOP=1を指定してください。時刻は「2026-10-01 10:00+09」のように、タイムゾーンまで書きます。

確認してから挿入すると重複が生じる

/root/svccs/invariants/signup_naive.sqlで2つのセッションを重ねて、inv.signups_naiveにrace@example.comの重複を作り、行数を/root/svccs/invariants/02-race.txtにrows <数>の形式で書いてください。

SELECT count(*) AS n … \gsetで数えた値を:nとして使います。2つのセッションとも、相手がコミットする前に数えて初めて、両方が0を見ます。片方を&でバックグラウンドに起動し、0.5秒後にもう片方を実行してください。順番に実行すると、2つ目は1を見て、挿入しません。

UNIQUEとON CONFLICT DO NOTHING

inv.membersの重複を整理して、members_email_key UNIQUE (email)を設定したあと、/root/svccs/invariants/signup.sql(文1つ)で2つのセッションを重ねて、/root/svccs/invariants/03-unique.txtにreturned_a・returned_b・rowsを書いてください。採点ツールは、signup.sqlをプローブ用のメールアドレスで2回実行してみて、元に戻します。

制約は既存の行も検査するので、legacyの重複が残っているとALTER TABLEが失敗します。DELETE … USINGで、同じメールアドレスの大きいidを削除してください。重ねた2つのセッションのうち、後のセッションは、前のセッションのコミットを待ってから、何も挿入せず、RETURNINGも空です。

DO UPDATEとRETURNINGの違い

/root/svccs/invariants/touch.sqlに、visitsを増やすupsertを1つの文で書き、すでに存在する会員にsignup.sqlとtouch.sqlが返す行数を、/root/svccs/invariants/04-upsert.txtに書いてください。採点ツールは、touch.sqlをプローブ用のメールアドレスで3回実行して、idとvisitsを確認します。

SET句では、既存の行はテーブルの別名(AS m)で、挿入しようとした行はEXCLUDEDで指します。EXCLUDED.visitsはデフォルト値の1なので、それに足すと、何回実行しても2です。DO NOTHINGのRETURNINGは、スキップした行を返しません。

予約の重なりはEXCLUDEで

btree_gistを作成し、既存の重なりを数えてキャンセルしたあと、アクティブな予約にだけかけるbookings_no_overlap排他制約を作成して、/root/svccs/invariants/05-exclude.txtにoverlaps_found・cancelled・sqlstateを書いてください。採点ツールは、プローブ用の部屋に、重なる予約・隣接する予約・キャンセルされた予約を入れてみて、元に戻します。

重なりは、tstzrange(starts_at, ends_at, '[)') && …で判定します。'[)'では、11時に終わる予約と11時に始まる予約は重なりません。'[]'で指定すると、kimとleeの予約のせいで、制約自体が設定できません。部分制約は、EXCLUDE … WHERE (status = 'active')です。

座席の入れ替えとDEFERRABLE

/root/svccs/invariants/swap.sqlで、即時検査のUNIQUEで受け取るエラーを、/root/svccs/invariants/06-swap.txtにimmediate_errorとして書き、seats_seat_no_keyをDEFERRABLE INITIALLY DEFERREDで作り直したあと、kimを14、leeを12に入れ替えてください。採点ツールは、制約の属性を確認し、トランザクションの中で入れ替えと重複を試してから元に戻します。

ALTER CONSTRAINTは外部キーしか変更できないので、同じALTER TABLEで、DROP CONSTRAINTとADD CONSTRAINTを一緒に使います。即時検査では、最初のUPDATEが終わった瞬間に12が2つになり、23505が発生します。一時的な番号(0)を使って3回に分けて変更すれば通過はしますが、制約は依然として即時検査のままです。

40001だけをやり直すリトライのループ

/root/svccs/invariants/retry.pyに、出金のルールどおりwithdrawを作成し、スレッド6個で同時に出金した結果を、/root/svccs/invariants/07-retry.jsonに書いてください。採点ツールは、別のデータベースで40001・40P01・23514をわざと起こしながらwithdrawを呼び出します。

psycopg.connect(conninfo, autocommit=True)で開き、conn.isolation_level = psycopg.IsolationLevel.SERIALIZABLEを設定してから、with conn.transaction():のブロックをループの中で開きます。例外のsqlstateで分岐してください。except Exceptionですべてをやり直すと、制約違反まで繰り返してしまいます。合計を読むSELECTもループの中に置かないと、やり直すときに新しい値を見られません。

不変条件ごとに置く場所

/root/svccs/invariants/map.jsonに、6つの不変条件ごとにwhere(constraint・isolation・application)とhowを書いてください。採点ツールは、constraintと書いたものが、現在データベースに設定されていて、反例を実際に拒否するかどうかを確認します。

CHECKは、検査中の行以外の行を見られないので、複数の行の合計は、制約では書けません。トランザクションが巻き戻されても、すでに出ていった外部呼び出しは戻ってこないので、それはデータベースが守れないルールです。