InnoDBのロック方式とデッドロック・ギャップロック,調査方法

InnoDBのロック方式とデッドロック・ギャップロック

以前、トランザクションと排他制御(楽観ロック悲観ロック)の基礎知識
と言う記事を作成しましたが、
https://zenn.dev/airiswim/articles/ebe313fb39a4c9
デッドロックとギャップロックについて書いていきたいと思います。
（これに関連する障害に出会った時にロック周りの自分の知識の曖昧なことに気づいた。。。笑）

また、これらまとめたのち、最後にInnoDBのロックの仕組みに立ち返りまとめたいと思います。

📍ギャップロック（Gap Lock）とは

📚 基本概念

ギャップロックは、インデックス内のレコード間の「隙間」をロックする仕組み。

これにより、他のトランザクションが該当する範囲に新しいレコードを挿入することを防ぎます。

📚　ギャップロックが発生する条件

1. 分離レベルがREPEATABLE READ以上
2. 範囲検索や存在しないキーに対する検索
3. SELECT ... FOR UPDATE や SELECT ... LOCK IN SHARE MODE

💻 実際にギャップロックを発生させてみる

以下のテーブルを使って実験します：

-- テスト用テーブルの作成
CREATE TABLE lock_test (
    id INT PRIMARY KEY,
    name VARCHAR(50)
) ENGINE=InnoDB;

-- テストデータの挿入
INSERT INTO lock_test VALUES 
(1, 'Alice'),
(5, 'Bob'),
(10, 'Charlie');

🧪 実験1: 存在しないキーでギャップロックを発生させる

ターミナル1（トランザクションA）:

START TRANSACTION;
SELECT * FROM lock_test WHERE id = 3 FOR UPDATE;
-- ロックは取得されるが、結果は0件

ターミナル2（トランザクションB）:

START TRANSACTION;
-- 以下のINSERTはブロックされる（ギャップ(1,5)にロックがかかっているため）
INSERT INTO lock_test VALUES (2, 'David');  -- ブロック
INSERT INTO lock_test VALUES (3, 'Eve');    -- ブロック
INSERT INTO lock_test VALUES (4, 'Frank');  -- ブロック

-- しかし、他のギャップへの挿入は成功する
INSERT INTO lock_test VALUES (6, 'Grace');  -- 成功

トランザクションAで、存在しないキー(3)で取得したことにより、
トランザクションAの時点で、ギャップ（1~5）をロックした。
そのせいでトランザクションBでは２、３、４へのインサートは弾かれる結果となった。

🧪 実験2: 範囲検索でのギャップロック

ターミナル1（トランザクションA）:

START TRANSACTION;
SELECT * FROM lock_test WHERE id BETWEEN 3 AND 7 FOR UPDATE;

ターミナル2（トランザクションB):

START TRANSACTION;
-- (1,5)と(5,10)のギャップがロックされる
INSERT INTO lock_test VALUES (2, 'Helen');  -- ブロック
INSERT INTO lock_test VALUES (6, 'Ian');    -- ブロック
INSERT INTO lock_test VALUES (8, 'Jack');   -- ブロック

ギャップロック対策

1. 分離レベルの変更

ギャップロックはインデックスの種類ではなく、分離レベルに依存する

READ COMMITTEDではギャップロックが発生しない.
ただし、ファントムリードが発生する可能性があるためあまり宜しくはない。

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- ただし、ファントムリードが発生する可能性がある
START TRANSACTION;
SELECT COUNT(*) FROM orders WHERE user_id = 2;  -- 0件
-- 他のトランザクションで INSERT INTO orders (user_id, amount) VALUES (2, 100);
SELECT COUNT(*) FROM orders WHERE user_id = 2;  -- 1件（ファントムリード）
COMMIT;

２. 適切なクエリ設計

-- 悪い例：範囲検索でギャップロック発生
SELECT * FROM orders WHERE user_id BETWEEN 10 AND 20 FOR UPDATE;

-- 良い例：特定の値での検索
SELECT * FROM orders WHERE user_id IN (10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20) FOR UPDATE;

