ワークショップの最後の席を守る

はじめに

2 つの受付が工芸ワークショップの席を販売しています。来場者が求めるのは、単に残数が非負に見えるカウンターではなく、確定した席です。まず通常のグループ予約を行い、次に使い捨ての練習用ワークショップで 1 席に対する 2 件の予約確定を再現します。最後にその席を保護し、別のワークショップでは接客を続けられるようにします。

基本的なターミナル操作と PostgreSQL の SELECT、WHERE、INSERT、UPDATE の知識が必要です。トランザクション、接続の識別、行ロック、RETURNING、復旧はここで説明します。PostgreSQL と架空のデータは準備済みです。Workshop Desk はシェル、Desk A と Desk B は 1 台のマシン上の同じデータベースに対する別々のネイティブな psql 接続です。3 つのタブをすべて開いておいてください。実行環境に表示されるテキストとラベルは英語です。作者による所要時間の見積もりには読解と通常の操作ミスを含みますが、初心者の所要時間を調査したものではありません。

指定された試行用予約だけを追加し、そのワークショップの places_left を更新します。競合の練習では、自分の試行用予約の削除を明示的に許可します。元の予約、ワークショップの ID・タイトル・定員・メモ、テーブル定義、準備済みツールを保全してください。サーバー設定の変更やテーブル作成はしません。この実習は 1 データベース上の PostgreSQL の READ COMMITTED 分離レベルを使います。決済、再試行、複数ワークショップ間のデッドロックは範囲外です。

通常のグループ予約を行う

このステップでは、実際のグループ予約の確定と、それが消費する席数を対応させます。

./desk status

ワークショップ 101 は定員 5、元の予約が 1 席、残席が 4 です。102 は 1 席で予約はありません。101 の original-family をそのまま保ってください。

Desk A に切り替えます。Desk A workshops=> プロンプトは、PostgreSQL の psql クライアント内で、データベース workshops に接続していることを意味します。ここにはシェルコマンドではなく SQL を入力します。セミコロンで SQL 文を終えます。接続はデータベースサーバーとの対話で、pg_backend_pid() はそのサーバープロセスを識別します。Desk B の値が異なれば、独立した接続だと分かります。

SELECT pg_backend_pid(), current_user;

ユーザーは workshop_write で、プロセス ID は変わります。BEGIN はトランザクションを開始します。変更をまとめ、COMMIT で同時に見えるようにする単位です。それまでは、別の接続は新しい予約を見られません。ROLLBACK は未コミットの変更を取り消します。この最初の例では、他の受付はワークショップ 101 を販売していません。

RETURNING は、UPDATE が実際に変更した行を表示します。2 席を引き、対応する 2 席の予約を同じトランザクションで挿入します。テキストには単一引用符を使い、リクエストコードが予約を一意に識別します。

BEGIN;
UPDATE workshops
SET places_left = places_left - 2
WHERE workshop_id = 101
RETURNING workshop_id, places_left;
INSERT INTO bookings (request_code, workshop_id, seats, desk)
VALUES ('normal-group', 101, 2, 'A');

更新は 101 | 2 を返します。プロンプトには、開いているトランザクションを示す * が含まれます。Workshop Desk に切り替え、コミット済み台帳を確認します。

./desk status

101 は、まだ残席 4 と元の予約だけを表示します。Desk A に戻り、終了します。

COMMIT;

Workshop Desk に切り替えます。

./desk status

101 は残席 2、予約済み 3 席になりました。元の 1 席と、今回のグループの 2 席です。実際の予約は normal-group|101|2|A です。成功したトランザクションは capacity = places_left + booked_seats を保ちます。最初のチェックはこれらの業務上の事実を読み、入力方法を推測するものではありません。

トランザクション内で SQL を打ち間違えると、プロンプトが ! になることがあります。トランザクションは失敗状態になり、ROLLBACK; まで後続の SQL は失敗します。同じ受付でロールバックし、Workshop Desk で ./desk status を確認して、コミットされなかった作業だけを再試行します。コミット済みのリクエストを、確認せず繰り返さないでください。リクエストコードの競合はエラーであり、2 件目の予約確定ではありません。

古い空席情報に基づく判断を再現する

このステップでは、意図的に不正な練習台帳を作り、非負の残数だけでは不十分な理由を観察し、影響をワークショップ 102 だけに限定します。

Desk A に切り替えます。この実験は、条件付きの席確保を計算せず、古い数値を意図的に書き込みます。最終的な予約方法として使わないでください。分離レベル READ COMMITTED は、各文の開始時にコミット済みデータを見せます。先の SELECT で、その結果が予約されるわけではありません。

BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT workshop_id, places_left FROM workshops WHERE workshop_id = 102;

Desk B に切り替えます。プロセス ID が Desk A と異なることを確認し、独自のトランザクションを開始して同じ席を読みます。

SELECT pg_backend_pid(), current_user;
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT workshop_id, places_left FROM workshops WHERE workshop_id = 102;

両 SELECT は残席 1 を返します。どちらの SELECT も席を確保していません。Desk A に切り替え、先の数値に基づく判断を保存します。

UPDATE workshops SET places_left = 0 WHERE workshop_id = 102;
INSERT INTO bookings (request_code, workshop_id, seats, desk)
VALUES ('unsafe-A', 102, 1, 'A');

A のトランザクションは開いたままにします。Desk B に切り替え、この UPDATE だけを入力します。

UPDATE workshops SET places_left = 0 WHERE workshop_id = 102;

まだプロンプトが戻りません。想定されたロック待ちであり、接続の故障ではありません。A はこの行を変更し、トランザクション終了まで行ロックを保持しています。Workshop Desk に切り替えます。

./desk waits

実際の Desk B のバックエンドの wait_event_type は Lock で、blocked_by は A のプロセス ID を含みます。ID は変わります。観察ツールは PostgreSQL の活動を読むだけで、アニメーションやタイマーがこの状態を作っているわけではありません。

Desk A に切り替えます。

COMMIT;

Desk B に切り替えます。UPDATE が終了します。WHERE 条件はワークショップだけを指定しているため、すでにゼロの残数を、再びゼロに設定できます。予約確定を追加し、コミットします。

INSERT INTO bookings (request_code, workshop_id, seats, desk)
VALUES ('unsafe-B', 102, 1, 'B');
COMMIT;

Workshop Desk に切り替えます。

./desk status

ワークショップ 102 は定員 1、残席 0、予約済み 2 席になりました。unsafe-A と unsafe-B が両方存在します。残数はデータベースの非負チェックを満たしても、1 人の席がありません。待機するだけでは、古い情報に基づく業務判断は直りません。次のステップでは、明示的に使い捨てとしたこの試行だけを削除し、判断方法を置き換えます。ステップのチェックは、この練習状態または正しく修復された最終状態を受け入れます。調査の証明にターミナル履歴を使うことはありません。

予約確定の前に席を確保する

このステップでは、予約確定を書き込む前に、実際の条件付きの席確保を必須にし、キャンセルした待機リクエストから復旧します。

Desk A に切り替えます。DELETE は WHERE 条件で選んだ行だけを削除します。IN は 2 つの使い捨てのリクエストコードを指定します。元の予約や通常予約は削除しません。同じトランザクション内で、練習用ワークショップ 102 だけを復元します。

BEGIN;
DELETE FROM bookings WHERE request_code IN ('unsafe-A', 'unsafe-B');
UPDATE workshops SET places_left = 1 WHERE workshop_id = 102;
COMMIT;

まず別のワークショップ 104 で、ロック待ちのキャンセルを練習します。A は ROLLBACK で変更を取り消すため、この試行で予約は作りません。

BEGIN;
UPDATE workshops SET places_left = 0 WHERE workshop_id = 104;

Desk B に切り替えます。

BEGIN;
UPDATE workshops SET places_left = places_left - 1
WHERE workshop_id = 104 AND places_left >= 1
RETURNING workshop_id;

B の待機中に、Desk B で Ctrl+C を押します。PostgreSQL は canceling statement due to user request と SQLSTATE 57014 を報告します。SQLSTATE は、PostgreSQL の 5 文字のエラー分類です。文をキャンセルすると、この明示的なトランザクションは失敗状態になります。通常の空の結果との違いを確かめます。

SELECT 1;

これは想定されたエラーで、current transaction is aborted、SQLSTATE 25P02 です。Desk B で復旧します。

