Posted inMySQL

MySQL InnoDB Locking：システムダウンを防ぐための Row、Gap、Next-Key Lock の理解

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

なぜロッキングメカニズムを無視できないのか？

航空券予約アプリを運営していると想像してみてください。残りの座席が1つしかない状況で、2人の顧客が同時に「予約」ボタンを押したとします。もしロック（Locking）の仕組みがなければ、データベースは両方の成功を記録してしまうかもしれません。その結果、データ不整合が発生し、カスタマーエクスペリエンスにとって最悪の事態を招きます。

以前、MySQL 8.0 で秒間約 500 トランザクション（TPS）のトラフィックがある Eコマースシステムを管理していた際、トランザクションを使っていれば十分だと思い込んでいました。しかし、フラッシュセールの際にユーザーが急増すると、ログは「Deadlock found」というエラーで溢れかえりました。システムは徐々に遅くなり、最終的には完全に停止しました。InnoDB がデータをどのようにロックするかを深く理解することは、より安全なコードを書くのに役立ちます。同時に、システムの同時実行性（Concurrency）を大幅に向上させることにもつながります。

5分でできる実機テスト

ロックの仕組みを実際に確認するために、2つのターミナルウィンドウ（セッションAとセッションB）を開いてください。テスト用の簡単なテーブルを作成します。

CREATE TABLE inventory (
    id INT PRIMARY KEY,
    item_name VARCHAR(50),
    stock INT
) ENGINE=InnoDB;

INSERT INTO inventory VALUES (1, 'iPhone 15', 10), (5, 'Samsung S24', 5), (10, 'Sony A7IV', 2);

次のように実行してみましょう。

セッション A: トランザクションを開始し、id=5 の行を保持します。

START TRANSACTION;
UPDATE inventory SET stock = stock - 1 WHERE id = 5; 
-- この時点で、セッションAは id=5 の行の制御権を占有しています。

セッション B: id=5 の行に干渉を試みます。

START TRANSACTION;
UPDATE inventory SET stock = stock - 1 WHERE id = 5;
-- セッションBは待機状態（Waiting）になります。セッションAがロックを解放するまで待つ必要があります。

セッション A で COMMIT; と入力した瞬間に、セッション B は解放され、即座にコマンドが実行されます。これが最も基本的なロック形式である Row Lock です。

InnoDB における3つの強力なロックタイプの解明

多くの開発者は、MySQL が修正中の行だけをロックすると誤解しています。実際には、特にデフォルトの分離レベル（Isolation Level）である REPEATABLE READ では、より複雑な動きをします。

1. Record Lock（レコードロック）

これはインデックスに対して直接かけられるロックです。SELECT * FROM table WHERE id = 10 FOR UPDATE; を実行すると、MySQL は ID 10 の行をしっかりとロックします。トランザクションが終了するまで、誰もこの行を修正したり削除したりすることはできません。

2. Gap Lock（ギャップロック）

Gap Lock は特定の行ではなく、インデックス間の隙間（ギャップ）をロックします。これは最も混乱を招きやすい概念です。

例えば、inventory テーブルに ID 1、5、10 があるとします。1 と 5 の間の隙間は (2, 3, 4) で、5 と 10 の間の隙間は (6, 7, 8, 9) です。

もし SELECT * FROM inventory WHERE id BETWEEN 2 AND 4 FOR UPDATE; を実行した場合、ID 2, 3, 4 の行が存在しなくても、MySQL はその範囲全体をロックします。目的は、他のセッションが ID=3 の新しい行を INSERT するのを防ぐためです。このメカニズムにより、「ファントムリード（Phantom Read）」現象を排除できます。

3. Next-Key Lock

Next-Key Lock は Record Lock と Gap Lock を組み合わせたものです。特定のレコードとその直前の隙間の両方をロックします。

実際には、InnoDB はインデックスをスキャンする際に、データが予期せず変更されないように Next-Key Lock をよく使用します。これが、存在しない行を更新しようとしたときに、他人のインサート処理が待たされる理由です。

警告：ロック時にインデックスを忘れないこと

これは私の苦い経験から得た教訓です。InnoDB が行レベル（Row-level）でロックを行うのは、WHERE 句でインデックスを使用している場合のみです。

インデックスのないカラムに対して更新コマンドを実行したと想像してみてください。

-- item_name にインデックスがないと仮定
UPDATE inventory SET stock = 0 WHERE item_name = 'iPhone 15';

この場合、MySQL はテーブル全体をスキャン（Full Table Scan）せざるを得ません。その結果、テーブル内のすべての行がロックされます！ 大規模なデータベースでこれを行うのは、自らサーバーのプラグを抜くようなもので、他のすべてのトランザクションが無限に待機することになります。

ロックの状態を確認するには？

システムが急に重くなった場合、私はよく次のコマンドを使ってロックを保持している「犯人」を探します。

SELECT * FROM information_schema.innodb_trx;
-- または、エンジンの詳細レポートを表示
SHOW ENGINE INNODB STATUS;

同時実行性を最適化するための4つの黄金律

大規模なデータシステムでのトラブルシューティングを何度も経験し、以下の不変の原則を導き出しました。

  • トランザクションを極めて短くする: トランザクション内でサードパーティの API を呼び出したり、重い画像処理を行ったりしないでください。計算を済ませてから、トランザクションを開き、更新して、すぐにコミットします。
  • 常にインデックスに従う: テーブル全体の誤ロックを避けるため、すべての UPDATE や DELETE コマンドにはプライマリキーまたはインデックスを含める必要があります。
  • 操作の順序を一貫させる: トランザクション A が行 1 を修正してから行 2 を修正する場合、トランザクション B も同じ順序に従う必要があります。もし B が逆の順序（2 の次に行 1）で修正を行うと、デッドロックが確実に発生します。
  • 適切な分離レベルを選択する: 閲覧数カウントのように 100% の正確性を必要としない機能については、READ COMMITTED の使用を検討してください。これにより Gap Lock が軽減され、処理速度が明らかに向上します。

最後に

MySQL のロックメカニズムをマスターすることは、車の運転における交通ルールを理解することに似ています。最初は複雑に感じるかもしれませんが、一度習得すれば、データの不整合やシステムのフリーズを心配することなく、高負荷に耐えられるシステムを自信を持って設計できるようになります。

クエリの遅延に悩んでいる方は、データベースの最適化についてより包括的に理解するために、私の Slow Query Log に関する記事もぜひチェックしてみてください！

Share:
Tags: