アトミックなグループ予約を保存する

はじめに

グループは一緒に参加したいと考えています。2 人の依頼に 1 席だけ予約できても成功ではありません。グループ全体の実際の予約確定を返し、残席が足りなければ何も変えない、再利用可能な SQL 文を保存します。実際の一意キー違反によって、席確保と予約確定が同じトランザクションに属するかを試します。

この独立した実習は、新しいワークショップと元の予約を準備し、前の実習のファイルは使いません。前の実習のトランザクション、行ロック、ROLLBACK の概念と、PostgreSQL の基本的な SELECT、INSERT、UPDATE が必要です。名前付きクライアントパラメーター、整数へのキャスト、データ変更の共通テーブル式はここで説明します。Workshop Desk はシェル、Desk A と Desk B は、1 台のマシン上の同じデータベースへの別々の psql 接続です。作者による所要時間の見積もりは読解と通常の操作ミスを含みますが、初心者の所要時間の調査はしていません。

/home/labex/project/workshop-desk/reserve.sql を作成します。提供されたツール、テーブル定義、元の予約と、各ワークショップの ID・タイトル・定員・メモを保全してください。ファイルは SQL 1 文だけを含み、psql コマンド、シェルコマンド、明示的な BEGIN/COMMIT、設定変更、外部リソースは含めません。説明された 4 パラメーターを受け、実際の予約の 4 フィールドを返します。同等の安全な SQL も受け入れます。これは制御された READ COMMITTED の演習であり、決済、自動再試行、分散環境の受付保証は扱いません。

予約確定につながる席確保を保存する

このステップでは、グループの席を実際に確保しなければ予約確定を作れない、1 つの文を保存します。

Workshop Desk から始めます。./ は現在のディレクトリの準備済みプログラムを実行します。状態表示プログラムは、足りない作業を補わず、実際のテーブルを読みます。

./desk status

201 は定員 6、元の予約が 1 席、残席 5 です。202 は 3 席、203 は 4 席です。original-family|201|1|Original を残す必要があります。最初の表示は workshop_id|title|capacity|places_left|booked_seats、2 つ目は request_code|workshop_id|seats|desk です。

ファイルは、psql 変数 workshop_id、seats、request_code、desk を受け取ります。各リクエストで別々に渡すクライアントパラメーターであり、データベース列や永続的なサーバー設定ではありません。:'name' は、その変数を安全に引用した SQL 文字列リテラルとして挿入するよう psql に指定し、リクエストコード内のアポストロフィも保ちます。引用符のない :name に置き換えないでください。PostgreSQL の ::integer は、ワークショップと正のグループ人数のテキストを整数に変換します。

WITH claimed AS (...) は、この文内で使う中間的な関係である共通テーブル式（CTE）に名前を付けます。ここでは CTE が条件付き UPDATE を行います。要求したグループ全体が収まる場合だけ、RETURNING workshop_id が 1 行を供給します。INSERT ... SELECT ... FROM claimed は、実際に返されたその行を使います。そのため、空の席確保からは INSERT が生じません。最後の RETURNING は、実際に挿入された予約を報告します。同じ文の中で基本テーブルを読み直せばデータ変更 CTE の新値が見えるとは考えず、RETURNING の関係を使ってください。

減算には要求人数を使い、WHERE 条件でも同じ人数を確認します。1 文は暗黙のトランザクションで実行されます。席確保と INSERT の両方が成功するか、SQL エラーで両方を取り消すかのどちらかです。後の 2 受付のテストでは、明示的なトランザクションで同じ文を未コミットに保ち、ロックを観察します。

完全な文を保存します。cat > reserve.sql は以下のテキストでファイルを置き換えます。引用符付きのヒアドキュメントマーカー SQL は、シェルによる内容の変更を防ぎます。閉じる SQL は単独行に貼り付けます。データベースの受付ではなく、シェルの Workshop Desk で実行してください。

