Skip to content
Published on

トランザクション分離レベル 完全ガイド: データベースではなくアプリケーションが担う部分

🇰🇷KO🇺🇸EN🇯🇵JA🇨🇳ZH
Split View필사 모드
シェア
Authors

はじめに

このブログにはすでにトランザクション分離レベルと実際の異常現象があります。標準の四つのレベルと三つの異常現象、スナップショット分離、write skewを二つのセッションのSQLで見せる記事です。分離レベルの理論を扱った記事と呼べます。

この記事は、その上に重ねる運用契約編です。分離レベルを正確に理解しているチームでも、サービスでは依然として事故を起こします。理由は、分離レベルがデータベース側の半分しか受け持たないからです。残りの半分はアプリケーションの仕事です。直列化失敗が起きたとき誰が再試行するか、ロックはどの強さでかけるべきか、キューテーブルは複数の消費者でどう分け合うか、そしてトランザクションを長く開いたままにするとデータベースに何が起きるか。この記事はそのリストを扱います。

基準エンジンはPostgreSQL 18です。分離レベルの実際の動作はエンジンごとに大きく異なるため、MySQL 8.4 InnoDBと異なる点は7節でエンジン名を付けて別に区別しました。二つのエンジンをひとまとめにした説明は、無いほうがましです。

1. PostgreSQLが実際に実装しているもの

まず事実関係を正確に押さえてから始めます。ここに書いた内容はすべてPostgreSQL 18の文書で確認したものです。

PostgreSQLは標準の四つの分離レベルをすべて要求はできても、内部的に実装されているのは三つだけです。文書の表現そのままに言えば「PostgreSQLのRead Uncommittedモードは、Read Committedのように動作する」とされています。これは、多version並行性制御の構造に標準の分離レベルを対応させる唯一の合理的な方法だからです。つまりPostgreSQLではdirty readはどの設定でも発生しません。

二つ目に重要な事実です。文書の分離レベル表は、ファントムリードの項目に「標準では許容されるが、PostgreSQLでは発生しない」と記しています。文書本文はこう説明します。「PostgreSQLのRepeatable Read実装はファントムリードを許しません。標準は特定の分離レベルでどの異常現象が起きてはならないかだけを規定しているため、より強い保証を与えることは許容されます。」

したがって、標準の表をそのまま覚えた人が、PostgreSQLで「Repeatable Readだからファントムが起きるはずだ」と判断すれば誤りです。逆に「Repeatable Readなら安全なはずだ」と判断するのも誤りです。Repeatable Readが防げないのはファントムではなく直列化異常(serialization anomaly)です。文書の定義はこうです。「複数のトランザクションを成功裏にコミットした結果が、それらのトランザクションを一度に一つずつ実行するどの順序によっても説明できない状態。」

レベルdirty readnon-repeatable readphantom readserialization anomaly
Read Uncommitted発生しない発生しうる発生しうる発生しうる
Read Committed発生しない発生しうる発生しうる発生しうる
Repeatable Read発生しない発生しない発生しない発生しうる
Serializable発生しない発生しない発生しない発生しない

基本の分離レベルはdefault_transaction_isolationが決定し、デフォルト値はread committedです。

2. どのレベルを選ぶか

実質的な選択肢は三つあり、それぞれ契約が異なります。

Read Committed — 各文が自分の開始時点のスナップショットを見ます。同じトランザクションの中でも、文ごとに異なるデータを見ることがあります。ほとんどの短いOLTPトランザクションにはこれで十分で、再試行の負担がないことが最大の利点です。ただし「読んで判断してから書く」ロジックをこのレベルで使うには、必ず明示的なロックが必要です。

Repeatable Read — トランザクション全体が一つのスナップショットを見ます。複数のテーブルを読んで一つの一貫したレポートを作る作業に向いています。ただし更新の衝突が起きるとcould not serialize access due to concurrent updateエラーでトランザクションが中断され、アプリケーションが再試行しなければなりません。

