トランザクション分離レベルの見落としの事例

概要

この記事ではトランザクション分離レベル関連の見落としにより、DB不整合が起きてしまう事例を見ていきます。

事例

事例1: MVCCスナップショット読み取りによる審査結果の不整合

料理審査システムにおいて、2人の審査員が並行して評価処理を行う際、MVCCのスナップショット読み取りにより、一方の審査員の評価更新が他方から見えず、料理ステータスが誤って更新される問題です。

ちょっと無理やりな例ですが、他にあまりいい例が思いつきませんでした。すみません。

以下は mysql を前提としており、mysql のデフォルトのトランザクション分離レベルは REPEATABLE READ であり、ファントムリードを回避することができます。しかしこれが逆に、今回の問題を引き起こします。

テーブル設計

料理テーブル (dishes)

CREATE TABLE dishes (
    dish_id INT PRIMARY KEY,
    dish_name VARCHAR(100),
    status VARCHAR(20) DEFAULT 'under_review'  -- 'under_review', 'approved', 'rejected' のみ
);

審査結果テーブル (reviews)

CREATE TABLE reviews (
    review_id INT PRIMARY KEY AUTO_INCREMENT,
    dish_id INT,
    judge_id INT,  -- 審査員のID
    result VARCHAR(20) DEFAULT 'pending',  -- 'pending', 'pass', 'fail' のみ
    FOREIGN KEY (dish_id) REFERENCES dishes(dish_id),
    INDEX idx_dish_judge (dish_id, judge_id)
);

問題が発生するシナリオ（実験手順）

初期データ準備

-- 全ターミナルで実行: DB作成と切り替え
CREATE DATABASE mvcc_experiment;
USE mvcc_experiment;

-- テーブル作成
CREATE TABLE dishes (
    dish_id INT PRIMARY KEY,
    dish_name VARCHAR(100),
    status VARCHAR(20) DEFAULT 'under_review'  -- 'under_review', 'approved', 'rejected' のみ
);

CREATE TABLE reviews (
    review_id INT PRIMARY KEY AUTO_INCREMENT,
    dish_id INT,
    judge_id INT,  -- 審査員のID
    result VARCHAR(20) DEFAULT 'pending',  -- 'pending', 'pass', 'fail' のみ
    FOREIGN KEY (dish_id) REFERENCES dishes(dish_id)
);

-- 初期データ投入（料理作成と同時に2人の審査員を割り当て）
INSERT INTO dishes VALUES (1, 'Curry Rice', 'under_review');
INSERT INTO reviews (dish_id, judge_id, result) VALUES 
(1, 101, 'pending'),  -- 審査員X
(1, 102, 'pending');  -- 審査員Y

-- 初期状態確認
SELECT * FROM dishes;
SELECT * FROM reviews;

問題発生の実験（時系列順）

-- ■ステップ0: 両ターミナルでDB選択
-- [ターミナル1] TX1用
USE mvcc_experiment;

-- [ターミナル2] TX2用
USE mvcc_experiment;

-- ■ステップ1: 両ターミナルでトランザクション開始
-- [ターミナル1] TX1: 審査員X(101)
START TRANSACTION;

-- [ターミナル2] TX2: 審査員Y(102)  
START TRANSACTION;

-- ■ステップ2: 両者がスナップショット作成（ほぼ同時）
-- ※この初回SELECTは今回の例では直接使用しませんが、ご想像の通り、
-- 　より複雑な処理では料理情報を参照する場面があるかもしれません。
-- 　ただし、良い具体例は思いつきませんでした。
-- [ターミナル1] TX1
SELECT * FROM dishes WHERE dish_id = 1;  -- トランザクション内初回SELECTでスナップショット作成

-- [ターミナル2] TX2
SELECT * FROM dishes WHERE dish_id = 1;  -- トランザクション内初回SELECTでスナップショット作成

-- ■ステップ3: TX1が料理テーブルをロック
-- [ターミナル1] TX1
SELECT * FROM dishes WHERE dish_id = 1 FOR UPDATE;

-- ■ステップ4: TX2も料理テーブルロック試行（ブロックされる）
-- [ターミナル2] TX2
SELECT * FROM dishes WHERE dish_id = 1 FOR UPDATE;
-- ★この時点でTX2は待機状態になる

