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

取り消せない変更

ぐるぐる回る一つの円に、予算が三つ隠れている

TT Labで続きを見る

目標

ロック待ち・文の実行・再試行の回数という3つのバジェットを、psqlの2つのセッションで直接測ります。55P03と57014を分けて受け取り、トランザクション範囲で設定したバジェットが同じ接続に残らないことをバックエンドPIDで証明し、失敗したトランザクションがひとりでに閉じないことまで確認したうえで、バジェット表を書きます。

なぜ重要なのか

読み取りで述べた3つのバジェットは、画面では区別できません。ユーザーには全部「回り続ける円」1つに見え、そのためボタンをもう一度押します。もう一度押した要求は、同じ行を待つ列にもう1つ加わるだけなので、問題を大きくします。顧客にいつ再び押してよいかを伝えるには、どこでどれだけ待ったか、そしてその失敗がもう一度試してよい種類かどうかを、先に知る必要があります。

コードでそれを区別する方法は、エラーの文ではなくSQLSTATEです。ロックを取れなくて切れたのは55P03(lock_not_available)、文そのものがキャンセルされたのは57014(query_canceled)、失敗したトランザクションの中で次のコマンドを送ったのは25P02(in_failed_sql_transaction)です。メッセージの文字列を比較するコードは、ロケールが変わった瞬間に壊れますが、この5文字は変わりません。

バジェットをかける場所も重要です。set_configの3つ目の引数をtrueにすると、そのトランザクションの中でだけ生き、コミットでもロールバックでも消えます。セッションにかけたり、データベースのデフォルトとしてかけたりすると、その接続を次に借りる人の要求まで、短い時間で切られます。その事故は、原因がこのコードにないので、見つけるのがとても難しくなります。

想定所要時間は80分です。期限が切れる前に＋時間を押して、セッションを延長してください。セッションが終わると、/root以下のファイルはすべて消えます。

環境

PostgreSQL 16がPod内ですでに動いています。接続はpsql -X -U lab -d labdbで、ホストは127.0.0.1です(export PGHOST=127.0.0.1)。インターネットや追加のインストールは不要です。

このラボは、スキーマbudgetの中だけで作業します。成果物はすべて/root/budgetの下に置きます。blue顧客の注文9は、テスト用の行なので、実行するたびに、そして採点するたびに、revisionが上がります。その値そのものを覚えておかず、必要なときにもう一度読んでください。採点ツールは、作成したスクリプトを実際にもう一度実行して判定するので、スクリプトは何回実行しても同じ形の出力を出す必要があります。

ロックを待つことは、固定のsleepでは扱いません。ホルダーが実際にロックを握ったことをpg_stat_activityで確認してから進むのがステップ1の課題であり、そのあとのステップはすべてそのスクリプトを使います。

