Database MVCC & Isolation Levels 完全ガイド 2025: Read Phenomena、Snapshot Isolation、SSI、PostgreSQL/InnoDB 内部構造

はじめに: なぜ isolation level はややこしいのか?

公式ドキュメントの罠

SQL 標準は 4 つの isolation level を定義する: Read Uncommitted、Read Committed、Repeatable Read、Serializable。そして 3 種類の anomaly で区別する。

この表を覚えると理解した気になる。ところが実際の DB はこの表の通りには動かない:

• PostgreSQL の Repeatable Read は Phantom Read を防止する (表によれば許可すべき)。
• MySQL InnoDB の Repeatable Read は Snapshot Isolation であり、Next-Key Lock で phantom も防ぐ。
• Oracle の Serializable は実際には Snapshot Isolation である。
• Write Skew という anomaly は SQL 標準には無いが、Snapshot Isolation の核心的な問題だ。

つまり、「Repeatable Read」という名前が同じでも DB ごとに意味が異なる。本記事では各 DB が実際にどう動作するかを覗いていく。

1. Transaction と ACID の復習

ACID の本当の意味

• Atomicity: すべて成功するかすべて失敗するか。
• Consistency: transaction 後もデータベース不変条件を維持。
• Isolation: 同時実行される transaction 同士が互いに影響を与えないように見える。
• Durability: commit されたデータは永続。

この中で Isolation が最も難しく、繊細だ。完全な Isolation (= Serializable) は transaction を直列実行するのと同じ結果を保証するが、そうすると性能がひどい。そのため様々な 弱い isolation level が提供される。

Isolation Level の本質

Isolation level は 「どの anomaly を許容するか」 の選択である。弱い isolation level は速いがプログラマがバグを避けるために追加の努力をしなければならない。強い isolation level は安全だが性能が落ちる。

Serializable  <------ 安全、遅い
       ^
Snapshot Isolation
       ^
Repeatable Read
       ^
Read Committed
       ^
Read Uncommitted <--- 危険、速い

2. Read Phenomena 完全制覇

Dirty Read: commit されていないデータを読む

例:

-- 初期状態: account = 100

-- T1
BEGIN;
UPDATE account SET balance = 200;  -- まだ commit していない
-- 突然 ROLLBACK

-- T2 (T1 の UPDATE 以降、ROLLBACK 以前)
BEGIN;
SELECT balance FROM account;  -- 200 を返す! (dirty read)

T1 は最終的に rollback されるので、T2 が読んだ 200 は 存在したことのない値 である。これは非常に危険だ。

実務: ほとんどの最新 DB は Read Uncommitted を実際にはサポートしない。設定しても Read Committed のように動作する場合が多い (例: PostgreSQL)。

Non-repeatable Read: 同じ行が変わる

-- T1
BEGIN;
SELECT balance FROM account WHERE id = 1;  -- 100

-- T2
BEGIN;
UPDATE account SET balance = 200 WHERE id = 1;
COMMIT;

-- T1 続き
SELECT balance FROM account WHERE id = 1;  -- 200 (別の値!)
COMMIT;

1 つの transaction 内で同じ SELECT が異なる結果を返す。複雑なレポートクエリで一貫性が崩れる。

Phantom Read: 同じ WHERE 結果が変わる

-- T1
BEGIN;
SELECT COUNT(*) FROM orders WHERE user_id = 5;  -- 10

-- T2
BEGIN;
INSERT INTO orders (user_id, ...) VALUES (5, ...);
COMMIT;

-- T1 続き
SELECT COUNT(*) FROM orders WHERE user_id = 5;  -- 11 (幽霊行!)
COMMIT;

存在しなかった行が突然現れた。これを Phantom と呼ぶ。Non-repeatable read と似ているが、対象が 個別の行ではなく範囲クエリの結果 である点が違う。

Lost Update: 書いた内容が消える

-- 初期: counter = 0

