Skip to content
Published on

トランザクション分離レベルと実際の異常現象 — 標準の定義が実装と食い違う地点

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

はじめに — 分離レベルを上げたのになぜデータが食い違うのか

並行性のバグに出会うと、よく出てくる処方があります。「分離レベルをRepeatable Readに上げろ」。そしてかなりの場合、その処方は問題を直しません。残高がマイナスになり、在庫が超過販売され、重複予約が出続けます。

理由は二つです。第一に、分離レベルの表で学んだ定義と実際の実装が違います。PostgreSQLのRead Committedは標準が許容するdirty readをそもそも作れず、Repeatable Readは標準が許容するファントムもほとんど防ぎます。MySQL InnoDBのRepeatable Readはまた別の動き方をします。第二に、四つのレベルのどれもが捕まえられない異常現象が一つあります。実務の並行性バグの大半は、まさにそれです。

この記事では標準の表をまず整理したうえで、その表が実際のデータベースでどう崩れるのかを二つのセッションのSQLで確認します。

標準が定義した四つのレベルと三つの異常現象

ANSI SQL標準は分離レベルを「何を許容するか」で定義しました。アルゴリズムではなく禁止リストとして定義したという点が、のちに混乱の種になります。

  • dirty read(ダーティリード) — コミットされていない他のトランザクションの変更を読むこと
  • non-repeatable read(反復不能読み取り) — 同じ行を二度読んだのに値が変わること
  • phantom read(ファントムリード) — 同じ条件で二度検索したのに行の数が変わること

ここに、標準にはないものの実務では最も重要な一つを加えて整理すると、次のようになります。

分離レベルdirty readnon-repeatable readphantom readwrite skew
Read Uncommitted標準上は許容 (PostgreSQLでは不可)許容許容許容
Read Committed不可許容許容許容
Repeatable Read不可不可標準上は許容 (PostgreSQLでは不可)許容
Serializable不可不可不可不可

表の右端の列が核心です。Serializableを除くすべてのレベルがwrite skew(ライトスキュー)を許容します。そして大半のサービスはRead Committedで動いています。

現在の設定はこう確認します。

-- PostgreSQL
SHOW default_transaction_isolation;   -- デフォルト値: read committed
SELECT current_setting('transaction_isolation');

-- トランザクション単位で指定
BEGIN ISOLATION LEVEL REPEATABLE READ;
-- または
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- MySQL
SELECT @@transaction_isolation;   -- デフォルト値: REPEATABLE-READ
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

デフォルト値が違うという点をまず認識しておく必要があります。PostgreSQLはRead Committed、MySQLはRepeatable Readです。MySQLからPostgreSQLへ移行するとき、静かに挙動が変わる最初の地点がここです。

PostgreSQLのRead Committedは標準と違う

PostgreSQLはロックではなくMVCCで分離を実装します。すべての読み取りはスナップショットを通して行われ、スナップショットにはコミット済みのバージョンだけが見えます。ですからRead Uncommittedを要求してもRead Committedとして動作します。構文は受け付けますが、dirty readは構造的に不可能です。

BEGIN ISOLATION LEVEL READ UNCOMMITTED;
SHOW transaction_isolation;

 transaction_isolation
-----------------------
 read uncommitted

設定値はそのまま見えますが、内部の動作はRead Committedと同じです。この事実を知らずに「Read Uncommittedに下げて性能を上げよう」と提案する場合がありますが、PostgreSQLでは何の効果もありません。

Read Committedではスナップショットが文単位で取り直されます。トランザクションの中でもSELECTを二度実行すれば、互いに異なるスナップショットを見ます。

-- セッションA
BEGIN;
SELECT balance FROM accounts WHERE id = 1;   -- 10000

-- セッションB
UPDATE accounts SET balance = 5000 WHERE id = 1;
COMMIT;

-- セッションA (同じトランザクションの中)
SELECT balance FROM accounts WHERE id = 1;   -- 5000  <-- 値が変わった
COMMIT;

ここまでは教科書どおりです。ところがRead Committedには、あまり知られていない動作がもう一つあります。UPDATEが他のトランザクションがロックした行に出会うと待機し、相手がコミットすると更新後の最新バージョンを読み直してWHERE条件を再評価します。