ステップ

  1. /root/budget/01-schema.sqlでスキーマbudgetとテーブルbudget.ordersを作ってください。tenant・id・qty・state・revisionの5列で、主キーは(tenant, id)です。blue顧客の注文1(数量7、pending、revision 3)・注文2(数量4、pending、revision 1)・注文9(数量6、pending、revision 0)の3行を入れます。注文9は、このラボの間ずっとテスト用に使う行なので、バージョンが上がり続けます。そのあと/root/budget/holder.shを作ってください。bash holder.sh <초> <주문번호>(プレースホルダーは秒数と注文番号です)で呼ぶと、バックグラウンドのpsqlセッションがその行をselect ... for updateで握り、pg_sleepで保持したあとコミットします。そのセッションのapplication_nameは必ずlockholderでなければならず、スクリプトは固定のsleepではなくpg_stat_activityをポーリングして、そのセッションがロックを握ったまま眠っていることを確認してはじめて、granted=yesを出力して終了する必要があります。最後にbash holder.sh 3 9を実行し、/root/budget/01-holder.txtにrows・probe_id・granted・holder_secondsの4行を残してください。
  2. /root/budget/attempt.shを作ってください。bash attempt.sh <잠금ms> <문장ms> <주문번호>(プレースホルダーはロックのミリ秒数、文のミリ秒数、注文番号です)で呼ぶと、1つのトランザクションの中でset_configの3つ目の引数をtrueにしてlock_timeoutとstatement_timeoutを設定し、blue顧客のその注文の行をrevision = revision + 1で更新したあとコミットします。成功しても失敗しても終了コードは0で、標準出力にsqlstate・rows・elapsed_msの3行だけを出力します。エラーがなければsqlstateは00000です。そのあとbash holder.sh 5 9で注文9の行を握らせておき、bash attempt.sh 200 1500 9を実行して、結果を/root/budget/02-lockwait.txtにsqlstate・lock_timeout_ms・statement_timeout_ms・waited_ms・rowsの5行で残してください。
  3. 今度は、2つのバジェットを逆にかけてみます。bash holder.sh 5 9で注文9の行を握らせておき、bash attempt.sh 400 150 9を実行してください。ロックのバジェットは400ms、文のバジェットは150msです。結果を/root/budget/03-stmt.txtにinverted_lock_ms・inverted_statement_ms・inverted_sqlstate・normal_sqlstate・first_budgetの5行で残してください。normal_sqlstateはステップ2で受け取ったコードで、first_budgetには、今回先に切った設定の名前(lock_timeoutまたはstatement_timeout)を書きます。
  4. /root/budget/leak.shを作ってください。psqlを1回だけ起動し、同じ接続の中で4回問い合わせます。トランザクションの外で1回(before)、トランザクションの中でset_config('lock_timeout','250ms',true)とset_config('statement_timeout','1200ms',true)をかけたあとに1回(inside)、コミットしたあとに1回(after_commit)、もう一度かけてからロールバックしたあとに1回(after_rollback)です。4行とも키=값/백엔드PID(プレースホルダーはキー、値、バックエンドPIDです)の形で出力します。実行結果を見て、/root/budget/04-noleak.txtにinside・after_commit・after_rollback・pid_same・db_role_settingsの5行を残してください。db_role_settingsは、pg_db_role_settingにlock_timeoutまたはstatement_timeoutが保存されている項目の数です。
  5. /root/budget/retry.shを作ってください。bash retry.sh <시도횟수> <잠금ms> <문장ms> <주문번호>(プレースホルダーは試行回数、ロックのミリ秒数、文のミリ秒数、注文番号です)で呼ぶと、attempt.shを呼び出し、sqlstateが55P03のときだけ、そして残りの試行があるときだけ、少し休んでからもう一度呼びます。試行回数には最初の呼び出しが含まれます。3なら、最初の1回と再試行2回です。それ以外のコードはそのまま伝えて、それ以上は呼びません。試行回数が1から16の整数でなければ、attempt.shを1回も呼ばずに、tries=0・final_sqlstate=22023・outcome=invalidを出力します。正常な経路では、tries・final_sqlstate・outcomeの3行を出力し、outcomeはfinal_sqlstateが00000ならapplied、そうでなければgaveupです。誰も握っていないときに1回、bash holder.sh 6 9のあとに3 200 1500 9で1回、同じホルダーが残っている間に3 400 150 9で1回実行して、結果を/root/budget/05-retry.txtにfree_tries・free_outcome・locked_tries・locked_outcome・locked_sqlstate・cancelled_tries・cancelled_sqlstateの7行で残してください。
  6. /root/budget/idle.shを作ってください。bash idle.sh <주문번호>(プレースホルダーは注文番号です)で呼ぶと、1つの接続の中で、2つのバジェットをトランザクション範囲でかけ、その行をselect ... for updateで握ろうとして失敗したあと、ロールバックする前に任意のSELECTをもう1つ送ってみて、そのあとロールバックし、ロールバックのあとにもう一度SELECTを送り、2つのバジェットの現在の値を読み、最初と最後のバックエンドPIDを比較します。出力はsqlstate・aborted_sqlstate・after_rollback・lock_timeout_after・statement_timeout_after・pid_sameの6行です。bash holder.sh 5 9で握らせておき、bash idle.sh 9を実行して、その6行をそのまま/root/budget/06-idle.txtに残してください。
  7. /root/budget/apply.shを作ってください。bash apply.sh <잠금ms> <문장ms> <주문번호> <승인당시revision>(プレースホルダーはロックのミリ秒数、文のミリ秒数、注文番号、承認当時のrevisionです)で呼ぶと、1つのトランザクションの中で2つのバジェットをかけ、承認当時のrevisionとpending状態が両方合っているときだけ、その行のrevisionを1上げます。出力はsqlstate・rows・verdictの3行で、verdictは次のとおりです。エラーなしで1行が変わったらapplied、エラーなしで0行ならstale、55P03ならlocked、57014ならcancelled、それ以外はerrorです。いま注文9の行のrevisionを読んで、その値で1回(applied)、同じ値でもう1回(stale)、bash holder.sh 5 9のあとに現在のrevisionで200 1500を1回(locked)と400 150を1回(cancelled)実行して、結果を/root/budget/07-verdict.txtにfresh_verdict・stale_verdict・locked_verdict・locked_sqlstate・cancelled_verdict・cancelled_sqlstateの6行で残してください。
  8. 最後に、このラボで決めたバジェットを/root/budget/08-budget.txtに10行で書いてください。lock_timeout_msは200、statement_timeout_msは1500、attemptsは3、retry_pause_msは200です(ステップ2から使ってきた値です)。worst_case_db_wait_msは試行回数×ロックのバジェット、worst_case_elapsed_msは、それに休止時間×(試行回数-1)を足した値です。measured_locked_msとmeasured_sqlstateは、bash holder.sh 5 9で握らせておき、bash attempt.sh 200 1500 9をもう1回実行して得た実測値です。retry_onには再試行するSQLSTATEを、propagateにはそのまま伝えるSQLSTATEを書きます。

参考

別の担当者が行を握っている状態を作る

/root/budget/01-schema.sqlでスキーマbudgetとテーブルbudget.ordersを作ってください。tenant・id・qty・state・revisionの5列で、主キーは(tenant, id)です。blue顧客の注文1(数量7、pending、revision 3)・注文2(数量4、pending、revision 1)・注文9(数量6、pending、revision 0)の3行を入れます。注文9は、このラボの間ずっとテスト用に使う行なので、バージョンが上がり続けます。そのあと/root/budget/holder.shを作ってください。bash holder.sh <초> <주문번호>(プレースホルダーは秒数と注文番号です)で呼ぶと、バックグラウンドのpsqlセッションがその行をselect ... for updateで握り、pg_sleepで保持したあとコミットします。そのセッションのapplication_nameは必ずlockholderでなければならず、スクリプトは固定のsleepではなくpg_stat_activityをポーリングして、そのセッションがロックを握ったまま眠っていることを確認してはじめて、granted=yesを出力して終了する必要があります。最後にbash holder.sh 3 9を実行し、/root/budget/01-holder.txtにrows・probe_id・granted・holder_secondsの4行を残してください。

application_nameは、psqlを呼ぶときに前にPGAPPNAMEを付けると決まります。ロックが握られたかどうかは、pg_stat_activityのwait_eventがPgSleepになったかどうかでわかります。その時点なら、FOR UPDATEはすでに終わっているという意味です。固定のsleepを使うと、環境が混み合っているときに、ロックが握られる前に次のステップが始まって、結果が安定しなくなります。

ロックのバジェットが切ったという証拠を、コードで受け取る

/root/budget/attempt.shを作ってください。bash attempt.sh <잠금ms> <문장ms> <주문번호>(プレースホルダーはロックのミリ秒数、文のミリ秒数、注文番号です)で呼ぶと、1つのトランザクションの中でset_configの3つ目の引数をtrueにしてlock_timeoutとstatement_timeoutを設定し、blue顧客のその注文の行をrevision = revision + 1で更新したあとコミットします。成功しても失敗しても終了コードは0で、標準出力にsqlstate・rows・elapsed_msの3行だけを出力します。エラーがなければsqlstateは00000です。そのあとbash holder.sh 5 9で注文9の行を握らせておき、bash attempt.sh 200 1500 9を実行して、結果を/root/budget/02-lockwait.txtにsqlstate・lock_timeout_ms・statement_timeout_ms・waited_ms・rowsの5行で残してください。