Serializable — 文書の表現では「Serializable Snapshot Isolation」という技法で実装されており、スナップショット分離の上に直列化異常の検査を重ねたものです。不変条件が複数の行にまたがるドメイン(残高の合計、座席の重複、在庫の総量)では、明示的なロック設計を置き換えられます。代償は再試行の比率と予測しづらさです。

文書がSerializableについて推奨する項目は、事実上の利用条件です。可能ならトランザクションをREAD ONLYとして宣言すること、トランザクションを短く保つこと、idle in transaction状態を長く置かないこと、コネクションプールで同時接続数を制御すること。読み取り専用のレポートトランザクションならSERIALIZABLE READ ONLY DEFERRABLEを使えます。このトランザクションは、衝突が起こりえないことが確立されるまでブロックされてから始まるため、直列化失敗で中断されることはありません。

-- 再試行なしで完全に一貫したスナップショットが必要な夜間レポート
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE;
SELECT ...;
COMMIT;

3. 直列化失敗の再試行層

Repeatable Read以上を使うと決めたなら、再試行は選択ではなく必須です。文書はこの点を二度強調しています。「このレベルを使用するアプリケーションは、直列化失敗によるトランザクションの再試行に備えなければならない。」そして「直列化失敗を処理する汎用的な方法を備えることが重要である。どのトランザクションが読み書きの依存関係に寄与してロールバックされるべきかを正確に予測するのは非常に難しいからである。」

鍵となる識別子はSQLSTATEの40001です。直列化失敗は常にこの値で返ってきます。再試行層は三つの性質を備える必要があります。

第一に、トランザクション全体を再実行しなければなりません。失敗した文だけを再実行しても意味がありません。スナップショット自体が無効化されているからです。

第二に、再試行回数には上限と指数バックオフが必要です。上限がなければ、競合が激しい瞬間に再試行が再試行を呼ぶ暴走が起きます。

第三に、副作用がトランザクションの外にあってはいけません。トランザクションの中で外部APIを呼び出したりメッセージを発行したりしていれば、再試行のたびに重複して発生します。外部呼び出しはコミット後に回すか、アウトボックスパターンでトランザクション内のテーブル書き込みに変える必要があります。

-- 再試行対象のエラーをサーバー側で確認する方法
-- 40001: serialization_failure, 40P01: deadlock_detected
DO $do$
BEGIN
  -- 実際のアプリケーションでは、このロジックはクライアント層にあるべきである
  PERFORM 1;
EXCEPTION
  WHEN serialization_failure OR deadlock_detected THEN
    RAISE NOTICE 'retryable: %', SQLSTATE;
END;
$do$;

デッドロック(40P01)も同じ再試行層で処理するのが実用的です。両方のエラーとも「もう一度やれば成功しうる」という性質は同じです。

一つ注意点があります。再試行層をデータベース関数の中に置いてはいけません。関数内の例外処理ブロックはサブトランザクションなので、外側のトランザクションのスナップショットはそのままです。再試行は必ずトランザクションを開始した層、つまりアプリケーションコードになければなりません。

4. 行ロックのはしごとSKIP LOCKED

Read Committedで「読んで判断してから書く」ロジックを安全にするには、明示的なロックが必要です。PostgreSQLは四段階の行ロックを提供しており、文書の構文はFOR lock_strength [ OF from_reference ] [ NOWAIT | SKIP LOCKED ]です。

強さの順に整理するとこうなります。

  • FOR UPDATE — 最も強いロック。他のトランザクションのUPDATE、DELETEと、四種類のロックSELECTすべてを阻止します。
  • FOR NO KEY UPDATE — 弱い排他ロック。FOR KEY SHAREは阻止しません。一意インデックスの列に触れないUPDATEが自動的に取るロックでもあります。
  • FOR SHARE — 共有ロック。UPDATE、DELETE、FOR UPDATE、FOR NO KEY UPDATEは阻止しますが、他のFOR SHAREやFOR KEY SHAREは阻止しません。
  • FOR KEY SHARE — 最も弱い共有ロック。DELETEとキー値のUPDATEだけを阻止します。外部キー検査が内部的に使うロックです。

