はじめに
MySQL の InnoDB では、トランザクション分離レベルや検索方法によってロックの取り方が異なります。
本記事では、REPEATABLE READ と READ COMMITTED の 2 つの分離レベルにおいて、主キーを使った DELETE 時のロック挙動を実際に検証した結果についてまとめていこうと思います。
検証環境
- MacOS
- MySQL 9.4.0 (Homebrew)
検証準備
テストテーブルの作成
users テーブルを作成し、データを入れます。
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50)
);
INSERT INTO users VALUES
(10, 'Alice'),
(20, 'Bob'),
(30, 'Charlie'),
(50, 'David'),
(100, 'Eve');
ロック確認用クエリ
もう一つ別のセッションで mysql にアクセスし、ロック状況を確認します。
ロック状況は performance_schema.data_locks で確認できます。
SELECT LOCK_TYPE, LOCK_MODE, LOCK_DATA
FROM performance_schema.data_locks
WHERE OBJECT_NAME = 'users';
検証結果
今回は以下の 3 つの検索方法を検証します。
| 検索方法 | SQL 例 |
|---|---|
| 主キー等価検索（存在する） | DELETE FROM users WHERE id = 30 |
| 主キー等価検索（存在しない） | DELETE FROM users WHERE id = 25 |
| 主キー範囲検索 | DELETE FROM users WHERE id BETWEEN 20 AND 40 |
検証 1: 主キー等価検索（存在するレコード）
WHERE id = 30 で存在するレコードを削除してみましょう。
REPEATABLE READ
一つ目のセッションで作業を実行していきます。
この時、ロックモニタを確認するまで COMMIT をしないようにしてください。
mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.009 sec)
mysql> BEGIN;
Query OK, 0 rows affected (0.000 sec)
mysql> DELETE FROM users WHERE id = 30;
Query OK, 1 row affected (0.002 sec)
二つ目のセッションでロックモニタを確認してみましょう。
mysql> SELECT LOCK_TYPE, LOCK_MODE, LOCK_DATA
-> FROM performance_schema.data_locks
-> WHERE OBJECT_NAME = 'users';
+-----------+---------------+-----------+
| LOCK_TYPE | LOCK_MODE | LOCK_DATA |
+-----------+---------------+-----------+
| TABLE | IX | NULL |
| RECORD | X,REC_NOT_GAP | 30 |
+-----------+---------------+-----------+
2 rows in set (0.001 sec)
READ COMMITTED
一つ目のセッションで作業を実行していきます。
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.015 sec)
mysql> BEGIN;
Query OK, 0 rows affected (0.000 sec)
mysql> DELETE FROM users WHERE id = 30;
Query OK, 1 row affected (0.003 sec)
二つ目のセッションでロックモニタを確認してみましょう。
mysql> SELECT LOCK_TYPE, LOCK_MODE, LOCK_DATA FROM performance_schema.data_locks WHERE OBJECT_NAME = 'users';
+-----------+---------------+-----------+
| LOCK_TYPE | LOCK_MODE | LOCK_DATA |
+-----------+---------------+-----------+
| TABLE | IX | NULL |
| RECORD | X,REC_NOT_GAP | 30 |
+-----------+---------------+-----------+
2 rows in set (0.003 sec)
結果
| 分離レベル | ロック |
|---|---|
| REPEATABLE READ |
X,REC_NOT_GAP on 30 |
| READ COMMITTED |
X,REC_NOT_GAP on 30 |
この場合はどの分離レベルでもロックの差が出ませんね。主キーで一意に特定できるため、どちらもレコードロックのみかかるようです。
検証 2: 主キー等価検索（存在しないレコード）
では次に、WHERE id = 25 で存在しないレコードを削除ようとしてみましょう。
REPEATABLE READ
一つ目のセッションで作業を実行していきます。
mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.016 sec)
mysql> BEGIN;
Query OK, 0 rows affected (0.000 sec)
mysql> DELETE FROM users WHERE id = 25;
Query OK, 0 rows affected (0.001 sec)
二つ目のセッションでロックモニタを確認してみましょう。
mysql> SELECT LOCK_TYPE, LOCK_MODE, LOCK_DATA
-> FROM performance_schema.data_locks
-> WHERE OBJECT_NAME = 'users';
+-----------+-----------+-----------+
| LOCK_TYPE | LOCK_MODE | LOCK_DATA |
+-----------+-----------+-----------+
| TABLE | IX | NULL |
| RECORD | X,GAP | 30 |
+-----------+-----------+-----------+
2 rows in set (0.001 sec)
READ COMMITTED
一つ目のセッションで作業を実行していきます。
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.004 sec)
mysql> BEGIN;
Query OK, 0 rows affected (0.001 sec)
mysql> DELETE FROM users WHERE id = 25;
Query OK, 0 rows affected (0.001 sec)
二つ目のセッションでロックモニタを確認してみましょう。
mysql> SELECT LOCK_TYPE, LOCK_MODE, LOCK_DATA
-> FROM performance_schema.data_locks
-> WHERE OBJECT_NAME = 'users';
+-----------+-----------+-----------+
| LOCK_TYPE | LOCK_MODE | LOCK_DATA |
+-----------+-----------+-----------+
| TABLE | IX | NULL |
+-----------+-----------+-----------+
1 row in set (0.003 sec)
結果
| 分離レベル | ロック |
|---|---|
| REPEATABLE READ |
X,GAP on 30（20〜30 間のギャップ） |
| READ COMMITTED | なし（IX のみ） |
今度は変化が出てきました。REPEATABLE READ では、id=25 が挿入されうるギャップ（20〜30 の間）をロックするようです。一方 READ COMMITTED ではギャップロックを取らないという結果となりました。
検証 3: 主キー範囲検索
最後に、WHERE id BETWEEN 20 AND 40 で範囲削除する場合についても検証しましょう。
REPEATABLE READ
一つ目のセッションで作業を実行していきます。
mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.004 sec)
mysql> BEGIN;
Query OK, 0 rows affected (0.001 sec)
mysql> DELETE FROM users WHERE id BETWEEN 20 AND 40;
Query OK, 2 rows affected (0.002 sec)
二つ目のセッションでロックモニタを確認してみましょう。
mysql> SELECT LOCK_TYPE, LOCK_MODE, LOCK_DATA FROM performance_schema.data_locks WHERE OBJECT_NAME = 'users';
+-----------+---------------+-----------+
| LOCK_TYPE | LOCK_MODE | LOCK_DATA |
+-----------+---------------+-----------+
| TABLE | IX | NULL |
| RECORD | X,GAP | 50 |
| RECORD | X | 30 |
| RECORD | X,REC_NOT_GAP | 20 |
+-----------+---------------+-----------+
4 rows in set (0.001 sec)
READ COMMITTED
一つ目のセッションで作業を実行していきます。
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.051 sec)
mysql> BEGIN;
Query OK, 0 rows affected (0.000 sec)
mysql> DELETE FROM users WHERE id BETWEEN 20 AND 40;
Query OK, 2 rows affected (0.004 sec)
二つ目のセッションでロックモニタを確認してみましょう。
mysql> SELECT LOCK_TYPE, LOCK_MODE, LOCK_DATA FROM performance_schema.data_locks WHERE OBJECT_NAME = 'users';
+-----------+---------------+-----------+
| LOCK_TYPE | LOCK_MODE | LOCK_DATA |
+-----------+---------------+-----------+
| TABLE | IX | NULL |
| RECORD | X,REC_NOT_GAP | 20 |
| RECORD | X,REC_NOT_GAP | 30 |
+-----------+---------------+-----------+
3 rows in set (0.003 sec)
結果
| 分離レベル | ロック |
|---|---|
| REPEATABLE READ |
X,REC_NOT_GAP on 20, X on 30, X,GAP on 50 |
| READ COMMITTED |
X,REC_NOT_GAP on 20, X,REC_NOT_GAP on 30 |
こちらにも差が生じました。 REPEATABLE READ では id=30 に対してネクストキーロック（X）を取得（レコード+前方ギャップ）し、追加で id=50 に対してギャップロック（X,GAP）を取得（30〜50 間のギャップ）しました。
READ COMMITTED では削除対象のレコードのみをロックしています。
まとめ
今回の検証で得られた結果をまとめます。
| 検索パターン | REPEATABLE READ | READ COMMITTED |
|---|---|---|
| 主キー等価（存在する） | レコードロック | レコードロック |
| 主キー等価（存在しない） | ギャップロック | ロックなし |
| 主キー範囲 | レコード+ギャップロック | レコードロックのみ |
REPEATABLE READ では検索用途によって取得するロックが異なります。想定していないギャップロックはサービス影響を与えるのでオペレーションによっては注意が必要そうです。
READ COMMITTED はギャップロックを取らないため、同時実行性が高い反面、ファントムリードが発生する可能性がありそうですね。
削除時は要件に応じて分離レベルを選択するとよさそうです。