cat > reserve.sql <<'SQL'
WITH claimed AS (
  UPDATE workshops
  SET places_left = places_left - :'seats'::integer
  WHERE workshop_id = :'workshop_id'::integer
    AND places_left >= :'seats'::integer
    AND :'seats'::integer > 0
  RETURNING workshop_id
)
INSERT INTO bookings (request_code, workshop_id, seats, desk)
SELECT :'request_code', workshop_id, :'seats'::integer, :'desk'
FROM claimed
RETURNING request_code, workshop_id, seats, desk;
SQL

使う前に保存したファイルを読みます。cat で表示します。入力ミスは別コマンドで隠さず、保存したソースで直します。

cat reserve.sql

desk book は、ネイティブな psql パラメーターを使い、このファイルをそのまま実行します。正解用クエリの提供、代わりの予約作成、残数の修復はしません。引数はワークショップ ID、正のグループ人数、一意のリクエストコード、受付ラベルです。通常の 2 人の依頼を実行します。

./desk book 201 2 normal-group A

実際に返る行は次のとおりです。

normal-group|201|2|A

正しい情報源を確認します。

./desk status

201 は残席 3、予約済み 3 席になりました。元の家族予約と、今回の正確な 2 人の予約が残ります。行が返るだけでは、健全なアルゴリズムの証明になりません。チェックは実際の行を読み、元のデータを保ち、実際の競合接続も含む別の隔離データで、変更していないソースを呼びます。一時的なテスト用データベースが、この実際の受付を修復することはありません。

SQL が失敗したら、プログラムはネイティブのエラーを表示し、非ゼロで終了します。残席不足とは呼びません。対話型の明示的トランザクションでは、まず同じ受付で ROLLBACK を使います。未コミットの入力ミスは何も変更しません。新しい誤った試行用予約を誤ってコミットしたら、前の実習で学んだように、その正確なコードだけを削除し、そのワークショップの残数だけを 1 トランザクションで再計算します。たとえば Desk A での次の復旧は、201 の仮の typo-trial だけを削除し、該当する誤りがなければ変更を生じません。

BEGIN;
DELETE FROM bookings WHERE request_code = 'typo-trial' AND workshop_id = 201;
UPDATE workshops
SET places_left = capacity -
  (SELECT COALESCE(SUM(seats), 0) FROM bookings WHERE workshop_id = 201)
WHERE workshop_id = 201;
COMMIT;

original-family や正当な指定予約は、決して削除しないでください。残る作業を決める前に状態を確認します。後続のコマンドが失敗しただけで、成功済みの依頼を再実行しないでください。

待機するグループと予約確定の失敗を試す

このステップでは、同じ保存済みファイルを 2 つの独立したネイティブ接続で実行し、別のワークショップを進めながら、INSERT の失敗で席が消費されないことを確かめます。

Desk A に切り替えます。プロンプトは、シェルではなく psql 内であることを示します。SQL はセミコロンで終わります。バックスラッシュで始まる psql コマンドはクライアントを制御し、セミコロンは付けません。\set はこの接続内だけのクライアント変数を設定し、別タブには引き継がれません。\i は指定したファイルを読み実行します。絶対ファイル名は、クライアントの現在のディレクトリに依存せず使えます。

READ COMMITTED トランザクションを開始し、初期残席 3 の 202 に 2 人のグループを送信します。

BEGIN ISOLATION LEVEL READ COMMITTED;
\set workshop_id 202
\set seats 2
\set request_code race-A
\set desk A
\i /home/labex/project/workshop-desk/reserve.sql

A は race-A | 202 | 2 | A と INSERT 0 1 を表示します。文は2 席を表す予約行を 1 行挿入しました。まだ未コミットです。プロンプトの * は開いているトランザクションを示します。

Desk B に切り替え、独立した依頼を準備します。

BEGIN ISOLATION LEVEL READ COMMITTED;
\set workshop_id 202
\set seats 2
\set request_code race-B
\set desk B
\i /home/labex/project/workshop-desk/reserve.sql

B は A の行ロックを待ち、プロンプトは戻りません。待たせたまま Workshop Desk に切り替えます。

./desk waits

Desk B の wait_event_type は Lock で、blocked_by には A の実際のバックエンドプロセス ID があります。READ COMMITTED では、A のコミット後、待機中の UPDATE が更新後の行で十分な残席を再評価します。以前に見えた残数は予約ではありません。

そのまま Workshop Desk で、同じ保存済みファイルを使い、別のワークショップを予約します。

./desk book 203 2 other-workshop C

A がまだ 202 を保持する間に、other-workshop|203|2|C を返して終了します。この無関係なワークショップまで不必要に止める、単一のグローバルロックを導入しないでください。

Desk A に切り替えます。

COMMIT;

Desk B に切り替えます。待機中のファイルは、ゼロ行と INSERT 0 0 で終了します。残席は 1 だけなので、2 人のグループは収まりません。グループを分割したり、別に予約確定を挿入したりしないでください。B のトランザクションを終えます。

ROLLBACK;

Workshop Desk に切り替えます。

./desk status

202 は残席 1 で、race-A|202|2|A だけがあり、race-B はありません。203 は残席 2 で、2 人の予約があります。成功の終了コードだけでなく、関係を確認してください。

次に、202 に 1 席を確保する余裕がある状態で INSERT を失敗させます。主キーにより、リクエストコードはワークショップ内だけでなく、bookings テーブル全体で一意である必要があります。original-family はすでに 201 に属します。ここで再利用すると、一意キー違反の SQLSTATE 23505 になるはずです。これは想定した失敗であり、空席なしの結果ではありません。

./desk book 202 1 original-family A

ファイル実行はネイティブエラーを報告し、ステータス 3 で終了します。他のシェルコマンドが $?（直前のコマンドの終了ステータス）を置き換える前に、直ちに取得してください。

printf 'Previous native exit: %s\n' "$?"

Previous native exit: 3 になるはずです。非ゼロのステータスは、エラーと正常なゼロ行の結果を区別します。文の暗黙のトランザクションは、その前の席確保も取り消す必要があります。台帳をもう一度読みます。

./desk status

202 はまだ残席 1 で、201 の original-family も変わりません。席確保後の ON CONFLICT DO NOTHING のような近道では、INSERT の失敗を隠しながら減算をコミットしてしまうことがあります。これはアトミックな拒否ではなく、残席数と実際の予約済み席数の合計が定員と一致しなくなります。チェックは十分な空席がある状態でこのケースを試すため、空の席確保によって誤ってエラーテストに合格することはありません。

新しいリクエストコードで、その最後の席を正当に予約します。

./desk book 202 1 after-error A

after-error|202|1|A が返るはずです。今度は残席ゼロの状態で、さらに 2 人を要求します。

./desk book 202 2 too-late B

プログラムは No booking returned. Inspect the real ledger before drawing a conclusion. を表示します。SQL は有効で予約を返さなかったため、この呼び出しはゼロで終了します。too-late 行を残さず、残数も変えてはいけません。

./desk status

最終的な新規予約は normal-group（201、2、A）、race-A（202、2、A）、other-workshop（203、2、C）、after-error（202、1、A）で、元の家族予約もそのままです。race-B と too-late はありません。残席はそれぞれ 3、0、2 です。

待機中のファイルを Ctrl+C でキャンセルすると、SQLSTATE 57014 により明示的なトランザクションは失敗状態になります。練習したように、再試行前に同じデータベースタブで ROLLBACK を使ってください。まずコミット済み台帳を確認します。他方の受付がすでに成功しているかもしれません。対話型の SQL エラーと、通常のゼロ行の結果では、必要な判断が異なります。

まとめ

保存した SQL は、実際の条件付き席確保を、グループ全体の予約確定 1 件につなげます。2 つのネイティブ接続で待機と再評価を実証し、別のワークショップは処理を続けました。リクエストコードの重複は実際の INSERT エラーを起こして席確保を取り消し、その後の正当な依頼は残っていた席を使えました。再利用可能なファイルと実際の台帳は、元データを保ち、残席不足と SQL エラーを区別します。