分離レベルが何を防ぎ、何を防げないのか

目標

読み物で見た表の4つ目の列、つまりどの分離レベルも捕まえてくれないライトスキューを、本物のPostgreSQLで自分で作ってみます。そして、それを防ぐ3つの道具である行ロック、直列化可能レベル、ジョブキュー用のスキップを、順に使ってみます。

なぜ重要なのか

並行性のバグに出会うと、「分離レベルを上げろ」という処方が先に出てきます。このラボは、その処方がいつ効き、いつ効かないかを数字で見せてくれます。ステップ2で失った100ウォンは、ステップ4ではエラーに変わり、ステップ5では、エラーすらなくルールが破られます。

特に注目してほしいのは、同じ900が2回出てくる点です。1回は更新が消えたため、もう1回は更新が拒否されたためです。値だけを見ると区別できず、どちらなのかは、エラーを受け取ったかどうかでしかわかりません。本番では、この違いが、報告される事故と、永遠に気づかれない事故を分けます。

環境

このPodの中でPostgreSQL 16が動いています。接続は、次のとおりです。

export PGPASSWORD=lab
psql -X -q -v ON_ERROR_STOP=1 -h 127.0.0.1 -U lab -d labdb

セッション2つを重ねる必要があるラボです。ターミナルは1つしかないので、片方をバックグラウンドで起動します。トランザクションの中にSELECT pg_sleep(3);を入れておくと、そのセッションがトランザクションを開いたまま待ちます。

psql ... -f /root/txn/lost.sql > /root/txn/lost_a.out 2>&1 &
sleep 1
psql ... -f /root/txn/lost.sql > /root/txn/lost_b.out 2>&1
wait

出力物はすべて/root/txn/の下に置きます。シード用の表(customersなど)には触れません。

ステップ

/root/txn/schema.sqlで、purse、oncall、tasksの3つの表を作って、データを投入します。
/root/txn/lost.sqlで、口座1の更新の消失を再現して、/root/txn/02-lost.txtに残します。
/root/txn/forupdate.sqlで、口座2を行ロックで守って、/root/txn/03-forupdate.txtに残します。
/root/txn/repeatable.sqlで、口座3の直列化エラーを受け取って、/root/txn/04-repeatable.txtに残します。
/root/txn/skew.sqlで、rrチームの当直ルールを破って、/root/txn/05-skew.txtに残します。
/root/txn/serializable.sqlで、ssiチームのルールを守って、/root/txn/06-serializable.txtに残します。
/root/txn/claim.sqlで、2つのワーカーがキューを分け合って取っていくようにして、/root/txn/07-queue.txtに残します。
学んだことをまとめて、/root/txn/08-notes.mdに残します。

参考

表の一覧は\dt、カラム構造は\d purseで見られます。
SELECT balance AS b FROM purse WHERE id = 1 \gsetは、照会結果をpsql変数bに入れます。後で:bとして使います。
エラーにSQLSTATEも一緒に表示するには、ファイルの先頭に\set VERBOSITY verboseを入れます。
各ステップは、開始するときに対象の口座やチームを初期値に戻してください。そうすれば、何回やり直しても同じ結果になります。
よくある間違い1: UPDATE purse SET balance = balance - 100と書くことです。そうするとデータベースが最新の値を読んで計算するので、消失が再現されません。読んだ値をそのまま使う:b - 100である必要があります。
よくある間違い2: 2つのセッションの開始間隔を、待ち時間より長く置くことです。最初のセッションがすでにコミットした後で2つ目が読むと、何も起こりません。

ラボ用の表を3つ作る

/root/txn/schema.sqlを作って投入します。purse(id, owner, balance)に残高1000の口座を3つ(id 1、2、3)、oncall(id, team, name, on_duty)にrrチームの2人(id 1、2)とssiチームの2人(id 3、4)をすべて当直として、tasks(id, state, worker)にqueued状態のジョブ6件(id 1–6)を入れます。

接続はpsql -h 127.0.0.1 -U lab -d labdbで、パスワードはlabです。export PGPASSWORD=labをしておけば、毎回聞かれません。

何回投入しても同じ状態になるように、DROP TABLE IF EXISTSから始めてください。後のステップがこの表を繰り返し元に戻して使うので、冪等性が重要です。

ジョブ6件は、INSERT ... SELECT g, 'queued', null FROM generate_series(1, 6) gで一度に入れられます。

更新の消失を再現する

/root/txn/lost.sqlを作って、口座1から100を引くトランザクションを、2つのセッションで重ねて動かします。残高を先に読んで、3秒休み、読んだ値から100を引いた絶対値を書きます。結果を/root/txn/02-lost.txtに、before、after、expectedの3行で残します。

psqlはSELECT balance AS b FROM purse WHERE id = 1 \gsetで照会結果を変数に入れ、後で:bとして使います。SELECT pg_sleep(3);は、トランザクションを開いたまま3秒待たせます。

片方のセッションをバックグラウンドで起動して、1秒後に2つ目のセッションを動かすと、2つのセッションが重なります。両方ともコミット前の1000を読むので、両方とも900を書きます。2回出金したのに、残高は1回分しか減りません。

動かす前に、口座1を1000に戻しておいてください。そうすれば、何回やり直しても同じ結果になります。