SQLSTATEを文字列で比較するには、psqlに-v VERBOSITY=verboseを指定すると、エラー行にコードが一緒に出ます。エラーメッセージの韓国語・英語の文をgrepする方式は、ロケールが変わると壊れます。waited_msは、attempt.shが出力したelapsed_msをそのまま写せばかまいません。

文のバジェットがロックのバジェットより短いと、どちらが先に切るのか

今度は、2つのバジェットを逆にかけてみます。bash holder.sh 5 9で注文9の行を握らせておき、bash attempt.sh 400 150 9を実行してください。ロックのバジェットは400ms、文のバジェットは150msです。結果を/root/budget/03-stmt.txtにinverted_lock_ms・inverted_statement_ms・inverted_sqlstate・normal_sqlstate・first_budgetの5行で残してください。normal_sqlstateはステップ2で受け取ったコードで、first_budgetには、今回先に切った設定の名前(lock_timeoutまたはstatement_timeout)を書きます。

PostgreSQLのドキュメントが直接述べています。statement_timeoutが0でないとき、lock_timeoutをそれと同じか、より大きくしても意味がありません。文のバジェットがいつも先に切れるからです。2つのコードが違うという事実が重要です。1つは待った末に取得できなかったもので、もう1つは文そのものがキャンセルされたものなので、次にすることが違います。

バジェットが借りてきた接続に残らないことを、同じPIDで証明する

/root/budget/leak.shを作ってください。psqlを1回だけ起動し、同じ接続の中で4回問い合わせます。トランザクションの外で1回(before)、トランザクションの中でset_config('lock_timeout','250ms',true)とset_config('statement_timeout','1200ms',true)をかけたあとに1回(inside)、コミットしたあとに1回(after_commit)、もう一度かけてからロールバックしたあとに1回(after_rollback)です。4行とも키=값/백엔드PID(プレースホルダーはキー、値、バックエンドPIDです)の形で出力します。実行結果を見て、/root/budget/04-noleak.txtにinside・after_commit・after_rollback・pid_same・db_role_settingsの5行を残してください。db_role_settingsは、pg_db_role_settingにlock_timeoutまたはstatement_timeoutが保存されている項目の数です。

psqlを4回呼ぶと接続が4つになり、何も証明されません。そのためPIDも一緒に出力させました。4行のPIDが同じなら、同じ接続です。データベースやロールにデフォルトとして設定しておく方法(alter database ... set)は、このラボでは禁止です。そうすると、他人の要求までそのバジェットを引き継ぎます。

もう一度試してよい失敗は、1種類だけである

/root/budget/retry.shを作ってください。bash retry.sh <시도횟수> <잠금ms> <문장ms> <주문번호>(プレースホルダーは試行回数、ロックのミリ秒数、文のミリ秒数、注文番号です)で呼ぶと、attempt.shを呼び出し、sqlstateが55P03のときだけ、そして残りの試行があるときだけ、少し休んでからもう一度呼びます。試行回数には最初の呼び出しが含まれます。3なら、最初の1回と再試行2回です。それ以外のコードはそのまま伝えて、それ以上は呼びません。試行回数が1から16の整数でなければ、attempt.shを1回も呼ばずに、tries=0・final_sqlstate=22023・outcome=invalidを出力します。正常な経路では、tries・final_sqlstate・outcomeの3行を出力し、outcomeはfinal_sqlstateが00000ならapplied、そうでなければgaveupです。誰も握っていないときに1回、bash holder.sh 6 9のあとに3 200 1500 9で1回、同じホルダーが残っている間に3 400 150 9で1回実行して、結果を/root/budget/05-retry.txtにfree_tries・free_outcome・locked_tries・locked_outcome・locked_sqlstate・cancelled_tries・cancelled_sqlstateの7行で残してください。

