トランザクション分離レベルとデッドロック｜READ COMMITTEDからSERIALIZABLEまで実務で理解

在庫が1つしかない商品が2人に売れてしまった。ポイント残高の減算が同時に走って合計が合わない。本番ログに deadlock detected や Deadlock found when trying to get lock が時々出るが、再現できないので放置している。こうした問題は、SQLの書き方が間違っているのではなく、「複数のトランザクションが同時に動くときに何が保証されるか」を決める分離レベルを意識せずに書いたときに起こります。

この記事では、分離レベルが防ぐ3つの読み取り異常（ダーティリード・ノンリピータブルリード・ファントムリード）を具体例で示し、4つの分離レベルとPostgreSQL/MySQLのデフォルトの違い、SELECT FOR UPDATE の使いどころ、デッドロックが起きる典型パターンと回避策、そしてどうしても起きる競合をアプリで吸収するリトライ設計までを解説します。クエリの高速化やテーブル設計の基礎はSQLパフォーマンスチューニング入門とデータベース設計入門で扱っているため、本記事は「同時実行」に絞ります。

分離レベルが防ぐ3つの読み取り異常

同時に動くトランザクションが互いに見えてしまう問題

トランザクションは「一連の操作をまとめて成功か失敗にする」仕組みですが、複数のトランザクションが同時に動いているとき、他方の途中経過がどこまで見えるかは別問題です。SQL標準は、見えてしまう度合いを3つの異常として定義しています。

異常	何が起きるか	具体例
ダーティリード	他のトランザクションがまだCOMMITしていない変更を読んでしまう	Aが残高を1000→0に更新中（未確定）、Bが0を読んで「残高不足」と判定。その後AがROLLBACK
ノンリピータブルリード	同じ行を2回読むと、間に他がCOMMITしたため値が変わっている	Aが在庫を読んで1、検証中にBが購入してCOMMIT、Aが再度読むと0
ファントムリード	同じ条件で2回検索すると、間に他がINSERT/DELETEしたため行数が変わっている	Aが「本日の注文」を数えて10件、集計中にBが1件追加、Aが再集計すると11件

これに加えて実務で重要なのがロストアップデート（2つのトランザクションが同じ行を読んで計算して書き戻し、片方の更新が消える）です。SQL標準の定義には含まれませんが、在庫や残高の二重減算はほぼこれが原因です。

4つの分離レベルとPostgreSQL/MySQLのデフォルト

レベルが上がるほど安全だが、待ちと失敗が増える

分離レベル	ダーティリード	ノンリピータブルリード	ファントムリード	備考
READ UNCOMMITTED	起きる	起きる	起きる	PostgreSQLではREAD COMMITTEDとして動作
READ COMMITTED	防ぐ	起きる	起きる	PostgreSQLのデフォルト
REPEATABLE READ	防ぐ	防ぐ	標準では起きうる	MySQL（InnoDB）のデフォルト。PostgreSQLの実装ではファントムも防ぐ
SERIALIZABLE	防ぐ	防ぐ	防ぐ	直列実行と同じ結果を保証。競合すると失敗する

両DBともMVCC（多版同時実行制御）という仕組みで、書き込み中の行も「変更前の版」を読ませることで読み取りをブロックしません。違いはいつのスナップショットを読むかです。READ COMMITTEDは「文ごと」に最新のCOMMIT済み状態を読み、REPEATABLE READは「トランザクション開始時」のスナップショットを最後まで使います。したがってREPEATABLE READでは、トランザクションの途中で他人のCOMMITは一切見えなくなります。

実務上の注意点として、MySQLのREPEATABLE READは通常のSELECTではスナップショットを読む一方、SELECT ... FOR UPDATE や UPDATE は最新の行をロックして読みます（さらにギャップロックで範囲への挿入も防ぎます）。「同じトランザクション内でSELECTとUPDATEの結果が食い違う」と感じたら、この仕様が原因のことが多いです。PostgreSQLのREPEATABLE READでは、スナップショット取得後に他人が更新した行を更新しようとすると could not serialize access due to concurrent update エラーになり、トランザクションをやり直す必要があります。

