MySQL(InnoDB)の行ロック

前回、InnoDBの共有ロック、排他ロック、インテンションロック（テーブルロック）について記事を書いたので、今回は行ロックについて記事にしました。

MySQL(InnoDB)共有ロックと排他ロックとインテンションロック（テーブルロック）

InnoDBの行ロックは次の3種類で、それぞれに共有ロックと排他ロックがあります。

• レコードロック
• ギャップロック
• インサートインテンションロック
• ネクストキーロック

これらのロック状況は、SHOW ENGINE INNODB STATUS  で確認でき、次のように表示されます。

お試し環境

• MySQL 8.0.19
• トランザクション分離レベル REPEATABLE READ

次のid（PRIMARY KEY）とvalue(varchar)の2カラムのテーブルを作成してロックを実際に試しています。

CREATE TABLE `tbl` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `value` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

レコードは次の通りで、idが歯抜けになるようにしています。

レコードロック

インデックスの値そのものにかけられるロック。（インデックスレコードがロックされる）

レコードロックでは、テーブルにインデックスが定義されていなくても必ず、インデックスレコードがロックされる。この場合、InnoDBによって非表示のクラスタ化されたインデックスが作成され、このインデックスを使用してレコードロックが行われる。

例えば、次のクエリを実行した場合（排他ロックのレコードロック）、他のトランザクションから t.c1 = 10 の行に対するINSERT, UPDATE, DELETEはできません。

SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;

レコードロックを試してみる

次のクエリを実行し、排他ロックのレコードロックを獲得。
SHOW ENGINE INNODB STATUSでトランザクションの状況を確認。

mysql> BEGIN;
mysql> SELECT * FROM tbl WHERE id = 10 FOR UPDATE;

このときのトランザクションの状況（SHOW ENGINE INNODB STATUSの結果を抜粋）

---TRANSACTION 5720, ACTIVE 8 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 56, OS thread handle 123145525313536, query id 1109 localhost root
TABLE LOCK table `learn_sql`.`tbl` trx id 5720 lock mode IX
RECORD LOCKS space id 77 page no 4 n bits 80 index PRIMARY of table `learn_sql`.`tbl` trx id 5720 lock_mode X locks rec but not gap
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 8; hex 800000000000000a; asc ;;
1: len 6; hex 000000001636; asc 6;;
2: len 7; hex 820000008d0154; asc T;;
3: len 3; hex 656565; asc eee;;

テーブルにIX（インテンション排他ロック）、1レコードに排他ロックのレコードロックがかかっているのを確認。

次に、別のトランザクションからレコードロックをかけた id=10 のレコードをUPDATE。

mysql> BEGIN;
mysql> UPDATE tbl SET value = 'EEE' WHERE id = 10;

しばらく時間がかかり、Lock wait timeout exceeded

このときのトランザクションの状況（SHOW ENGINE INNODB STATUSの結果を抜粋）

---TRANSACTION 5721, ACTIVE 2 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 57, OS thread handle 123145525919744, query id 1123 localhost root updating
UPDATE tbl SET value = 'EEE' WHERE id = 10
------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 77 page no 4 n bits 80 index PRIMARY of table `learn_sql`.`tbl` trx id 5721 lock_mode X locks rec but not gap waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 8; hex 800000000000000a; asc ;;
1: len 6; hex 000000001636; asc 6;;
2: len 7; hex 820000008d0154; asc T;;
3: len 3; hex 656565; asc eee;;

レコードロックは獲得待ちになってます。
（TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:）

ギャップロック

インデックスとインデックスの間にかけられるロック。

例えば、次のクエリを実行すると、idが5~10の範囲の値の間のギャップがロックされます。
そのため、レコードが存在するかに関係なく、他のトランザクションが、id: 6のような範囲内の値をINSERTしようとするとロック待ちになります。

SELECT * FROM tbl WHERE id BETWEEN 5 AND 10 FOR UPDATE;

一意のインデックスを使って、一意の行を検索する場合は、ギャップロックは発生しません！
例えば、次のようなクエリ。（idに一意のインデックスが貼られていて、id = x で1行を指定）

SELECT * FROM tbl WHERE id = 10;

まあ、↓のスライドの方がわかりやすいかな...

ギャップロックを試してみる