-- セッションA
BEGIN;
UPDATE accounts SET balance = balance - 3000 WHERE id = 1;
-- コミットせずに待機

-- セッションB
BEGIN;
UPDATE accounts SET balance = balance - 4000 WHERE id = 1;   -- 待機

-- セッションA
COMMIT;

セッションBはロックが解けるとbalanceが7000になった最新の行を読み直し、3000に更新します。結果は3000、つまり二つの差し引きがどちらも反映されます。balance = balance - 4000のように現在の値を基準にする更新では、この再評価のおかげでlost updateが発生しません。

問題は、アプリケーションが値を読んで計算したあと定数として書き込むパターンです。

-- アプリケーションがよくやること
SELECT balance FROM accounts WHERE id = 1;   -- 10000を読む
-- アプリケーション側で 10000 - 4000 = 6000 を計算
UPDATE accounts SET balance = 6000 WHERE id = 1;   -- 定数で上書き

この場合、セッションAの差し引きは跡形もなく消えます。再評価はWHERE条件にだけ適用され、SET句の定数はそのまま使われるからです。ORMがエンティティ全体を読み込んでフィールドを変えて保存する方式が、まさにこのパターンです。

Repeatable Readは実はスナップショット分離である

PostgreSQLのRepeatable Readは、トランザクションの最初の文の時点でスナップショットを一度だけ取り、最後までそれだけを見ます。これは標準のRepeatable Readより強いものです。同じ行の値が変わらないのはもちろん、条件に合う行の数も変わりません。つまりファントムも見えません。

-- セッションA
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM orders WHERE status = 'pending';   -- 42

-- セッションB
INSERT INTO orders (status) VALUES ('pending');
COMMIT;

-- セッションA
SELECT count(*) FROM orders WHERE status = 'pending';   -- 42  <-- そのまま
COMMIT;

代償は、書き込み衝突が起きるとトランザクションが死ぬことです。スナップショット以降に他のトランザクションがコミットした行を更新しようとすると、PostgreSQLは待ってから再評価する代わりにエラーを出します。

-- セッションA
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;   -- 10000

-- セッションB
UPDATE accounts SET balance = 5000 WHERE id = 1;
COMMIT;

-- セッションA
UPDATE accounts SET balance = balance - 3000 WHERE id = 1;

ERROR:  could not serialize access due to concurrent update
SQLSTATE: 40001

これがRepeatable Readを使うときに必ず知っておくべき契約です。分離レベルを上げると、アプリケーションが再試行する責任を負うことになります。再試行ロジックなしに分離レベルだけを上げるのは、ユーザーに500エラーをより頻繁に見せる変更にすぎません。

Repeatable Readで一緒に現れるエラーがもう一つあります。

ERROR:  could not serialize access due to concurrent delete

読んだ行が他のトランザクションによって削除されたときに発生します。対応は同じく再試行です。

MySQL InnoDBのRepeatable Readとギャップロック

同じ名前ですが、InnoDBのRepeatable Readは違う動きをします。差は二箇所にあります。

第一に、通常のSELECTはトランザクションのスナップショットを見ますが、ロック読み取りと更新文は最新のコミット済みバージョンを見ます。ですから一つのトランザクションの中でSELECTとUPDATEが互いに異なるデータを見ることがあります。

-- セッションA (MySQL, REPEATABLE READ)
BEGIN;
SELECT balance FROM accounts WHERE id = 1;   -- 10000 (スナップショット)

-- セッションB
UPDATE accounts SET balance = 5000 WHERE id = 1;
COMMIT;

-- セッションA
SELECT balance FROM accounts WHERE id = 1;          -- 10000 (依然としてスナップショット)
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;  -- 5000 (最新)
UPDATE accounts SET balance = balance - 3000 WHERE id = 1;
COMMIT;

PostgreSQLなら三番目の文で40001エラーが出ていたはずの状況ですが、InnoDBは静かに進みます。エラーがないことは安全だという意味ではありません。アプリケーションが最初のSELECTの10000を根拠に判断していたなら、その判断はすでに古いものです。

第二に、InnoDBはファントムをギャップロックで防ぎます。ロック読み取りや更新文は、インデックスレコードだけでなくレコードの間の間隔まで施錠します。これがネクストキーロックです。