-- 分離レベルの確認と変更
-- PostgreSQL
SHOW default_transaction_isolation;
BEGIN ISOLATION LEVEL REPEATABLE READ;
-- MySQL
SELECT @@transaction_isolation;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  -- 次のトランザクションだけ
START TRANSACTION;

SELECT FOR UPDATEで「読んで書く」を安全にする

在庫や残高のように「読んで、計算して、書く」処理は、分離レベルを上げても本質的には解決しません（READ COMMITTEDではロストアップデートが起き、REPEATABLE READ/SERIALIZABLEでは片方がエラーになる）。確実なのは、読む時点で行をロックすることです。

BEGIN;
-- この行を他のトランザクションが更新・FOR UPDATEできないようロック
SELECT stock FROM products WHERE id = 42 FOR UPDATE;
-- アプリ側で stock >= 1 を確認してから
UPDATE products SET stock = stock - 1 WHERE id = 42;
INSERT INTO orders (product_id, user_id) VALUES (42, 7);
COMMIT;

-- ロック待ちをせず即エラーにしたい場合（画面で「混み合っています」と返す等）
SELECT stock FROM products WHERE id = 42 FOR UPDATE NOWAIT;

-- ジョブキューなど「ロック中の行は飛ばして次を取る」場合
SELECT id FROM jobs WHERE status = 'pending'
ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;

単純な減算だけなら UPDATE products SET stock = stock - 1 WHERE id = 42 AND stock > 0 として更新行数が1かどうかで判定するほうが、ロックの保持時間が短く済みます。FOR UPDATE は「読んだ値をもとに複数の処理を行う」場合に使います。

デッドロックが起きる典型パターンと回避策

互いに相手のロック解放を待つ

デッドロックは、トランザクションAが行1をロックして行2を待ち、トランザクションBが行2をロックして行1を待つ、という状態です。どちらも永遠に進めないため、DBが検出して片方を強制的にエラー終了させます。PostgreSQLは deadlock_timeout（デフォルト1秒）待った後に検査してエラー（SQLSTATE 40P01）を返し、MySQL InnoDBは即座に検出して変更量の少ないほうをロールバックします（エラー1213）。「たまに出るが再現しない」のは、タイミングが噛み合ったときだけ発生するためです。

典型パターンは次の3つです。

1. 更新順序の不一致: 送金処理で「AからBへ」と「BからAへ」が同時に走り、それぞれ自分の口座→相手の口座の順にロックする。
2. バッチと画面の衝突: バッチが UPDATE ... WHERE category = 'x' で多数の行を順に更新している間に、画面側が同じ範囲の行を別の順序で更新する。
3. 親子テーブルの外部キー: 子テーブルへのINSERTは親の行に共有ロックを取るため、親を更新するトランザクションと絡む。

回避の基本は「同じ順序で、短く」

• ロック順序を統一する: 複数行を更新するときは常に主キー順（ORDER BY id）でロックする。送金なら口座IDの小さいほうから必ずロックする。これだけで循環待ちは原理的に起きなくなります。
• トランザクションを短くする: 外部API呼び出し・メール送信・ユーザーの入力待ちをトランザクションの中に入れない。ロック保持時間が長いほど衝突確率は上がります。長いトランザクションはコネクションプールの接続も占有し続けるため、接続枯渇の原因にもなります。
• 一括で取る: 複数行を個別にFOR UPDATEするのではなく、WHERE id IN (...) ORDER BY id FOR UPDATE で一度に取得する。
• 不要なインデックス欠落を直す: MySQLではWHERE句にインデックスがないと想定より広い範囲がロックされ、デッドロックの温床になります。

-- PostgreSQL: デッドロックの詳細をログに残す
-- postgresql.conf
log_lock_waits = on          -- deadlock_timeout を超えた待ちを記録
deadlock_timeout = 1s

-- MySQL: 直近のデッドロックの内容を確認
SHOW ENGINE INNODB STATUS\G
-- "LATEST DETECTED DEADLOCK" セクションに両トランザクションのSQLと待っていたロックが出る
-- 全デッドロックをエラーログに残す
SET GLOBAL innodb_print_all_deadlocks = ON;

競合を前提にしたリトライ設計

エラーコードを見てトランザクション全体をやり直す

ロック順序を統一しても、SERIALIZABLEやREPEATABLE READでの直列化失敗、まれなデッドロックは残ります。これらは「今回はやり直せば通る」一時的なエラーなので、アプリ側でトランザクション全体を再実行するのが正しい対処です。

import time, random
import psycopg
from psycopg.errors import SerializationFailure, DeadlockDetected

RETRYABLE = (SerializationFailure, DeadlockDetected)  # 40001, 40P01

def run_with_retry(conn, fn, max_attempts=5):
    for attempt in range(1, max_attempts + 1):
        try:
            with conn.transaction():
                return fn(conn)          # トランザクション全体をやり直す単位にする
        except RETRYABLE:
            if attempt == max_attempts:
                raise
            # 指数バックオフ + ジッターで、同時に再試行して再衝突するのを避ける
            time.sleep(min(0.05 * (2 ** attempt), 1.0) + random.uniform(0, 0.05))

設計上の注意点は3つです。第一に、リトライする単位は「SQL1文」ではなく「トランザクション全体」です。途中まで進んだ状態から1文だけやり直しても整合性は保てません。第二に、トランザクションの中で行った外部への副作用（メール送信、決済API）は再実行されるため、副作用はCOMMIT後に行うか、冪等にしておきます。第三に、リトライ回数には上限を設け、超えたらエラーとして扱います。無限に再試行すると障害時に負荷を増幅させます。MySQLではエラーコード1213（デッドロック）と1205（ロック待ちタイムアウト、デフォルト50秒）が対象になります。

トラブル事例：在庫1個の商品が2人に売れた

症状

セール開始直後、在庫1個の限定商品に対して2件の注文が成立し、在庫数が-1になっていました。コードは「在庫をSELECT→1以上なら在庫をUPDATE→注文をINSERT」をトランザクション内で行っており、テストでは問題ありませんでした。

原因

PostgreSQLのデフォルトであるREAD COMMITTEDで、2つのリクエストがほぼ同時に SELECT stock を実行し、両方が1を読みました。その後それぞれが UPDATE ... SET stock = 0 と注文INSERTを行い、両方ともCOMMITに成功しました。典型的なロストアップデートで、READ COMMITTEDは「読んだ値が変わらないこと」を保証しないため、SQL単体は正しくても同時実行では破綻します。

対処

1. 在庫の読み取りを SELECT ... FOR UPDATE に変更し、先に読んだトランザクションがCOMMITするまで後続を待たせるようにした。待たされた側は0を読み、正しく「売り切れ」を返すようになった。
2. さらに UPDATE products SET stock = stock - 1 WHERE id = ? AND stock > 0 と更新行数チェックを併用し、二重の安全策にした。
3. DB側に CHECK (stock >= 0) 制約を追加し、仮にアプリのバグがあっても負の在庫がCOMMITできないようにした。

「分離レベルを上げれば解決するのでは」と考えがちですが、REPEATABLE READにしても片方がエラーになるだけで、リトライ設計がなければユーザーには失敗として見えます。ロック（FOR UPDATE）＋制約＋リトライの3層で守るのが実務的な答えです。

まとめ

分離レベルとロックは「同時に動く処理の間で、何を保証するか」を決めるものです。

1. READ COMMITTED（PostgreSQL）とREPEATABLE READ（MySQL）はデフォルトが違い、同じコードでも挙動が異なる。
2. 「読んで計算して書く」処理はFOR UPDATEか条件付きUPDATEで守る。分離レベルだけでは解決しない。
3. デッドロックは「同じ順序で、短く」ロックすることで大半を防げる。ログを有効にして原因のSQLを特定する。
4. 残る競合はトランザクション全体のリトライで吸収し、副作用はCOMMIT後に出す。

まずは本番のログで deadlock や could not serialize を検索し、出ているならそのSQLのロック順序を確認するところから始めてみてください。同時実行を考慮したDB設計やアプリ側の実装レビューは、Harmonic Societyのシステム開発・インフラ支援でもお手伝いしています。