次のクエリを実行し、SHOW ENGINE INNODB STATUSでトランザクションの状況を確認。

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM tbl WHERE id BETWEEN 5 AND 10  FOR UPDATE;
+----+-------+
| id | value |
+----+-------+
|  5 | ddd   |
| 10 | eee   |
+----+-------+

ロックの状況確認。

---TRANSACTION 5722, ACTIVE 14 sec
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 56, OS thread handle 123145525313536, query id 1127 localhost root
TABLE LOCK table `learn_sql`.`tbl` trx id 5722 lock mode IX
RECORD LOCKS space id 77 page no 4 n bits 80 index PRIMARY of table `learn_sql`.`tbl` trx id 5722 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 8; hex 8000000000000005; asc         ;;
 1: len 6; hex 000000001636; asc      6;;
 2: len 7; hex 820000008d0143; asc       C;;
 3: len 3; hex 646464; asc ddd;;

RECORD LOCKS space id 77 page no 4 n bits 80 index PRIMARY of table `learn_sql`.`tbl` trx id 5722 lock_mode X
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 8; hex 800000000000000a; asc         ;;
 1: len 6; hex 000000001636; asc      6;;
 2: len 7; hex 820000008d0154; asc       T;;
 3: len 3; hex 656565; asc eee;;

次に、別のトランザクションから「id: 6」のレコードをINSERTすると、しばらく時間がかかり、Lock wait timeout exceeded

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO tbl(id, value) VALUES (6, 'ggg');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

その他のいろんなパターンを知りたい場合は、良く分かるMySQL Innodbのギャップロック が参考になります。

インサートインテンションロック

INSERT文によって設定されるギャップロック

ネクストキーロック

レコードロックとギャップロックを組み合わせ。
インデックスレコードに対するレコードロックと、そのインデックスレコードの前にあるギャップに対するギャップロックを組み合わせたものです。

試してみた方がわかりやすいと思います。

ネクストキーロックを試してみる

次のクエリを実行します。

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM tbl WHERE id < 4 FOR UPDATE;
+----+-------+
| id | value |
+----+-------+
|  1 | aaa   |
|  2 | bbb   |
|  3 | ccc   |
+----+-------+
3 rows in set (0.01 sec)

このときのトランザクションの状況は

3 lock struct(s), heap size 1136, 4 row lock(s)
MySQL thread id 53, OS thread handle 123145525313536, query id 1080 localhost root
TABLE LOCK table `learn_sql`.`tbl` trx id 5719 lock mode IX
RECORD LOCKS space id 77 page no 4 n bits 80 index PRIMARY of table `learn_sql`.`tbl` trx id 5719 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 8; hex 8000000000000001; asc         ;;
 1: len 6; hex 000000001636; asc      6;;
 2: len 7; hex 820000008d0110; asc        ;;
 3: len 3; hex 616161; asc aaa;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 8; hex 8000000000000002; asc         ;;
 1: len 6; hex 000000001636; asc      6;;
 2: len 7; hex 820000008d0121; asc       !;;
 3: len 3; hex 626262; asc bbb;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 8; hex 8000000000000003; asc         ;;
 1: len 6; hex 000000001653; asc      S;;
 2: len 7; hex 01000001270245; asc     ' E;;
 3: len 3; hex 636363; asc ccc;;

RECORD LOCKS space id 77 page no 4 n bits 80 index PRIMARY of table `learn_sql`.`tbl` trx id 5719 lock_mode X locks gap before rec
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 8; hex 8000000000000005; asc         ;;
 1: len 6; hex 000000001636; asc      6;;
 2: len 7; hex 820000008d0143; asc       C;;
 3: len 3; hex 646464; asc ddd;;

3行しか取得していないのに「4 row lock(s)」になっています。
最後の行をみると、「id: 5, value: 'ddd'」のレコードにギャップロックがかかっているのがわかります。

「id < 4」で、「id: 1~3」までレコードロックをかけて、その末尾のindex値を持つ行の後のギャップもロックを取るため「id: 4, 5」の範囲にギャップロックがかかるわけです。

この状態のまま、別のトランザクションで「id: 4」のレコードをINSERTしてみると、ロック待ちになります。

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO tbl(id, value) VALUES (4, 'xxx');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

参考

• エキスパートのためのMySQL[運用+管理]トラブルシューティングガイド
• innodbのロックモード