行ロックの衝突関係を表にするとこうなります。X印が衝突です。

要求 → / 保有 ↓KEY SHARESHARENO KEY UPDATEUPDATE
FOR KEY SHAREX
FOR SHAREXX
FOR NO KEY UPDATEXXX
FOR UPDATEXXXX

実務で最も役立つのは強さではなく待機ポリシーです。文書の表現そのままに言えば、NOWAITは選択した行を即座にロックできない場合、待たずにエラーを出します。SKIP LOCKEDは即座にロックできない行を読み飛ばします。文書はSKIP LOCKEDについて「ロックされた行を読み飛ばすことは一貫しないデータビューを提供するため汎用的な作業には適さないが、複数の消費者がキュー形式のテーブルにアクセスする際にロック競合を避けるために使える」と明記しています。

これがデータベースを作業キューとして使う標準パターンです。

-- 複数のワーカーがそれぞれ異なるジョブを拾っていくキュー
BEGIN;

WITH picked AS (
  SELECT id
  FROM jobs
  WHERE status = 'PENDING'
  ORDER BY created_at
  LIMIT 10
  FOR UPDATE SKIP LOCKED
)
UPDATE jobs j
   SET status = 'RUNNING', started_at = now()
  FROM picked p
 WHERE j.id = p.id
RETURNING j.id, j.payload;

COMMIT;

SKIP LOCKEDがなければ、ワーカー10台すべてが同じ最初の行に並びます。あれば、それぞれ別の行を拾います。

文書には落とし穴も一つ書かれています。Read CommittedでORDER BYとロック節を一緒に使うと、結果が並び順から外れることがあります。ソートが先に適用されてからロック待ちが発生し、待ちが解けた時点でソート列の値がすでに変わっている可能性があるためです。Repeatable Read以上では、同じ状況が40001の直列化失敗になります。

5. アドバイザリーロック — データの外にある相互排他

ロックしたい対象が行ではない場合があります。「このバッチは同時に一つしか動いてはいけない」「この外部API呼び出しはテナントごとに一つだけ」といった要件です。このとき、ロックフラグ用の列を作りUPDATEで印を付ける方式は、テーブルのbloatを生み、失敗時の後始末が難しくなります。

PostgreSQLのアドバイザリーロックはこの用途のために設計されています。文書はこれを「アプリケーションが意味を定義するロックであり、システムは使用法を強制しないためアプリケーションが正しく使わなければならない」と説明し、テーブルフラグより速く、テーブルbloatを生まず、セッション終了時に自動的に片付けられるという利点を挙げます。

二つのレベルがあります。セッションレベルは明示的に解放するかセッションが終わるまで保持され、トランザクションのロールバックにも生き残ります。トランザクションレベルはトランザクションが終わると自動的に解放されます。短い相互排他にはトランザクションレベルが安全です。

-- トランザクションレベルのアドバイザリーロック: 獲得できなければ即座にfalseを返す
BEGIN;
SELECT pg_try_advisory_xact_lock(hashtext('nightly-settlement'));
-- trueなら進行、falseなら別のインスタンスがすでに動いているので終了
COMMIT;

文書が警告する落とし穴が一つあります。LIMITと一緒に使うとき、SQLの評価順序のせいで意図より多くのロックを獲得してしまうことがあります。必ずサブクエリの中でLIMITを先に適用し、その外側でロック関数を呼び出す必要があります。

6. 長時間トランザクションという本当のコスト

分離レベルの議論で最も抜け落ちやすい話題です。トランザクションを長く開いたままにすると何が起きるか。

PostgreSQLのMVCCは、更新するとき既存の行バージョンをそのまま残し、新しいバージョンを作ります。古いバージョンは「このバージョンを見られるトランザクションが一つもなくなったとき」に初めて回収できます。ところが開いたままのトランザクションが一つでもあると、そのトランザクションのスナップショットより後に死んだすべての行バージョンがデータベース全体で回収されなくなります。

結果はこうなります。朝に開いたまま昼食に行ってしまった一つのpsqlセッションのせいで、まったく無関係な注文テーブルの死んだ行が積み上がり、インデックスが膨らみ、順次スキャンが読むべきページが増え、クエリ全体が遅くなります。そしてトランザクションIDの消費が続けば、wraparound防止のための強制vacuumまで発生します。

防衛線は三つのタイムアウトです。PostgreSQL 18の文書によれば、三つの値はすべてデフォルトが0、つまり無効です。デフォルト設定のままのサーバーには、この防衛線がそもそも存在しないということです。

  • statement_timeout — 指定した時間を超える文を中断します。
  • lock_timeout — テーブル、インデックス、行などのロックを獲得しようと待つ時間が指定時間を超えたら文を中断します。
  • idle_in_transaction_session_timeout — トランザクションを開いたままクライアントの命令を待って遊んでいるセッションを終了します。

PostgreSQL 17からはtransaction_timeoutもあります。トランザクション全体が指定時間を超えるとセッションを終了し、デフォルト値はやはり0です。

-- データベース単位のデフォルト。バッチ用アカウントは別途緩和する
ALTER DATABASE appdb SET statement_timeout = '30s';
ALTER DATABASE appdb SET idle_in_transaction_session_timeout = '60s';
ALTER ROLE batch_worker SET statement_timeout = '30min';

現在開いている古いトランザクションはこうやって探します。

SELECT pid, state, now() - xact_start AS xact_age,
       now() - state_change AS state_age,
       wait_event_type, wait_event, left(query, 60) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND now() - xact_start > interval '5 minutes'
ORDER BY xact_start;

7. MySQL 8.4 InnoDBと異なる点

ここからはエンジン名を付けて読む必要があります。二つのエンジンの違いは些細なものではありません。

基本の分離レベルが異なります。MySQL 8.4の文書によれば、InnoDBの基本分離レベルはREPEATABLE READです。PostgreSQL 18のデフォルトはRead Committedです。つまり同じアプリケーションコードを両エンジンにつなぐと、基本の動作が異なります。

Repeatable Readのロック動作が異なります。InnoDBではSELECT ... FOR UPDATE、SELECT ... FOR SHARE、UPDATE、DELETEは検索条件によって動作が変わります。一意インデックスに一意な値を指定した検索なら、見つかったインデックスレコードだけをロックし、その手前のギャップはロックしません。それ以外の検索条件なら、スキャンしたインデックス範囲をギャップロックまたはネクストキーロックでロックし、他のセッションがその範囲に挿入するのを防ぎます。つまりInnoDBはロック読み取りでギャップを施錠しファントムを防ぎます。PostgreSQLにはギャップロックという概念そのものがなく、スナップショットでファントムを防ぎます。

Read Committedの動作も異なります。InnoDBのRead Committedではギャップロックが無効になり、外部キー制約検査と重複キー検査にのみ残ります。またUPDATE文がすでにロックされた行に出会うと「準一貫読み取り(semi-consistent read)」を行い、最新のコミット済みバージョンをMySQL層に返し、その値でWHERE条件の一致を判定します。PostgreSQLにはこの動作はありません。

Read Uncommittedの意味が異なります。InnoDBのRead UncommittedではSELECTはロックなしで実行され、以前のバージョンの行が使われることがあります。つまりダーティリードが実際に発生します。PostgreSQLでは発生しません。

