Posted inMySQL

MySQLにおけるOptimistic LockingとPessimistic Locking：Race ConditionとOverbookingを根本から解決する

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

1. クイックスタート：5分で試すRace Conditionの再現と修正

コンサートのチケット残数が残りわずか1枚。その瞬間、2人のユーザーがまったく同じミリ秒のタイミングで決済ボタンを押しました。結果、2人とも決済に成功してしまい、さらに恐ろしいことにデータベース上の在庫数は「-1」を記録してしまいます。これこそがバックエンドエンジニアにとっての古典的な悪夢であるRace ConditionによるOverbookingです。

まずはMySQLのターミナル上で直接テストできるように、ticketsテーブルを素早く作成し、サンプルデータを1件インサートしてみましょう。

CREATE TABLE tickets (
    id INT PRIMARY KEY AUTO_INCREMENT,
    event_name VARCHAR(100) NOT NULL,
    available_seats INT NOT NULL,
    version INT DEFAULT 0
) ENGINE=InnoDB;

INSERT INTO tickets (id, event_name, available_seats, version) 
VALUES (1, 'Rock Fest 2026', 1, 0);

もし2つのリクエストが安易な実装のまま同時に残席数を読み取り、減算処理を実行したらどうなるでしょうか？

-- リクエストAとリクエストBが同時に available_seats = 1 を読み取る
SELECT available_seats FROM tickets WHERE id = 1;

-- その後、双方が1を減算してUPDATEを実行する
UPDATE tickets SET available_seats = available_seats - 1 WHERE id = 1;

両方のUPDATE文が正常終了し、データは一瞬で不整合を起こします。この問題は、以下に示す2つの代表的なロック機構によって完全に解消できます。

アプローチ1：Pessimistic Locking（悲観的ロック：SELECT … FOR UPDATE）

START TRANSACTION;
-- id = 1 の行を排他ロック。後続のリクエストは待機を強制される
SELECT available_seats FROM tickets WHERE id = 1 FOR UPDATE;

-- アプリケーション側で available_seats > 0 を確認した上で減算処理を行う
UPDATE tickets SET available_seats = available_seats - 1 WHERE id = 1;
COMMIT;

アプローチ2：Optimistic Locking（楽観的ロック：versionカラムの活用）

-- ステップ1：現在のversion値（例: version = 0）を含めてデータを取得
SELECT available_seats, version FROM tickets WHERE id = 1;

-- ステップ2：versionが他から変更されていない場合のみ更新
UPDATE tickets 
SET available_seats = available_seats - 1, version = version + 1 
WHERE id = 1 AND version = 0;
-- Affected Rows = 1: チケット購入成功！
-- Affected Rows = 0: 他のユーザーに先を越されたため、完売を通知するかリトライ処理を実行

2. 内部構造：対極にある2つの設計思想

ロックメカニズムの選択を誤ると、スループットが大幅に低下したり、トラフィック急増時にデータベースがダウンしたりする原因になります。

Pessimistic Locking（悲観的ロック）

慎重派の思想：「データに触れるなら即座にロックすべきだ。他のリクエストを信用することはできない。」

InnoDBでトランザクションを開始し、SELECT ... FOR UPDATEを実行すると：

  • MySQLはクエリの条件に一致した行に対して即座にExclusive Lock（排他ロック / X-Lock）を適用します。
  • その行を変更しようとする、またはFOR UPDATE付きで読み取ろうとする他のトランザクションはすべて完全にブロックされ、カレントトランザクションがCOMMITまたはROLLBACKを実行するまで待機状態になります。
  • この仕組みはストレージエンジンに依存します。MyISAMは行レベルロック（row-level lock）をサポートしていないため、必ずInnoDBを使用する必要があります。

Optimistic Locking（楽観的ロック）

楽観派の思想：「読み書きは自由にさせればいい。競合など滅多に起きないのだから、保存直前にまとめて検証すれば十分だ。」

Optimistic Lockingの本質は、MySQLのロック機能に依存しない点にあります。すべての制御ロジックはアプリケーション層の条件付きSQLによって完結します。

  • テーブルに整数型のversionカラム、またはマイクロ秒単位のupdated_atカラムを追加します。
  • データを保存する際、WHERE id = :id AND version = :current_versionのようにバージョンチェック条件を付加します。
  • Affected rows（更新行数）が0件だった場合、直前に別のリクエストによって更新されたことを意味します。バックエンド側でOptimisticLockExceptionなどの例外をスローするか、リトライループを起動します。