-- セッションA
BEGIN;
SELECT * FROM orders WHERE user_id = 42 FOR UPDATE;
-- user_id = 42 のインデックス区間とその前後の間隔がロックされる

-- セッションB
INSERT INTO orders (user_id, status) VALUES (42, 'pending');   -- 待機

ギャップロックはファントムを防いでくれますが、代償が大きいです。存在しない値に対してもロックがかかり、ロック範囲が広がってデッドロックの確率が上がります。特に条件カラムにインデックスがないと、InnoDBはなめるすべてのレコードにロックをかけるので、事実上テーブル全体がロックされます。MySQLでデッドロックが頻発するならインデックスから確認すべき理由がこれです。

-- 現在のロック状態の確認 (MySQL 8.0)
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;

ギャップロックが負担で、多くのチームはMySQLでもRead Committedへ下げます。実際に大規模サービスではよくある選択で、この場合ファントムはアプリケーションが引き受けることになります。

write skew — スナップショット分離の本当の穴

いよいよ表の右端の列に進みます。二つのトランザクションが同じ集合を読み、互いに異なる行を更新するとき、スナップショット分離は何の衝突も検知しません。重なる書き込みがないので衝突がないというのは正しいのです。ところが不変条件は壊れます。

当直医の例が古典です。ルールは「当直者が最低一人は残らなければならない」です。

CREATE TABLE doctors (
  id       int PRIMARY KEY,
  name     text NOT NULL,
  on_call  boolean NOT NULL
);
INSERT INTO doctors VALUES (1, 'Kim', true), (2, 'Lee', true);

-- セッションA: キム医師が当直を外れる
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctors WHERE on_call = true;   -- 2, 条件を通過

-- セッションB: イ医師が同時に当直を外れる
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctors WHERE on_call = true;   -- 2, 条件を通過

-- セッションA
UPDATE doctors SET on_call = false WHERE id = 1;
COMMIT;

-- セッションB
UPDATE doctors SET on_call = false WHERE id = 2;
COMMIT;

SELECT count(*) FROM doctors WHERE on_call = true;

 count
-------
     0

二つのトランザクションはどちらも成功しエラーもありませんでしたが、当直者は0人になりました。それぞれ異なる行を更新したので書き込み衝突がなく、スナップショット分離にはこれを捕まえる手段がありません。

同じ構造のバグが実務で繰り返されます。在庫確認後の差し引き、座席の重複予約チェック、口座残高の合計検証、ユニーク制約のない重複登録チェックは、すべてwrite skewです。分離レベルをRead CommittedからRepeatable Readへ上げても一つも直りません。

PostgreSQLのSERIALIZABLEはこれを捕まえます。実装方式はロックではなくSSI、つまりSerializable Snapshot Isolationです。スナップショット分離の上でトランザクション間の読み書き依存を追跡し、直列順序では説明できないパターンを検知すると片方を中断させます。

-- 両セッションともSERIALIZABLEで上のシナリオを繰り返すと
COMMIT;

ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.
HINT:  The transaction might succeed if retried.
SQLSTATE: 40001

注意すべき点が三つあります。

  1. エラーがコミット時点で出ることがあります。トランザクションの途中で何の問題もなくてもCOMMITで失敗しうるので、再試行の範囲はトランザクション全体でなければなりません。
  2. 参加するすべてのトランザクションがSERIALIZABLEであってこそ保証が成立します。片方だけ上げておいても何の意味もありません。
  3. 読み取り専用トランザクションならSET TRANSACTION READ ONLY DEFERRABLEと宣言して、追跡の負担と中断の確率を下げられます。

MySQLのSERIALIZABLEはSSIではありません。通常のSELECTを共有ロック読み取りに変える方式、つまり2相ロックに近いものです。安全ですが、並行性の低下がはるかに大きくなります。

SERIALIZABLE、SELECT FOR UPDATE、楽観的ロックのどれを選ぶか

三つの選択肢があり、それぞれ合う場所が違います。

第一に、ロックすべき行が明確なら、悲観的ロックが最も単純で予測しやすいです。

BEGIN;
SELECT stock FROM products WHERE id = 77 FOR UPDATE;
-- ここから他のトランザクションは同じ行をロックできない
UPDATE products SET stock = stock - 1 WHERE id = 77;
COMMIT;

行が存在しない場合まで防ぎたいならFOR UPDATEでは足りません。存在しない行はロックできないからです。このときはユニーク制約かadvisory lockを使います。

-- アプリケーションレベルの明示的なロック (トランザクション終了時に自動解除)
SELECT pg_advisory_xact_lock(hashtext('reserve-seat:' || 'A12'));

待機時間を制御するにはオプションを併用します。

SELECT * FROM products WHERE id = 77 FOR UPDATE NOWAIT;        -- 即座にエラー
SELECT * FROM products WHERE id = 77 FOR UPDATE SKIP LOCKED;   -- ロックされた行はスキップ

SKIP LOCKEDはキューテーブルの実装に特に有用です。複数のワーカーが同じテーブルから互いに異なるジョブを取っていけます。

-- ジョブキューから安全に一件取ってくる
UPDATE jobs SET status = 'running', picked_at = now()
WHERE id = (
  SELECT id FROM jobs
  WHERE status = 'queued'
  ORDER BY id
  FOR UPDATE SKIP LOCKED
  LIMIT 1
)
RETURNING *;

第二に、衝突がまれでユーザーとの対話がトランザクションの途中に挟まる場合は、楽観的ロックのほうが良いです。画面を開いたままロックを維持することはできないからです。

-- バージョンカラムで衝突だけを検知する
UPDATE documents
SET title = 'new title',
    version = version + 1
WHERE id = 5 AND version = 12;
-- 更新された行が0なら誰かが先に変えたという意味

第三に、不変条件が複数の行や複数のテーブルにまたがっていてロック対象を特定できないとき、SERIALIZABLEが答えです。先の当直医の例がまさにその場合です。ただしスループットが高い経路では中断率が上がるので、中断率を指標として観察しながら適用範囲を狭めるのが良いです。

可能であれば、データベースの制約に置き換えるのが最も堅牢です。分離レベルで守っていた不変条件を制約へ移せば、再試行も不要になります。

-- 重複予約を分離レベルではなく制約で防ぐ
CREATE UNIQUE INDEX uq_seat_reservation
  ON reservations (showtime_id, seat_no)
  WHERE cancelled_at IS NULL;

-- 時間区間の重なりを制約で防ぐ
CREATE EXTENSION IF NOT EXISTS btree_gist;
ALTER TABLE bookings
  ADD CONSTRAINT no_overlap
  EXCLUDE USING gist (room_id WITH =, during WITH &&);

おわりに — 再試行なしの分離レベル引き上げは意味がない

三つだけ覚えておけば十分です。

第一に、Read Committedより上のすべての分離レベルは、アプリケーションに再試行の義務を負わせます。SQLSTATE 40001と40P01を捕まえてトランザクション全体を実行し直すコードがなければ、分離レベルを上げることは障害を増やす変更です。

import time, random
import psycopg

RETRYABLE = {"40001", "40P01"}  # serialization_failure, deadlock_detected

def run_in_tx(conn, fn, max_attempts=5):
    for attempt in range(max_attempts):
        try:
            with conn.transaction():
                return fn(conn)
        except psycopg.errors.Error as e:
            if e.sqlstate not in RETRYABLE or attempt == max_attempts - 1:
                raise
            # 指数バックオフ + ジッター。同じ瞬間の再衝突を防ぐ。
            time.sleep((2 ** attempt) * 0.05 * (0.5 + random.random()))

再試行関数の中にはデータベースの作業だけを入れるべきです。メール送信や外部API呼び出しが混ざっていると、再試行のたびに副作用が繰り返されます。

第二に、大半の並行性バグはnon-repeatable readやphantom readではなくwrite skewです。分離レベルの表で、その列はSerializableでのみ空いています。Repeatable Readへ上げても直らないなら、いま抱えている問題がwrite skewではないかを疑ってください。

第三に、分離レベルで守ろうとしている不変条件があるなら、それを制約として表現できないかをまず検討してください。ユニークインデックスとEXCLUDE制約は分離レベルと無関係に常に成立し、再試行も不要で、あとから合流したチームメンバーがうっかり回避することもできません。

Discuss on Twitter • View on GitHub