#はじめに
この記事では、MySQL8.0でのロックがどのようにおこなわれているのか、performance_schema.data_locksの中身を出力することで確認することを目的にします。
#準備
- バージョン：MySQL8.0（InnoDB）
- 分離レベル：REPEATABLE READ（デフォルト）
今回SELECTするのは以下テーブルです。
CREATE TABLE `user` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL DEFAULT '',
`age` int NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB
あらかじめ数件INSERTしてデータを作ってあります。
+----+------+-----+
| id | name | age |
+----+------+-----+
| 1 | aaa | 35 |
| 5 | bbb | 22 |
| 10 | ccc | 43 |
+----+------+-----+
#MySQLのロックおさらい
MySQLのロック機構には、どんな種類のロックなのかや、どんな粒度のロックなのか、なにに対するロックなのかといった側面から説明される、いくつかの概念が存在するので、かんたんにまとめます。
##共有ロック or 排他ロック（どんな種類のロックか）
共有（S）ロックと排他（X）ロックの違いは、そのロックを与えられたトランザクションが該当の行に対して、どんな操作が許されるのか、ということを示します。
-
共有（S）ロック：読み取りを許可
-
排他（X）ロック：更新、削除を許可
##行ロック or テーブルロック（どんな粒度のロックか）
MySQLのロックでは、個別の行に対するロックをとることもあれば、テーブル全体に対するロックをとることもあります。
テーブル全体に対するロックというのは後続トランザクションに与える影響が大きいので、できるだけ個別の行ロックの取得で参照や更新を済ませることがパフォーマンス向上にとって重要だとおもわれます。
MySQLでは、行ロック取得時にもインテンションロックというテーブルに対するロックが取得される仕様になっており、複数の粒度のロックを成立させるための工夫が施されています。
##レコードロック or ギャップロック or ネクストキーロック（何に対するロックか）
- レコードロック：インデックスレコードに対するロックを示します。MySQLでは、行ロックを実現する手段として、テーブルの行そのものに対するロックを取得するのではなく、テーブルに生成されたインデックスのレコードに対するロックを取得することで実現しています。そのため、インデックスを明示的に作成しなかったテーブルに対しても、内部的にインデックスが生成される仕様になっています。
- ギャップロック：インデックスレコードとインデックスレコードの間に対するロックを示します。ギャップロック同士は競合しないという仕様があるのですが、この仕様ゆえにデッドロックが発生することがあり、注意が必要です。
- ネクストキーロック：レコードロックとギャップロックの組み合わせを示し、あるインデックスレコードのロックと、そのレコードの前のギャップ（間）に対するギャップロックを意味します。
#PRIMARY KEY のインデックスを利用してSELECTする。
##単一のトランザクションの場合
###レコードロック
- SELECT実行
mysql> SELECT * FROM user WHERE id = 1 FOR UPDATE;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 1 | aaa | 35 |
+----+------+-----+
1 row in set (0.00 sec)
- ロック状況
ロックの確認には、performance_schema.data_locksを利用します。
mysql> select THREAD_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks;
+-----------+-------------+------------+-----------+---------------+-------------+-----------+
| THREAD_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------+-------------+------------+-----------+---------------+-------------+-----------+
| 50 | user | NULL | TABLE | IX | GRANTED | NULL |
| 50 | user | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 1 |
+-----------+-------------+------------+-----------+---------------+-------------+-----------+
2 rows in set (0.00 sec)
1行目は LOCK_MODE = IX であり、テーブルに対するインテンション排他ロック。
2行目は LOCK_MODE = X,REC_NOT_GAP（排他のレコードロック）であり、LOCK_DATA にある1というインデックス値に対するインデックスレコードをロックすることにより、行ロックを実現しています。
いずれの行もLOCK_STATUSはGRANTEDなので、ロックが取得できていることがわかります。
これがロック待ち状態であれば、WAITINGと表示されます。
###ギャップロック
- SELECT実行
mysql> SELECT count(*) FROM user WHERE id = 2 FOR UPDATE;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
- ロック状況
mysql> SELECT THREAD_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
+-----------+-------------+------------+-----------+-----------+-------------+-----------+
| THREAD_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------+-------------+------------+-----------+-----------+-------------+-----------+
| 50 | user | NULL | TABLE | IX | GRANTED | NULL |
| 50 | user | PRIMARY | RECORD | X,GAP | GRANTED | 5 |
+-----------+-------------+------------+-----------+-----------+-------------+-----------+
2 rows in set (0.00 sec)
1行目はさきほどと同じく、テーブルに対するインテンション排他ロック。
2行目は、LOCK_MODE = X,GAP なので、排他のギャップロックが取得されています。
LOCK_DATA = 5 なので、1と5の間のギャップに対するロックが取得されています。
###ネクストキーロック
- SELECT実行
mysql> SELECT count(*) FROM user WHERE id > 5 FOR UPDATE;
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
- ロック状況
mysql> select THREAD_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks;
+-----------+-------------+------------+-----------+-----------+-------------+------------------------+
| THREAD_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------+-------------+------------+-----------+-----------+-------------+------------------------+
| 50 | user | NULL | TABLE | IX | GRANTED | NULL |
| 50 | user | PRIMARY | RECORD | X | GRANTED | supremum pseudo-record |
| 50 | user | PRIMARY | RECORD | X | GRANTED | 10 |
+-----------+-------------+------------+-----------+-----------+-------------+------------------------+
3 rows in set (0.00 sec)
SELECT文では、idが5より大きいものを指定したので、この範囲に対するロックが取得される必要があります。
まず3行目では、LOCK_MODE が X で、LOCK_DATA が 10 となるロックが取得されています。
LOCK_MODE の X というのは、排他のネクストキーロックを示しており、LOCK_DATA が 10 なので、5と10の間のギャップと、10というインデックス値に対するレコードのロックが取得されたことを意味します。
加えて、2行目の LOCK_DATA = supremum pseudo-record は疑似的な上限値を示しており、id の値として 10 より大きな値の範囲全体に対するロックが取得されています。
##複数トランザクションの場合
###ロック待ちする SELECT を実行
ここでは、さきほど実行した id = 1 に対する SELECT が同時に実行されたときのロック状況を確認してみます。
- Tr1（トランザクション1）
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM user WHERE id = 1 FOR UPDATE;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 1 | aaa | 35 |
+----+------+-----+
1 row in set (0.00 sec)
- Tr2（トランザクション2）
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM user WHERE id = 1 FOR UPDATE;
（※ 結果が返ってこず待たされる）
- ロック状況
mysql> SELECT THREAD_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
+-----------+-------------+------------+-----------+---------------+-------------+-----------+
| THREAD_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------+-------------+------------+-----------+---------------+-------------+-----------+
| 51 | user | NULL | TABLE | IX | GRANTED | NULL |
| 51 | user | PRIMARY | RECORD | X,REC_NOT_GAP | WAITING | 1 |
| 50 | user | NULL | TABLE | IX | GRANTED | NULL |
| 50 | user | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 1 |
+-----------+-------------+------------+-----------+---------------+-------------+-----------+
4 rows in set (0.00 sec)
3,4 行目は Tr1 に関する行で、テーブルのインテンション排他ロックと、 id = 1 のインデックスレコードの排他ロックを示します。
その後、 Tr2 で同じSQLを発行すると、すでに id = 1 のインデックスは排他ロックがとられているのでロック待ちになり、2行目にあるように LOCK_STATUS が WAITING となる、 LOCK_MODE = X, REC_NOT_GAP （排他レコードロック）の行があらわれます。
なお、インテンション排他ロックはインテンション排他ロックと競合せず互換するため、Tr2 のテーブルに対するインテンション排他ロック（1行目）は GRANTED となります。
また、このまま Tr2 を放っておくと、ロックタイムアウトをこえたタイミングで「ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction」 となり、エラーになります。
###ロック待ちしない SELECT を実行
ここでは、Tr1 で実行するSQLはさきほどと同一でも、Tr2 であとから実行するSQLでは、Where句で指定する id として、存在するほかの id を指定してみます。
- Tr1
mysql> SELECT * FROM user WHERE id = 1 FOR UPDATE;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 1 | aaa | 35 |
+----+------+-----+
1 row in set (0.00 sec)
- Tr2
mysql> SELECT * FROM user WHERE id = 5 FOR UPDATE;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 5 | bbb | 22 |
+----+------+-----+
1 row in set (0.00 sec)
今度はさきほどと違って、SELECT結果が返ってきます。
- ロック状況
mysql> SELECT THREAD_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
+-----------+-------------+------------+-----------+---------------+-------------+-----------+
| THREAD_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------+-------------+------------+-----------+---------------+-------------+-----------+
| 51 | user | NULL | TABLE | IX | GRANTED | NULL |
| 51 | user | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 5 |
| 50 | user | NULL | TABLE | IX | GRANTED | NULL |
| 50 | user | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 1 |
+-----------+-------------+------------+-----------+---------------+-------------+-----------+
4 rows in set (0.00 sec)
今回は、LOCK_STATUS がすべて GRANTED になっていることが確認できます。
3, 4行目は Tr1 によって取得されたロックを示し、今までと同様です。
1, 2行目は Tr2 によって取得されたロックを示し、2行目は値が5であるインデックスレコードの行ロックが取得できたことを示しています。