📍 デッドロック（Deadlock）とは

📚 基本概念

デッドロックは、
「2つ以上のトランザクションが互いに相手の持つリソースの解放を待ち続ける状態」です。
InnoDBはデッドロックを自動検出し、一方のトランザクションをロールバックします。

📚 デッドロックが発生する典型的なパターン

1. アクセス順序（排他を掛ける順序）に依存（循環待ち (Circular Wait)）

• トランザクション A がリソース X を保持し、リソース Y を待っている
• 同時に、トランザクション B がリソース Y を保持し、リソース X を待っている
  
  →　結果： A も B も相手が解放するまで待ち続けてしまう

2. 検索と更新との間の逆順処理をする

これは特にギャップロックやNext-Key Lockに関連する重要なポイントです：

• トランザクションA: SELECT ... FOR UPDATE → INSERT
• トランザクションB: INSERT → SELECT ... FOR UPDATE
  →　結果：ギャップロックによるデッドロック

💻 実際に起こしてみよう

🧪 実験1: 異なる順序でのリソースアクセス

-- セットアップ
CREATE TABLE accounts (
    id INT PRIMARY KEY,
    balance DECIMAL(10,2)
) ENGINE=InnoDB;

INSERT INTO accounts VALUES (1, 1000), (2, 1000);

1. ターミナル1（トランザクションA）:

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- アカウント1をロック

2. ターミナル2（トランザクションB）:

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 2;  -- アカウント2をロック

3. ターミナル1（トランザクションA）:

UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- アカウント2のロック待ち

4. ターミナル2（トランザクションB）:

UPDATE accounts SET balance = balance + 100 WHERE id = 1;  -- アカウント1のロック待ち→デッドロック！

結果として、一方のトランザクションで以下のエラーが発生します：

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

📚 デッドロック対策

1. 一貫したリソースアクセス順序

-- 悪い例：ランダムな順序でのアクセス
UPDATE table1 SET value = 1 WHERE id = @random_id1;
UPDATE table1 SET value = 2 WHERE id = @random_id2;

-- 良い例：常に同じ順序でアクセス
UPDATE table1 SET value = 1 WHERE id = LEAST(@id1, @id2);
UPDATE table1 SET value = 2 WHERE id = GREATEST(@id1, @id2);

2. トランザクションの短縮化

-- 悪い例：長時間のトランザクション
START TRANSACTION;
-- 重い処理
SELECT SLEEP(10);
-- 複数のテーブル操作
UPDATE table1 SET ...;
UPDATE table2 SET ...;
UPDATE table3 SET ...;
COMMIT;

-- 良い例：必要最小限のトランザクション
-- 事前準備
SET @prepared_data = ...;

START TRANSACTION;
UPDATE table1 SET col1 = @prepared_data WHERE id = 1;
UPDATE table2 SET col2 = @prepared_data WHERE id = 1;
COMMIT;

👓 調査について①：　InnoDBモニターの設定

💻 必要な設定

-- InnoDBロックモニターを有効化
SET GLOBAL innodb_status_output = ON;
SET GLOBAL innodb_status_output_locks = ON;

💻 デフォルト設定

設定項目	デフォルト値	説明
innodb_status_output	OFF	InnoDBの詳細状態出力の有効/無効
innodb_status_output_locks	OFF	ロック情報の詳細出力の有効/無効

重要: デフォルトではロックの詳細情報は表示されない。サーバーを落としたらOFFに戻る

👀 現在の設定を確認

-- グローバル設定の確認
SELECT @@GLOBAL.innodb_status_output, @@GLOBAL.innodb_status_output_locks;

-- セッション設定の確認
SELECT @@innodb_status_output, @@innodb_status_output_locks;

👓 調査について②： ロック情報の確認方法

1. SHOW ENGINE INNODB STATUS(デッドロック情報の確認)

-- InnoDBの詳細状態を確認
SHOW ENGINE INNODB STATUS\G

2. ロック待機状況の確認