-- T1 と T2 が同時に:
BEGIN;
SELECT counter FROM stats;   -- どちらも 0
-- アプリケーションで +1 計算
UPDATE stats SET counter = 1;
COMMIT;

-- 結果: counter = 1 (一つの update が消えた!)

2 つの transaction が同じ値を読んでそれぞれ update すると、後から書いたものが先に書いたものを上書きする。

解決策:

• SELECT ... FOR UPDATE (悲観ロック)
• UPDATE stats SET counter = counter + 1 (原子的演算)
• Optimistic locking (WHERE version = ?)

Write Skew: Snapshot の致命的弱点

-- 病院スケジューリング: 最低 1 名の医師が当直でなければならない
-- 初期状態: 医師 Alice と Bob 両方が当直中

-- T1 (Alice がオフ申請)
BEGIN;
SELECT COUNT(*) FROM doctors WHERE on_call = true;  -- 2
-- 2 名なので 1 名抜けても大丈夫と判断
UPDATE doctors SET on_call = false WHERE name = 'Alice';
COMMIT;

-- T2 (Bob がオフ申請、同時進行)
BEGIN;
SELECT COUNT(*) FROM doctors WHERE on_call = true;  -- 2 (Alice 変更前の snapshot)
UPDATE doctors SET on_call = false WHERE name = 'Bob';
COMMIT;

-- 結果: 両方オフ! 当直医師 0 名!

2 つの transaction がそれぞれ 異なる行を変更 したので書き込み衝突が無い。しかし結果的に 不変条件 (最低 1 名当直) が破られる。これを Write Skew と呼ぶ。

Write Skew は Snapshot Isolation で発生し得て、真の Serializable でのみ完全に防止される。

Read Skew: 一貫性の無い集計

-- 口座 A、B それぞれ 100 円

-- T1 (読み取り)
BEGIN;
SELECT balance FROM accounts WHERE id = 'A';  -- 100

-- T2 (送金 A -> B)
BEGIN;
UPDATE accounts SET balance = 50 WHERE id = 'A';
UPDATE accounts SET balance = 150 WHERE id = 'B';
COMMIT;

-- T1 続き
SELECT balance FROM accounts WHERE id = 'B';  -- 150
COMMIT;

T1 は A=100、B=150 を読んで合計 250 円という 誤った値 を得る。本来は A=50、B=150 または A=100、B=100 でなければならない。

Snapshot Isolation はこれを完璧に解決する: T1 が開始された時点の snapshot を見るので、A=100、B=100 を読む。

3. MVCC: Multi-Version Concurrency Control

MVCC の基本アイデア

従来の DB はロックで同時性を管理した。読み取りもロックを掛けて書き込みを止め、書き込みもロックを掛けて読み取りを止めた。これは 「Readers block writers, writers block readers」 という有名な問題を生んだ。

MVCC はこの問題を解決する:

「各行の複数バージョンを維持し、各 transaction は自分に合うバージョンを読む。」

結果: 読み取りは書き込みを止めず、書き込みも読み取りを止めない。

MVCC の核心構造

各行にメタデータを追加:

• xmin: このバージョンを作成した transaction ID。
• xmax: このバージョンを削除した transaction ID (無ければ 0 または無限大)。

可視性ルール:

• Transaction T が行バージョン V を見られる <-> xmin <= T AND (xmax == 0 OR xmax > T)。

つまり、T の開始時点ですでに存在し、まだ生きているバージョン だけが T に見える。

4. PostgreSQL の MVCC 深掘り

行の実際の保存構造

PostgreSQL ではすべての行 (tuple) が以下のヘッダを持つ:

typedef struct HeapTupleHeaderData {
    TransactionId t_xmin;   // 作成した transaction
    TransactionId t_xmax;   // 削除/更新した transaction
    CommandId     t_cmin;   // 同じ transaction 内の command ID
    CommandId     t_cmax;
    ItemPointerData t_ctid; // この tuple の位置 (または次バージョンへのポインタ)
    // ...
};

UPDATE の実際の動作

PostgreSQL で UPDATE は その場での更新ではない:

1. 既存の行に xmax を設定 (削除マーク)。
2. 新しい行を挿入 し xmin = 現在の transaction。
3. 既存の行の ctid が新しい行を指すように接続。

結果: ページに 同じ論理的行の複数バージョン が共存する。

Before UPDATE:
[xmin=100, xmax=0, id=1, balance=100]

After UPDATE by tx 150:
[xmin=100, xmax=150, id=1, balance=100]  <- 旧バージョン
[xmin=150, xmax=0, id=1, balance=200]    <- 新バージョン

Snapshot

PostgreSQL transaction は開始時に snapshot を得る。Snapshot は:

• xmin: アクティブな最古の transaction ID。
• xmax: まだ割り当てられていない最大の transaction ID。
• xip[]: 現在進行中の transaction ID のリスト。

Tuple T が見えるかの判定:

def is_visible(tuple_xmin, tuple_xmax, snapshot):
    # まだ commit されていない作成者
    if tuple_xmin in snapshot.xip:
        return False
    if tuple_xmin >= snapshot.xmax:
        return False

    # 既に削除されている場合
    if tuple_xmax != 0 and tuple_xmax not in snapshot.xip:
        if tuple_xmax < snapshot.xmax:
            return False  # 削除が commit 済み

    return True

VACUUM: ガベージコレクション

MVCC の副作用は 旧バージョン行がどんどん溜まる ことだ。これを整理するのが VACUUM である:

• VACUUM: すべての transaction がもはや見られない dead tuple を回収。
• VACUUM FULL: テーブルを再構築して領域も回収 (AccessExclusive lock が必要)。
• Autovacuum: 自動トリガ。

VACUUM を怠る とテーブルが膨張し (bloat)、性能が急激に劣化する。

PostgreSQL の Isolation Level の真実

PostgreSQL は Read Uncommitted をサポートしない。設定しても Read Committed のように動作する。実際の実装:

注意: PostgreSQL の Repeatable Read は標準より強い。Phantom Read も防止する。しかし Write Skew は依然として発生し得る。これを完全に防ぐには Serializable に上げなければならない。

5. MySQL InnoDB の MVCC

Undo Log ベースの MVCC

PostgreSQL が新しいバージョンを その場に追加 する方式ならば、InnoDB は in-place update + undo log 方式だ:

1. 実際のデータページには 最新バージョンのみ 保存。
2. 以前のバージョンは undo log に保存。
3. 古い transaction が旧バージョンを読む必要があるとき、undo log をたどって再構成。

データページ:
[id=1, balance=200, DB_TRX_ID=150, DB_ROLL_PTR=-> undo]
                                        |
                                        v
Undo Log:
[id=1, balance=100, DB_TRX_ID=100]

長所短所の比較

InnoDB Repeatable Read = Snapshot Isolation

InnoDB のデフォルト isolation level は Repeatable Read で、transaction 開始時に consistent read view を生成する。これは Snapshot Isolation と実質的に同一だ。

Next-Key Lock: Phantom 防止

InnoDB は Next-Key Lock で Repeatable Read でも phantom を防止する:

-- id 列インデックス: 5, 10, 20

-- T1
BEGIN;
SELECT * FROM t WHERE id > 8 AND id < 15 FOR UPDATE;
-- このクエリが (5, 10]、(10, 20) の範囲にロックを掛ける

-- T2
INSERT INTO t VALUES (12);  -- 待機! (範囲ロックのため)

Next-Key Lock は インデックスエントリ + その前のギャップ にロックを掛ける。これで Repeatable Read + phantom 防止を達成する。

Gap Lock, Record Lock, Next-Key Lock

• Record Lock: 特定のインデックスエントリにロック。
• Gap Lock: インデックスエントリ間の「ギャップ」にロック。
• Next-Key Lock: Record Lock + その前の Gap Lock。