3. 実践比較：トレードオフ、デッドロックの罠、そしてAtomic Update

各手法にはそれぞれ適したユースケースが存在します。

評価基準 Pessimistic Locking（悲観的ロック） Optimistic Locking（楽観的ロック）
処理の本質 MySQL InnoDB本来の行レベルロックを使用 versionカラムの検証を組み合わせたアプリケーション層のパターン
競合レベル 非常に高い（同一行に対して多数のリクエストが競合） 低〜中程度（参照が中心で、同一データの同時更新は稀）
スループット トランザクションが順次待機するため低め 参照過多時は極めて高い（ただしリトライ多発時は急落）
潜在的リスク コネクションプールの枯渇、広範囲なデッドロック 連続リトライによるCPU負荷増大やネットワーク帯域の浪費

Pessimistic Lockingで陥りがちな典型的なデッドロックの罠

FOR UPDATEを無秩序に多用すると、容易にデッドロックを引き起こします。例えば、ウォレットAからウォレットBへの送金処理を考えてみましょう。

  • トランザクション1がウォレットAをロックし、続けてウォレットBをロックしようとする。
  • トランザクション2がウォレットBをロックし、続けてウォレットAをロックしようとする。
  • 両トランザクションがお互いのロック解放を永久に待ち続ける。MySQLがデッドロックを検知し、一方のトランザクションを強制終了（ロールバック）する。

実践における鉄則：リソースをロックする順序を常に統一することです。クエリを実行する前にIDのリストを昇順でソートしてからFOR UPDATEを実行するように徹底しましょう。

究極のシンプル解：Atomic Update（単一ステートメント更新）

在庫の減算や残高引き落としのような基本的な処理であれば、必ずしもversionカラムや長時間のトランザクションを導入する必要はありません。MySQL自身に単一のSQL文でアトミックに処理させることができます。

UPDATE tickets 
SET available_seats = available_seats - 1 
WHERE id = 1 AND available_seats >= 1;

極めて簡潔かつ効果的です。この単一のUPDATE文自体がわずか数マイクロ秒の間だけ自動的に行ロックを保持し、即座に解放します。available_seats >= 1という条件により、在庫数がマイナスになることは原理的にあり得ません。戻り値のaffected rowsが1なら購入成功、0ならすでに完売していることを示します。

4. 本番環境から得られた実践的な教訓

数千単位のCCU（同時接続ユーザー数）を抱える高負荷システムでは、ローカル開発環境では決して顕在化しない落とし穴が浮き彫りになります。

  • テーブルロック化の罠を防ぐ：SELECT ... FOR UPDATEのWHERE句に指定するカラムには、必ずインデックス（INDEX）を設定してください。インデックスが存在しない場合、InnoDBはフルテーブルスキャンを行い、全行およびギャップに対してロック（Gap Lock）をかけてしまいます。たった1つのクエリがデータベース全体を機能停止に追い込むことになりかねません。
  • ロック待機タイムアウトを短縮する：トランザクションを長時間ロック待ちの状態にしてはいけません。MySQLのデフォルト値であるinnodb_lock_wait_timeout（50秒）は、ピーク時のアプリケーションコネクションプールを一瞬で枯渇させるのに十分すぎる長さです。セッション単位で3秒程度まで短縮しましょう。 
    SET innodb_lock_wait_timeout = 3;

    MySQL 8.0以降を使用している場合は、SELECT ... FOR UPDATE NOWAITやSKIP LOCKED構文を活用し、無駄に待機させず即座にFail-Fastさせる構成を検討してください。

  • ユースケースに応じた選定基準：
    • Pessimistic Lock（またはRedis分散ロック）を採用すべきケース：大型フラッシュセール、人気ライブのチケット争奪戦、映画館の座席指定など、1秒間に1万人以上がまったく同じリソースに対して購入処理を仕掛けるシナリオ。
    • Optimistic Lockを採用すべきケース：バックオフィス業務、CMS管理画面、ユーザープロフィール更新など、リクエストの99%が参照処理であり、複数の管理者が同一レコードを同時に編集することが極めて稀なシナリオ。
Share:
Tags: