InnoDBのロックについて理解する
はじめに
データベースのロックといえば、「共有ロック」や「排他ロック」という言葉を聞いたことがある方も多いでしょう。しかし、実際にロックの状況を確認したことはありますか？
MySQLでは、performance_schema.data_locks テーブルを利用することで、現在どのようなロックが取得されているのかを確認できます。本記事では、MySQLのロックを実際に取得しながら data_locks テーブルを観察し、ロックの仕組みを学んでいきます。
共有ロックとは？
SELECT ... FOR SHARE を使用すると、共有ロックが取得されます。これは「私がこのデータを見ているから、更新しないでね」といった制約を課すロックです。
共有ロック同士は競合しませんが、共有ロックと排他ロックは競合します。
共有ロックの動作例
-- トランザクション 1
BEGIN;
SELECT * FROM users WHERE id = 1 FOR SHARE;
-- トランザクション 2
SELECT * FROM users WHERE id = 1; -- OK (通常のSELECTは競合しない)
SELECT * FROM users WHERE id = 1 FOR SHARE; -- OK (共有ロック同士は競合しない)
UPDATE users SET name = 'Alice' WHERE id = 1; -- 待機状態 (共有ロックと排他ロックは競合する)
この状態で performance_schema.data_locks を確認すると、以下のような結果になります。
mysql> SELECT ENGINE, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
+--------+-----------+---------------+-------------+-----------+
| ENGINE | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+-----------+---------------+-------------+-----------+
| INNODB | TABLE | IS | GRANTED | NULL |
| INNODB | RECORD | S,REC_NOT_GAP | GRANTED | 1 |
+--------+-----------+---------------+-------------+-----------+
-
LOCK_TYPE=TABLEのLOCK_MODE=ISは、共有ロックを取得する宣言です。 -
LOCK_TYPE=RECORDのLOCK_MODE=Sは、特定のレコード（id=1）に対する共有ロックを示します。
排他ロックとは？
SELECT ... FOR UPDATE や UPDATE、DELETE などの操作では、排他ロックが取得されます。これは「私がこのデータを更新するので、他のトランザクションは見たり変更したりしないでね」という制約を課すロックです。
排他ロックと共有ロック、または排他ロック同士は競合します。
排他ロックの動作例
-- トランザクション 1
BEGIN;
SELECT * FROM users WHERE id = 1 FOR UPDATE;
-- トランザクション 2
SELECT * FROM users WHERE id = 1; -- OK (通常のSELECTは競合しない)
SELECT * FROM users WHERE id = 1 FOR SHARE; -- 待機状態 (排他ロックと共有ロックは競合する)
UPDATE users SET name = 'Bob' WHERE id = 1; -- 待機状態 (排他ロック同士は競合する)
この状態で performance_schema.data_locks を確認すると、以下のような結果になります。
mysql> SELECT ENGINE, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
+--------+-----------+---------------+-------------+-----------+
| ENGINE | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+-----------+---------------+-------------+-----------+
| INNODB | TABLE | IX | GRANTED | NULL |
| INNODB | RECORD | X,REC_NOT_GAP | GRANTED | 1 |
+--------+-----------+---------------+-------------+-----------+
-
LOCK_TYPE=TABLEのLOCK_MODE=IXは、排他ロックを取得する宣言です。 -
LOCK_TYPE=RECORDのLOCK_MODE=Xは、特定のレコード（id=1）に対する排他ロックを示します。
レコードロックとギャップロック
InnoDB では、レコードに対するロックを取得する際に ギャップロック が発生することがあります。ギャップロックは、レコードとレコードの間のギャップに対するロックで、新しいレコードの挿入を防ぎ、ファントムリード を防止します。
ギャップロックの動作例
例えば、次のような users テーブルがあるとします。
mysql> SELECT * FROM users;
+----+------+------+
| id | name | age |
+----+------+------+
| 10 | A | 11 |
| 20 | B | 22 |
| 30 | C | 33 |
+----+------+------+
ここで、以下のクエリを実行すると、
-- トランザクション 1
BEGIN;
SELECT * FROM users WHERE id BETWEEN 10 AND 25 FOR UPDATE;
performance_schema.data_locks は次のようになります。
mysql> SELECT ENGINE, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
+--------+-----------+---------------+-------------+-----------+
| ENGINE | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+-----------+---------------+-------------+-----------+
| INNODB | TABLE | IX | GRANTED | NULL |
| INNODB | RECORD | X,REC_NOT_GAP | GRANTED | 10 |
| INNODB | RECORD | X | GRANTED | 20 |
| INNODB | RECORD | X,GAP | GRANTED | 30 |
+--------+-----------+---------------+-------------+-----------+
それぞれのLOCK_MODEについて見ていきます。
LOCK_DATA=10(id=10)は、REC_NOT_GAPと記載があり、ギャップロックでないレコードロックです。
LOCK_DATA=20(id=20)は、特にギャップに関する記載はありません。この場合はid=20の手前のギャップロックとid=20のレコードロックです。このギャップロックとレコードロックを足したロックをネクストキーロックと呼びます。
LOCK_DATA=30(id=30)は、GAPと記載があり、ギャップロックのみだということが分かります。
つまり、id=10, 10<id<=20, 20<id<30 の範囲にロックがかかっている 状態になります。
まとめ
本記事では、MySQL の performance_schema.data_locks を利用して、共有ロック・排他ロック・ギャップロックの実際の動作を観察しました。
- 共有ロック は、他トランザクションからの変更を防ぐロック
- 排他ロック は、他トランザクションからの変更と参照を防ぐロック
- レコードロック は、レコードに対する対するロック
- ギャップロック は、レコード間のギャップに対するロック
これらのロックの仕組みを理解することは、SQLの効率を意識できたり保守運用時に役に立ったりするはずです。ぜひ、実際に試してみてください！
Discussion