-- ロック待機しているトランザクション
SELECT 
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread,
    r.trx_query waiting_query,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_query blocking_query
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

3. パフォーマンススキーマでの確認

-- 現在のロック状況
SELECT 
    ENGINE_TRANSACTION_ID,
    OBJECT_NAME,
    LOCK_TYPE,
    LOCK_MODE,
    LOCK_STATUS,
    LOCK_DATA
FROM performance_schema.data_locks
WHERE OBJECT_NAME = 'your_table_name';

📍InnoDBエンジンが実際に使用しているロック方式について

InnoDBには以下の主要なロックがあります：

ロックタイプ	説明	効果
Record Lock （レコードロック）	特定のインデックスレコードだけをロック	他のトランザクションが そのレコードを変更・削除するのを防ぐ
Gap Lock （ギャップロック）	インデックスレコード間の「隙間」をロック	他のトランザクションが その隙間に新しいレコードを挿入するのを防ぐ
Next-Key Lock （次キーロック）	Record Lock + Gap Lockの組み合わせ	レコード自体と その前のギャップの両方をロック
Insert Intention Lock （挿入意図ロック）	行を挿入する前に設定される 特殊なギャップロック	同じギャップ内の異なる位置への挿入は競合しない

Next-Key Lockについて

Next-Key Lockは以下の2つのロックの組み合わせです：

• Record Lock（レコードロック）: 特定のレコード（行）をロック
• Gap Lock（ギャップロック）: レコード間の「隙間」をロック

InnoDBのデフォルト分離レベル（REPEATABLE READ）では、
ファントムリードを防ぐ必要があります。Next-Key Lockにより、以下の問題を解決します：

1. ファントムリードの防止: 他のトランザクションの挿入を防ぐ
2. 一貫性の保証: 同じトランザクション内で同じ結果を返す
3. 予測可能な動作: ロックの範囲が明確

要するに効果としては、

• レコードの変更・削除を防ぐ（Record Lock）
• 新しい行の挿入を防ぐ（Gap Lock）
• 同じクエリで常に同じ結果を保証

ファントムリードとは:
同じトランザクション内で同じSELECTクエリを実行したとき、
他のトランザクションが挿入した新しい行が突然現れる現象のことです。

✏️ 補足情報

＜READ COMMITTED分離レベルの場合＞

• Gap Lockは使用されない
• Record Lockのみ使用
• ファントムリードが発生する可能性がある

＜パフォーマンスへの影響＞

• Next-Key Lockは広範囲をロックするため、同時実行性が下がる場合がある
• 必要に応じて分離レベルを調整することを検討

実際にかかってるのか確かめてみよう！

実際に確かめてみよう！

実験1：基本的なNext-Key Lockの動作

test用データの準備

CREATE TABLE lock_test (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- テストデータ挿入
INSERT INTO lock_test VALUES 
(1, 'Alice'),
(3, 'David'),
(5, 'Bob'),
(15, 'Eve');

-- 現在のデータ確認
SELECT * FROM lock_test ORDER BY id;

2つのMySQLセッション（ターミナル）を開いて、以下を実行してみてください。

セッション1（トランザクションA）:

-- セッション1
START TRANSACTION;
SELECT * FROM lock_test WHERE id = 10 FOR UPDATE;

重要なポイント:
id = 10 は存在しないレコードですが、
InnoDBは id = 10 を含む可能性のある範囲をロックします。
この場合、(5, 15) のギャップがロックされます。

セッション2（トランザクションB）:

-- セッション2
START TRANSACTION;
INSERT INTO lock_test VALUES (12, 'Test');
-- → ブロックされる

なぜ id = 12 の挿入がブロックされるのか？

1. 存在しないレコードへのロック: WHERE id = 10 で存在しないレコードを指定
2. ギャップロックの発生: InnoDBは id = 10 が存在する可能性のあるギャップ (5, 15) をロック
3. 挿入のブロック: id = 12 はこのギャップ内なので挿入がブロックされる

---

今回はこれで以上です！
また色々試しながら理解を上げていけたらと思う! :)