SELECT ... FOR UPDATEで防ぐ

/root/txn/forupdate.sqlを作ります。ステップ2と同じ流れですが、残高を読むときにFOR UPDATEで行をロックします。口座2で2つのセッションを重ねて動かし、結果を/root/txn/03-forupdate.txtに、before、after、expectedの3行で残します。

SELECT balance AS b FROM purse WHERE id = 2 FOR UPDATE \gsetで読むと、その行がロックされます。2つ目のセッションは、最初のセッションがコミットするまで、この照会そのもので待ち、起きた後は更新された値を読みます。

悲観的ロックと呼ぶ理由がこれです。衝突が起きると先に見越して、順番待ちをさせます。その代わり、待ち時間が生まれ、複数の行を別々の順序でロックすると、デッドロックが起きます。

REPEATABLE READはエラーで防ぐ

/root/txn/repeatable.sqlを作ります。ステップ2と同じ流れをBEGIN ISOLATION LEVEL REPEATABLE READ;で始めて、口座3で2つのセッションを重ねて動かします。遅れて書こうとしたセッションが受け取ったSQLSTATEと最終残高を、/root/txn/04-repeatable.txtに、sqlstate、after、expectedの3行で残します。

psqlはデフォルトでは、エラーメッセージにSQLSTATEを表示しません。ファイルの先頭に\set VERBOSITY verboseを入れると、ERROR: 40001: ...のようにコードも一緒に出ます。

ここで残高は900になります。ステップ2と同じ数字ですが、意味は正反対です。ステップ2は出金の1つが静かに消えた900で、ステップ4は出金の1つが拒否された900です。拒否された側はリトライすればよいのですが、消えた側は、リトライする機会すらありません。

REPEATABLE READでも防げないもの

/root/txn/skew.sqlを作ります。rrチームの当直者の数が2人を超えているかを確認してから、自分自身を当直から外すトランザクションで、分離レベルはREPEATABLE READです。2つのセッションが、それぞれid 1とid 2で重なって動くようにして、残りの当直者数を/root/txn/05-skew.txtに、isolation、on_dutyの2行で残します。

条件の判断と更新をpsqlの中でつなげるには、SELECT (count(*) > 1)::text AS ok ... \gsetで真偽を変数に入れ、\if :okと\endifで囲めばよいです。自分の番号はpsql -v me=1のように渡して、SQLで:meとして使います。

2つのセッションは、別々の行を書き換えます。書き込みが重ならないので、スナップショット分離には衝突を検知する根拠がありません。両方とも成功し、当直者は0人になります。これが、読み取りの結果に頼って別の行を書き換えるときに生まれる、ライトスキューです。

SERIALIZABLEでライトスキューを捕まえる

/root/txn/serializable.sqlを作ります。ステップ5と同じ流れをBEGIN ISOLATION LEVEL SERIALIZABLE;で始めて、対象はssiチーム(id 3と4)です。片方が受け取ったSQLSTATEと、残りの当直者数を、/root/txn/06-serializable.txtに、sqlstate、on_dutyの2行で残します。

SERIALIZABLEは、書き込みの衝突だけを見るのではなく、読み取りと書き込みの間の依存関係まで追跡します。2つのトランザクションが、お互いが読んだ集合を書き換えたことに気づき、片方をロールバックします。

ステップ5のファイルをコピーして、分離レベルとチーム名、自分の番号だけを変えれば十分です。SQLSTATEを見るには、ここでも\set VERBOSITY verboseが必要です。

代償はエラーです。このレベルを使うことにしたなら、アプリケーションにリトライが必ずなければなりません。

SKIP LOCKEDでジョブを分けて取る

/root/txn/claim.sqlを作ります。queued状態のジョブをid順に3件選んでFOR UPDATE SKIP LOCKEDでロックし、runningに変えながら、workerに自分の名前を書くトランザクションです。worker-aとworker-bの2つのセッションを重ねて動かして6件をすべて取っていき、結果を/root/txn/07-queue.txtに、worker_a、worker_b、unclaimedの3行で残します。

選ぶ行と書き換える行を1つの文に収めるには、CTEが便利です。

WITH picked AS (
  SELECT id FROM tasks WHERE state = 'queued' ORDER BY id
  FOR UPDATE SKIP LOCKED LIMIT 3
)
UPDATE tasks t SET state = 'running', worker = :'me'
FROM picked WHERE t.id = picked.id;

:'me'は、psql変数を文字列リテラルで囲んで入れます。SKIP LOCKEDがないと、2つ目のワーカーが最初のワーカーがロックした行の前で順番待ちをし、起きた後は、すでに処理された行をもう一度見ることになります。

何が何を防いだのかをまとめる

/root/txn/08-notes.mdに4行以上書きます。ステップ2とステップ4の残高がなぜ同じ900なのに意味が違うのか、ステップ5でREPEATABLE READがなぜ防げなかったのか、ロックと分離レベルをそれぞれどんな状況で選ぶのかを書きます。

本文に쓰기 편향、FOR UPDATE、SKIP LOCKEDが含まれている必要があります(韓国語の語は「ライトスキュー」を意味します)。

特にステップ5をよくまとめておいてください。実務の並行性の事故は、大半がその、表にない4つ目の列で起こります。