この 3 つの組み合わせで様々な isolation level を実装する。

6. Snapshot Isolation とその限界

Snapshot Isolation の魅力

Snapshot Isolation (SI) は多くの DB で「Repeatable Read」または「Serializable」として売られる。なぜか?

• 読み取りが速い: snapshot さえあればロック不要。
• Read Skew 防止: 一貫した時点のデータを見る。
• 実装が単純: MVCC の上にそのまま載せられる。

First-Committer-Wins (FCW) ルール

SI で同じ行を複数の transaction が修正しようとすると、先に commit した側が勝つ。他の transaction は「書き込み衝突」エラーで 必ず rollback されなければならない。

-- T1、T2 両方が同じ行を修正しようとする
-- T1: UPDATE row SET ... WHERE id = 1
-- T2: UPDATE row SET ... WHERE id = 1

-- T1 が先に commit -> 成功
-- T2 が commit を試みる -> ERROR: could not serialize access

アプリケーションはこのエラーを捕捉して transaction を再試行しなければならない。

Write Skew: SI の致命的欠陥

先ほど見た医師当直の例がまさに Write Skew だ。2 つの transaction が互いに異なる行を修正 すれば FCW も適用されない。結果として不変条件が破られる。

Write Skew の共通パターン:

1. transaction がデータを読んで条件を確認。
2. 条件に従って別の行を修正。
3. 別の transaction が同じ条件を確認し、同じロジックで修正。
4. 両方の transaction が成功するが全体の条件が破られる。

Phantom と Write Skew の関係

Phantom は Write Skew の特殊ケースだ:

• 通常の Write Skew: 既に存在する行を読む。
• Phantom Write Skew: 条件に合う行が「無い」ことを読む。

-- 例: ユーザー名の一意性保証
-- T1: ユーザー名 "alice" が無いことを確認 -> 追加
-- T2: ユーザー名 "alice" が無いことを確認 -> 追加
-- 結果: 同じ名前が 2 回挿入される (SI で可能!)

これを防ぐには 真の Serializable が必要か、unique 制約 に頼らなければならない。

7. Serializable Snapshot Isolation (SSI)

問題提起

Snapshot Isolation は速いが Write Skew を許す。真の Serializable は安全だがロックのため遅い。もっと良い方法は無いか?

2008 年の Michael Cahill、Uwe Rohm、Alan Fekete の論文 "Serializable Isolation for Snapshot Databases" が答えを示した: SSI (Serializable Snapshot Isolation)。

SSI の核心アイデア

「SI のように動作するが、serializable でないパターンを検出して rollback する。」

Transaction 間の read/write 依存グラフ を追跡し、サイクルが検出されたら片方をキャンセルする。

Dangerous Structure

Cahill らは 「rw-dependency の二重サイクル」 が serializability を壊す唯一のパターンだと証明した:

T1 --rw--> T2 --rw--> T3

連続する 2 つの rw 依存 (T1 は T2 が書いたものを読まなかったが、T2 は T1 が読んだものを書いた) があれば危険だ。SSI はこれを検出する。

PostgreSQL の SSI 実装

PostgreSQL 9.1 から SERIALIZABLE = SSI で実装された:

1. Transaction 開始時に SI snapshot を取得。
2. 読み取り/書き込み演算ごとに predicate lock を追跡。
3. 衝突パターン検出時に片方を ERROR: could not serialize access で rollback。
4. Rollback された transaction はアプリケーションが再試行。

長所: 性能は SI に近く、安全性は Serializable。 短所: rollback 処理ロジックが必要、直感と異なる時点でエラーが発生。

使用例

-- PostgreSQL
BEGIN ISOLATION LEVEL SERIALIZABLE;

-- 医師当直の例
SELECT COUNT(*) FROM doctors WHERE on_call = true;
UPDATE doctors SET on_call = false WHERE name = 'Alice';