-- ■ステップ5: TX1が処理を完了
-- [ターミナル1] TX1
UPDATE reviews SET result = 'pass' WHERE dish_id = 1 AND judge_id = 101;

-- MySQLのREPEATABLE READはMVCC（スナップショット）技術でファントムリードを回避
-- そのため通常のSELECTは実データではなく、トランザクション開始時のスナップショットを読む
SELECT COUNT(*) as total, COUNT(CASE WHEN result = 'pass' THEN 1 END) as passed 
FROM reviews WHERE dish_id = 1;
+-------+--------+
| total | passed |
+-------+--------+
|     2 |      1 |
+-------+--------+
-- 結果: total=2, passed=1

-- ステータス更新判定（1/2人なので更新しない）
-- 集計結果: 2人中1人合格 → 更新しない

COMMIT;
-- ★TX1コミット完了、ロック解放

-- ■ステップ6: TX2がロック取得し処理継続
-- [ターミナル2] TX2（ロック取得成功後、自動的に継続）
UPDATE reviews SET result = 'pass' WHERE dish_id = 1 AND judge_id = 102;

-- MySQLのREPEATABLE READはMVCC（スナップショット）技術でファントムリードを回避
-- そのため通常のSELECTは実データではなく、トランザクション開始時のスナップショットを読む
SELECT COUNT(*) as total, COUNT(CASE WHEN result = 'pass' THEN 1 END) as passed 
FROM reviews WHERE dish_id = 1;
+-------+--------+
| total | passed |
+-------+--------+
|     2 |      1 |
+-------+--------+
-- ★問題: total=2, passed=1（TX1の更新が見えない！）

-- ステータス更新判定（スナップショットで1/2人に見える）
-- 集計結果: 2人中1人合格（実際は2人合格）
-- 全員合格に見えないので、ステータス更新しない
-- ★問題: 実際は2/2人合格なのに更新されない！

COMMIT;

結果確認

-- 実際のデータ状況を確認
SELECT * FROM reviews;
+-----------+---------+----------+--------+
| review_id | dish_id | judge_id | result |
+-----------+---------+----------+--------+
|         1 |       1 |      101 | pass   |
|         2 |       1 |      102 | pass   |
+-----------+---------+----------+--------+
-- 結果: 両審査員とも'pass'

SELECT * FROM dishes;
+---------+------------+--------------+
| dish_id | dish_name  | status       |
+---------+------------+--------------+
|       1 | Curry Rice | under_review |
+---------+------------+--------------+
-- 結果: statusは'under_review'のまま（本来は'approved'であるべき）

-- 正しい集計
SELECT COUNT(*) as total, COUNT(CASE WHEN result = 'pass' THEN 1 END) as passed 
FROM reviews WHERE dish_id = 1;
+-------+--------+
| total | passed |
+-------+--------+
|     2 |      2 |
+-------+--------+
-- 結果: total=2, passed=2（全員合格）

クリーンアップ

-- テーブルとDB削除
DROP TABLE reviews;
DROP TABLE dishes;
DROP DATABASE mvcc_experiment;

対策案

A. SELECT FOR UPDATEで集計（Current Readを強制）

-- 集計時にFOR UPDATEで最新データを読む
SELECT COUNT(*) as total FROM reviews WHERE dish_id = 1 FOR UPDATE;
SELECT COUNT(*) as passed FROM reviews WHERE dish_id = 1 AND result = 'pass' FOR UPDATE;

B. UPDATE文のサブクエリで集計（Current Read利用）

-- UPDATE文内のサブクエリは最新データを読む
UPDATE dishes SET status = 'approved' 
WHERE dish_id = 1 
AND (SELECT COUNT(*) FROM reviews WHERE dish_id = 1) = 
    (SELECT COUNT(*) FROM reviews WHERE dish_id = 1 AND result = 'pass');
-- ※MySQL実装依存の動作

C. 適切な分離レベル使用

-- READ COMMITTEDを使用（毎回最新データ読み取り）
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
-- 処理...

D. PostgreSQLを使用（デフォルトで問題回避）

• PostgreSQLのREPEATABLE READは同じ手順でも不整合が起きない
• ファントムリードを許可するため、通常のSELECTも実データを読む
• TX2はTX1の更新を認識でき、正しく集計できる
• PostgreSQL: REPEATABLE READでもファントムリードを許可（実データ読み取り）
• MySQL: REPEATABLE READはスナップショット読み取り（不整合が発生）

参考