Serializableの実装が異なります。InnoDBのSerializableはautocommitが切れているとき、すべての普通のSELECTを暗黙的にSELECT ... FOR SHAREに変換します。つまりロックベースです。PostgreSQLのSerializableはロックではなく直列化異常の検査に基づいており、衝突時には40001で中断します。

分離レベルを指定するシステム変数の名前も異なります。MySQLはtransaction_isolation、PostgreSQLはdefault_transaction_isolationです。

8. 観測 — 常に何を見るべきか

並行性の問題は再現が難しいので、指標をあらかじめ有効にしておくほうが賢明です。

待機イベント。pg_stat_activityのwait_event_typeは、バックエンドが何を待っているかを教えてくれます。文書が定義する値には、Lock(SQLから見える対象への重いロック)、LWLock(内部データ構造を守る軽量ロック)、BufferPin、IO、IPC、Client、Timeout、Activity、Extensionがあります。並行性の調査ではLockが要です。

デッドロック検出。PostgreSQLはロック待ちのたびにデッドロックを検査するわけではありません。コストが高いからです。代わりにdeadlock_timeoutだけ待ってから検査します。デフォルトは1秒です。log_lock_waitsを有効にしておくと同じ時間を基準にロック待ちのログが残るため、デッドロックには至らなかった長い待ちまで捕捉できます。

-- ロック待ちをログに残す(deadlock_timeoutを基準にする)
ALTER SYSTEM SET log_lock_waits = on;
SELECT pg_reload_conf();

ロックスロット。max_locks_per_transactionのデフォルトは64です。文書は「64というデフォルト値は歴史的に十分であることが証明されているが、一つのトランザクションで多くのテーブルに触れるクエリがあるなら値を上げる必要があるかもしれない」と述べ、子テーブルの多い親テーブルへのクエリを例に挙げます。パーティションが数百個あるテーブルを照会するワークロードで実際にぶつかる限界です。

Serializable専用の指標。Serializableを使うなら、述語ロック(predicate lock)関連のパラメータも見る必要があります。max_pred_locks_per_transactionのデフォルトは64、max_pred_locks_per_pageのデフォルトは2、max_pred_locks_per_relationのデフォルトは-2です。述語ロックが不足すると、ロック単位がページから関係全体へ昇格し、直列化失敗が急増します。

クイズ: 理解度を確認しましょう

クイズ1: PostgreSQLでREAD UNCOMMITTEDとしてトランザクションを開始しました。他のトランザクションがまだコミットしていない値を読めるでしょうか。

正解: 読めません。PostgreSQLのRead Uncommittedは、Read Committedのように動作します。

説明: 文書は「PostgreSQLでは四つの標準分離レベルをすべて要求できるが、内部的には三つしか実装されておらず、Read Uncommittedモードは Read Committedのように動作する」と明記しています。理由は、MVCC構造に標準の分離レベルを対応させる唯一の合理的な方法だからです。他のエンジンとは異なります。MySQL 8.4 InnoDBのRead UncommittedではSELECTがロックなしで実行され、以前のバージョンの行を見られるため、ダーティリードが実際に発生します。移植性を考えるなら、この違いを必ず知っておく必要があります。

クイズ2: 下のコードの問題は何でしょうか。
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  INSERT INTO orders (...) VALUES (...);
  -- ここで決済ゲートウェイへのHTTP呼び出し
  UPDATE inventory SET qty = qty - 1 WHERE sku = 'A-1';
COMMIT;

正解: トランザクションの中に外部API呼び出しが入っています。直列化失敗で再試行すると決済が重複します。しかもHTTPの応答を待っている間トランザクションが開いたままなのでVACUUMを妨げます。