-- 同時実行される別の transaction が Bob を修正すると
-- commit 時に一方は rollback される:
-- ERROR: could not serialize access due to read/write dependencies
COMMIT;

アプリケーションパターン:

def do_doctor_off(doctor_name):
    while True:
        try:
            with db.transaction(isolation="serializable"):
                count = db.execute("SELECT COUNT(*) FROM doctors WHERE on_call = true")
                if count > 1:
                    db.execute("UPDATE doctors SET on_call = false WHERE name = %s", doctor_name)
                else:
                    raise ValueError("Cannot go off, you're the only one on call")
            return
        except SerializationError:
            continue  # 再試行

8. Isolation Level 別の実践利用ガイド

Read Committed (デフォルトで十分か?)

大部分の Web アプリケーション でデフォルトだ。個別クエリが一貫性を持てば OK な場合:

• 単純な CRUD
• ユーザープロフィールの参照/修正
• ブログ投稿

注意: Non-repeatable read が許可されるので、1 つの transaction で同じ行を 2 回読むコードは注意が必要。

Repeatable Read / Snapshot Isolation

レポート生成、複雑な参照 に適する:

• ダッシュボード集計
• 長い transaction 中に一貫したビューが必要な時
• Read-heavy 分析クエリ

注意: Write Skew の可能性を認識し、重要な不変条件が関わるなら明示的ロックや Serializable を検討。

Serializable (SSI)

整合性が絶対的に重要な場合:

• 金融取引
• 医師当直、航空機予約のようなリソース割り当て
• Unique 制約の複雑な条件

トレードオフ:

• 性能低下 (rollback オーバーヘッド)
• 再試行ロジック必須
• 高同時性環境で競合が激化

どう選ぶか?

1. 基本的に Read Committed で始める。
2. 不整合バグが見え始めたら該当する transaction だけ Serializable に上げる。
3. 全体を Serializable にせず、重要な経路にのみ 選択的に適用。

9. Optimistic vs Pessimistic Locking

Isolation level 以外にも同時性制御戦略がある。

Pessimistic Locking (悲観ロック)

「衝突が頻繁に起きるだろう。先に防ごう。」

BEGIN;
SELECT * FROM account WHERE id = 1 FOR UPDATE;  -- ロック取得
-- 計算...
UPDATE account SET balance = ... WHERE id = 1;
COMMIT;  -- ロック解放

長所: 衝突なし、単純なロジック。 短所: ロック競合、デッドロック、長いロック保持時の性能低下。

Optimistic Locking (楽観ロック)

「衝突は稀だ。検出して再試行しよう。」

-- 読み取り
SELECT balance, version FROM account WHERE id = 1;  -- balance=100, version=5

-- アプリケーションで計算
new_balance = 100 - 30

-- 書き込み (version チェック)
UPDATE account
SET balance = 70, version = version + 1
WHERE id = 1 AND version = 5;

-- affected rows == 0 なら他の transaction が先に修正 -> 再試行

長所: ロックオーバーヘッドなし、高同時性に有利。 短所: アプリケーション再試行ロジック必要、衝突が多いとかえって遅い。

選択ガイド

10. 分散 Transaction と Isolation

単一 DB Isolation の限界

ここまでの話は 単一 DB 内の isolation だった。複数 DB やサービス間の transaction はどうするか?

Two-Phase Commit (2PC)

伝統的な分散 transaction プロトコル:

1. Prepare Phase: Coordinator がすべての参加者に「準備できたか?」と問い合わせる。
2. Commit Phase: 全員が準備できていれば「commit せよ」命令、そうでなければ「rollback」命令。

問題点:

• Coordinator 障害時にブロッキング。
• 性能低下 (2 回のネットワーク往復)。
• 参加者の数だけ非可用性が増加。

Saga パターン

2PC の代替。補償 transaction (compensating transaction) で失敗を復旧:

Order Service: create_order(ID)
Payment Service: charge(ID)
Inventory: reserve(ID)

失敗時は逆順で:
Inventory: unreserve(ID)
Payment Service: refund(ID)
Order Service: cancel_order(ID)

長所: ブロッキングなし、サービス独立性。 短所: 補償ロジックの実装負担、isolation level が低い (中間状態が外部に見える可能性)。

Percolator / Spanner

Google の分散 DB は真の Serializable のために:

• Percolator (Bigtable 上): 2PC + MVCC で汎用分散 transaction。
• Spanner: TrueTime API で全域時計同期 + 2PC + Paxos。

TiDB は Percolator モデルをオープンソースで実装した。

11. 実戦トラブルシューティング

問題 1: "deadlock detected"

原因: 2 つの transaction が互いのロックを待つ。

解決:

• 常に 同じ順序 でロックを取得する (例: id 昇順)。
• ロック保持時間の短縮 (短い transaction)。
• SELECT ... FOR UPDATE SKIP LOCKED でスキップ。
• 再試行ロジックを実装。

問題 2: "could not serialize access"

原因: SSI の rollback。

解決:

• アプリケーションで再試行。
• 指数バックオフ。
• 競合が激しい経路は明示的ロックへの切り替えを検討。

問題 3: 長い transaction による bloat (PostgreSQL)

症状: ディスク使用量急増、クエリが遅くなる。

原因: 長く開いている transaction が VACUUM を妨害。

解決:

-- 長時間実行中の transaction を確認
SELECT pid, state, query, xact_start, now() - xact_start AS duration
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY xact_start;

-- 手動 VACUUM
VACUUM (ANALYZE, VERBOSE) table_name;

予防:

• idle_in_transaction_session_timeout を設定。
• Transaction を短く保つ。
• アプリケーションが transaction を開いたまま idle 状態で放置しない。

問題 4: Web アプリケーションでの Lost Update

症状: ユーザーが同時に編集すると 1 人の変更が消える。

解決:

• version 列 + optimistic locking。
• または CRDT ベースの協調編集。
• またはユーザーフィードバック (「誰かが先に修正しました。マージしますか?」)。

クイズで復習

おわりに: Isolation Level の罠と解法

核心の教訓

1. 「Repeatable Read」という名前を信じるな。DB ごとに異なる実装だ。ドキュメントと実際の動作を確認しよう。

2. Snapshot Isolation は万能ではない。Write Skew は繊細だが致命的だ。不変条件に敏感なロジックは Serializable を検討しよう。

3. 完璧な Serializable は高価だ。性能が重要なら全体を SI にして リスクのある経路だけ Serializable に昇格する戦略が実用的だ。

4. MVCC はタダではない。PostgreSQL は VACUUM、InnoDB は undo log。長期 transaction は両方にとって毒だ。

5. Isolation level は防衛線の一層。Unique 制約、外部キー、アプリケーション検証を一緒に活用しよう。

実務チェックリスト

• 我々のチームの DB のデフォルト isolation level は何か?
• Write Skew が発生し得るロジックはあるか?
• 長い transaction が VACUUM/undo log に負担をかけていないか?
• 再試行ロジックが必要な isolation level を使っているか?
• デッドロック発生時の検出と再試行は準備されているか?

分散システムの難しさの多くの部分が「データ整合性」だ。Isolation Level はその真ん中にある。名前の暗記よりも 実際の動作を理解 することがバグを防ぐ。

参考資料

• A Critique of ANSI SQL Isolation Levels (Berenson et al., 1995) - Isolation level の穴
• Serializable Isolation for Snapshot Databases (Cahill et al., 2008) - SSI 原論文
• PostgreSQL Concurrency Control - 公式ドキュメント
• MySQL InnoDB Locking and Transaction Model
• Designing Data-Intensive Applications, Ch.7 - Martin Kleppmann の名著
• Jepsen: PostgreSQL Analysis - 検証レポート
• Hermitage: Isolation Level Test Suite - 各 DB の実際の動作比較