はじめに
業務でデータベースの削除オペレーションを行う際、手順確認のために分離レベルごとのロック挙動を調査する機会がありました。
本記事では、インデックスのないカラムに対して LIMIT 付きの DELETE を実行した場合、REPEATABLE READ と READ COMMITTED でロックの取り方にどのような違いが出るかを検証した結果をまとめます。
検証環境
- MacOS
- MySQL 9.4.0 (Homebrew)
検証準備
テストテーブルの作成
users テーブルを作成し、データを入れます。name カラムにはインデックスを付けていません。
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> SELECT * FROM users;
+-----+---------+
| id | name |
+-----+---------+
| 10 | Alice |
| 20 | Bob |
| 30 | Charlie |
| 50 | David |
| 100 | Eve |
+-----+---------+
5 rows in set (0.001 sec)
ロック確認用クエリ
別のセッションで mysql にアクセスし、ロック状況を確認します。
ロック状況は performance_schema.data_locks で確認できます。
SELECT LOCK_TYPE, LOCK_MODE, LOCK_DATA
FROM performance_schema.data_locks
WHERE OBJECT_NAME = 'users';
検証
インデックスのないカラム name を条件にして、LIMIT 1 付きで DELETE を実行します。
DELETE FROM users WHERE name = 'Bob' LIMIT 1;
REPEATABLE READ
一つ目のセッションで作業を実行していきます。
この時、ロックモニタを確認するまで COMMIT しないでください。
mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.003 sec)
mysql> BEGIN;
Query OK, 0 rows affected (0.000 sec)
mysql> DELETE FROM users WHERE name = 'Bob' LIMIT 1;
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 | 10 |
| RECORD | X | 20 |
+-----------+-----------+-----------+
3 rows in set (0.004 sec)
READ COMMITTED
一つ目のセッションで作業を実行していきます。
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.001 sec)
mysql> BEGIN;
Query OK, 0 rows affected (0.008 sec)
mysql> DELETE FROM users WHERE name = 'Bob' LIMIT 1;
Query OK, 1 row 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 |
+-----------+---------------+-----------+
2 rows in set (0.003 sec)
結果
| 分離レベル | ロック |
|---|---|
| REPEATABLE READ |
X on 10, X on 20（ネクストキーロック） |
| READ COMMITTED |
X,REC_NOT_GAP on 20（レコードロックのみ） |
考察
REPEATABLE READ の挙動
LIMIT 1 で 1 件だけ削除しているにもかかわらず、id=10（Alice）にもロックがかかっています。
これは name カラムにインデックスがないため、MySQL がフルテーブルスキャンを行い、Bob を探す過程でスキャンしたレコードにロックを取得するためです。
READ COMMITTED の挙動
実際に削除した id=20（Bob）のみにロックがかかっています。
X,REC_NOT_GAP はレコードロックのみでギャップロックを含みません。READ COMMITTED ではスキャン中に条件に合致しなかったレコードのロックは解放されるため、最終的に削除対象のみがロックされます。
まとめ
| 分離レベル | ロック対象 | ギャップロック |
|---|---|---|
| REPEATABLE READ | スキャンしたレコードすべて | あり |
| READ COMMITTED | 削除対象のレコードのみ | なし |
インデックスのないカラムを条件にした DELETE では、REPEATABLE READ は想定以上のロックを取得する可能性があります。特に大きなテーブルでは、LIMIT 1 であってもフルテーブルスキャンが発生し、多くのレコードがロックされてしまう恐れがあります。
本番でのデータ削除オペレーションでは、この挙動を理解した上で分離レベルの選択やインデックスの活用を検討することが重要です。