説明: Serializableは40001エラーでトランザクションが中断されることがあり、アプリケーションはトランザクション全体を再試行しなければなりません。外部呼び出しがトランザクションの中にあると、再試行するたびに再び呼び出されます。解決策は、外部呼び出しをトランザクションの外に出すか、アウトボックステーブルにイベントを記録してコミット後に別のワーカーが送信する構造に変えることです。二つ目の問題も深刻です。ネットワーク遅延がわずか数秒でも、その間データベース全体の死んだ行の回収が止まります。文書がSerializable使用時に「必要以上のものを一つのトランザクションに入れないこと」と「idle in transaction状態を必要以上に置かないこと」を推奨しているのはこのためです。

クイズ3: ワーカー10台が同じjobsテーブルからジョブを取得していますが、処理量はワーカー1台のときと同じです。何が抜けているでしょうか。

正解: SKIP LOCKEDです。

説明: SELECT ... FOR UPDATE LIMIT 1だけを使うと、すべてのワーカーが同じ最初の行をロックしようと並びます。最初のワーカーが処理している間、残りの9台は待つので、事実上直列実行になります。文書はSKIP LOCKEDについて「複数の消費者がキュー形式のテーブルにアクセスする際にロック競合を避けるために使える」と明記しています。

SELECT id FROM jobs
WHERE status = 'PENDING'
ORDER BY created_at
LIMIT 10
FOR UPDATE SKIP LOCKED;

ただし同じ文書が警告するように、SKIP LOCKEDは一貫しないビューを提供するため汎用的な照会に使ってはいけません。キューの消費のように「どの行でも一つ拾えればよい」場合にのみ適しています。

クイズ4: 開発サーバーではうまく動いていたバッチが、本番では他のトランザクションを数分間ずつ止めます。分離レベルを下げれば解決するでしょうか。

正解: いいえ。分離レベルとロック待ちは別の問題です。

説明: 分離レベルは「何を見られるか」を決め、ロックは「何を待たなければならないか」を決めます。Read Committedに下げても、UPDATEが同じ行に触れれば依然として待ちます。診断の順序はこうです。まずpg_stat_activityでwait_event_type = 'Lock'のセッションを探し、そのセッションが何を待っているかを確認します。原因はたいてい、バッチが一つのトランザクションであまりに多くの行を更新していることです。対応はバッチをチャンクに分け、各チャンクを別々のトランザクションとしてコミットすること、予防はlock_timeoutを設定して無限の待ちを防ぐことです。デフォルト値の0は無限の待ちを意味します。

クイズ5: Serializableに切り替えたら40001エラーが急増しました。統計を見るとほとんどのクエリがSeq Scanです。関係があるでしょうか。

正解: あります。順次スキャンは関係レベルの述語ロックを引き起こします。

説明: PostgreSQLのSerializableは読み取ったデータに述語ロックをかけて読み書きの依存関係を追跡します。インデックススキャンならロック単位は狭いですが、順次スキャンならテーブル全体がロック対象になるため、無関係なトランザクション同士でも衝突と判定されます。文書自体がSerializableの性能に関する推奨事項として「順次スキャンを避けるよう実行計画を最適化すること」を挙げ、random_page_costとcpu_tuple_costの調整に言及しています。合わせて見るべきは述語ロックの上限です。max_pred_locks_per_transactionのデフォルト64、max_pred_locks_per_pageのデフォルト2を超えると、ロック単位が昇格して衝突がさらに増えます。

おわりに

分離レベルは「上げれば安全になるつまみ」ではありません。データベースとアプリケーションの間の契約です。契約の条項はこうです。データベースは、定められた異常現象が起きないことを保証する。その代わりアプリケーションは、直列化失敗を再試行し、トランザクションを短く保ち、副作用をトランザクションの境界の外に出さない。

この契約のアプリケーション側の条項を守らなければ、分離レベルをどれだけ上げても事故は続きます。逆に条項を守れば、Read Committedに明示的なロックを数行加えるだけで、ほとんどのドメインは安全になります。

二つのセッションで実際の異常現象を再現してみたいなら、PostgreSQLプレイグラウンドで試すことができます。

参考資料

関連記事

Discuss on Twitter • View on GitHub