ROLLBACK;

Desk A に切り替え、練習用の変更を取り消します。

ROLLBACK;

今度はワークショップ 102 の本当の最後の席を保護します。WHERE 条件は、UPDATE 自体の一部として十分な残席を確認します。競合更新の待機後、READ COMMITTED は更新後の行に対してこの条件を再評価します。RETURNING は、このトランザクションが実際に席を確保したかを知らせます。

Desk A で実行します。

BEGIN ISOLATION LEVEL READ COMMITTED;
UPDATE workshops SET places_left = places_left - 1
WHERE workshop_id = 102 AND places_left >= 1
RETURNING workshop_id, places_left;

A は 102 | 0 を受け取り、席を確保しました。行が返ったからこそ、対応する予約確定を作ります。

INSERT INTO bookings (request_code, workshop_id, seats, desk)
VALUES ('safe-last-A', 102, 1, 'A');

未コミットのままにします。Desk B に切り替えます。

BEGIN ISOLATION LEVEL READ COMMITTED;
UPDATE workshops SET places_left = places_left - 1
WHERE workshop_id = 102 AND places_left >= 1
RETURNING workshop_id, places_left;

B は待ちます。Workshop Desk に切り替え、実際の待機を読みます。

./desk waits

行ロックは、無関係なワークショップを止めるべきではありません。そのまま Workshop Desk で、短時間の 3 つ目の接続を開きます。./db --desk A は同じ準備済みのデータベースログインを使いますが、この一時的なシェル呼び出しは、開いている Desk A タブとは独立しています。引用符付きヒアドキュメントは、閉じる SQL までの全行をクライアントに渡し、その後閉じます。閉じるマーカーは単独行にします。予約内の C は、この 3 番目のリクエストを記録します。

./db --desk A <<'SQL'
BEGIN;
UPDATE workshops SET places_left = places_left - 1
WHERE workshop_id = 103 AND places_left >= 1
RETURNING workshop_id, places_left;
INSERT INTO bookings (request_code, workshop_id, seats, desk)
VALUES ('other-workshop', 103, 1, 'C');
COMMIT;
SQL

102 がまだ保持されている間に、103 | 2 を返してコミットします。Desk A に切り替えます。

COMMIT;

Desk B に切り替えます。UPDATE は今度はゼロ行と UPDATE 0 を返します。新しいゼロ残数は places_left >= 1 を満たしません。これは席を確保しなかった正常な SQL 文であり、SQL エラーではありません。予約確定を挿入してはいけません。B のトランザクションを終えます。

ROLLBACK;

Workshop Desk に切り替えます。

./desk status

102 には safe-last-A|102|1|A の 1 件だけがあり、残席はゼロです。103 には other-workshop|103|1|C があり、残席は 2 です。104 は元の 1 席と予約なしのままで、101 は通常のグループと元の家族予約を保ちます。すべてのワークショップで、残席と予約済み席の合計が定員に一致します。

通常の入力ミスによる新しい試行用の誤予約では、まずその受付の開いているトランザクションを ROLLBACK します。この練習環境では、自分の誤ったコードだけを削除し、そのワークショップの残数だけを 1 トランザクションで再計算できます。たとえば 103 で typo-trial を誤ってコミットした場合、Desk A で以下を実行し、意図したリクエストを再試行する前に状態を確認します。サブクエリは実際の予約席数を合計し、COALESCE(..., 0) は予約がなければゼロを使います。これは明示的な練習用の復旧であり、予約アルゴリズムの一部ではありません。

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

この誤りを作っていなければ、例を実行しても変わりません。元の予約や正当な指定予約を消すために使わないでください。次の実習では、席確保と予約確定を再利用可能な 1 文でつなぎ、空の席確保の後に呼び出し側が誤って挿入しないようにします。

まとめ

実際の通常予約を作成し、古い読み取りに基づく過剰予約を再現し、条件付き UPDATE と同じトランザクション内の予約確定で最後の席を保護しました。ネイティブなロック観察では、競合する受付が待つ一方、別のワークショップは処理を続けることが分かりました。待機中の文をキャンセルし、失敗状態のトランザクションを ROLLBACK で復旧する練習もしました。最終台帳は元の予約を保ち、確定済みの各席と残席数が整合しています。