再試行は、回数の契約であり、時間の契約ではありません。ロックが解けるまで無限に待つ代わりに、決められた回数だけ試して諦めるのは、待つ要求が溜まること自体が、次の事故だからです。文のキャンセル(57014)を再試行すると、同じ文が同じ時間をもう一度消費します。

失敗したトランザクションは、ひとりでには閉じない

/root/budget/idle.shを作ってください。bash idle.sh <주문번호>(プレースホルダーは注文番号です)で呼ぶと、1つの接続の中で、2つのバジェットをトランザクション範囲でかけ、その行をselect ... for updateで握ろうとして失敗したあと、ロールバックする前に任意のSELECTをもう1つ送ってみて、そのあとロールバックし、ロールバックのあとにもう一度SELECTを送り、2つのバジェットの現在の値を読み、最初と最後のバックエンドPIDを比較します。出力はsqlstate・aborted_sqlstate・after_rollback・lock_timeout_after・statement_timeout_after・pid_sameの6行です。bash holder.sh 5 9で握らせておき、bash idle.sh 9を実行して、その6行をそのまま/root/budget/06-idle.txtに残してください。

エラーが出たトランザクションの中で次のコマンドを送ると、サーバーが拒否します。その拒否にも固有のSQLSTATEがあります。失敗した要求が接続をその状態のままプールに戻すと、次の人は何を送っても同じ拒否を受けます。バジェットがロールバックでも消えるかどうかも、一緒に確認してください。psqlにON_ERROR_STOPを指定すると、最初のエラーで抜けてしまって、そのあとを見られません。

待って失敗したものと、承認が古くなって拒否されたものを分ける

/root/budget/apply.shを作ってください。bash apply.sh <잠금ms> <문장ms> <주문번호> <승인당시revision>(プレースホルダーはロックのミリ秒数、文のミリ秒数、注文番号、承認当時のrevisionです)で呼ぶと、1つのトランザクションの中で2つのバジェットをかけ、承認当時のrevisionとpending状態が両方合っているときだけ、その行のrevisionを1上げます。出力はsqlstate・rows・verdictの3行で、verdictは次のとおりです。エラーなしで1行が変わったらapplied、エラーなしで0行ならstale、55P03ならlocked、57014ならcancelled、それ以外はerrorです。いま注文9の行のrevisionを読んで、その値で1回(applied)、同じ値でもう1回(stale)、bash holder.sh 5 9のあとに現在のrevisionで200 1500を1回(locked)と400 150を1回(cancelled)実行して、結果を/root/budget/07-verdict.txtにfresh_verdict・stale_verdict・locked_verdict・locked_sqlstate・cancelled_verdict・cancelled_sqlstateの6行で残してください。

4つの結果の次の行動は、すべて違います。lockedは少しあとにもう一度試せます。cancelledは同じ文をもう一度送ると同じ時間をもう一度消費します。staleは何回送り直しても永遠に0行なので、新しい承認が必要です。再試行の条件をsqlstate1つにした理由が、ここにあります。

どこでどれだけ待つのかを表に書く

最後に、このラボで決めたバジェットを/root/budget/08-budget.txtに10行で書いてください。lock_timeout_msは200、statement_timeout_msは1500、attemptsは3、retry_pause_msは200です(ステップ2から使ってきた値です)。worst_case_db_wait_msは試行回数×ロックのバジェット、worst_case_elapsed_msは、それに休止時間×(試行回数-1)を足した値です。measured_locked_msとmeasured_sqlstateは、bash holder.sh 5 9で握らせておき、bash attempt.sh 200 1500 9をもう1回実行して得た実測値です。retry_onには再試行するSQLSTATEを、propagateにはそのまま伝えるSQLSTATEを書きます。

この表が、顧客に答える文になります。いつ再び押してよいか、最悪でどれだけかかるかです。ここに書かれた数字は、DB待機と再試行だけを対象にします。接続の確立、複数のSQL、応答の送信はこの表の外にあるので、API全体の時間バジェットと同じだとは言いません。