InnoDB の行レベルロックについて解説してみる

2016-11-30 (Last updated: 2016-12-04)

MySQL

自分の浅はかな理解だと、Deadlock が起こる理由が説明できないケースに遭遇したので、InnoDB の行レベルロックについて調べてまとめてみました。
「行レベルロックだと、同じ行を更新する場合にしか Deadlock が起こらないんでしょ」と思っているような人が対象です。
また、主に InnoDBのロックの範囲とネクストキーロックの話 - かみぽわーるを参考にさせていただいたので、そちらの内容がすんなり理解できる方には冗長な内容だと思います。
MySQL のバージョンは 5.6.33 です。

サンプルデータ

次の SQL で作成したデータを扱うことにします。

CREATE TABLE `orders` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `product_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_product_id` (`product_id`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO
  `orders` (`product_id`, `user_id`)
VALUES
  (1, 1),
  (1, 3),
  (1, 6),
  (1, 10),
  (1, 15),
  (1, 20),
  (1, 26),
  (1, 33),
  (1, 41),
  (1, 50),
  (2, 1),
  (2, 3),
  (2, 6),
  (2, 10),
  (2, 15),
  (2, 20),
  (2, 26),
  (2, 33),
  (2, 41),
  (2, 50)
;

SELECT すると以下のような結果になります。

mysql> SELECT * FROM `orders`;
+----+------------+---------+
| id | product_id | user_id |
+----+------------+---------+
|  1 |          1 |       1 |
|  2 |          1 |       3 |
|  3 |          1 |       6 |
|  4 |          1 |      10 |
|  5 |          1 |      15 |
|  6 |          1 |      20 |
|  7 |          1 |      26 |
|  8 |          1 |      33 |
|  9 |          1 |      41 |
| 10 |          1 |      50 |
| 11 |          1 |      60 |
| 12 |          2 |       1 |
| 13 |          2 |       3 |
| 14 |          2 |       6 |
| 15 |          2 |      10 |
| 16 |          2 |      15 |
| 17 |          2 |      20 |
| 18 |          2 |      26 |
| 19 |          2 |      33 |
| 20 |          2 |      41 |
| 21 |          2 |      50 |
| 22 |          2 |      60 |
+----+------------+---------+
22 rows in set (0.01 sec)

予備知識

ロックを管理するのは InnoDB なので、InnoDB で処理できない WHERE 条件（インデックスが使えない条件）はロックの範囲を決める際には無視されます。storage engine (e.g. InnoDB) や executor と聞いてよくわからない方は次のスライドを一読することをオススメします。

雑なMySQLパフォーマンスチューニング

ロックの種類

ロックの範囲を決定付けるロックの種類には主に次の 3 つがあります。

タイプ	概要
Record Locks	インデックスレコードに対するロック。InnoDB Lock Monitor では “locks rec but not gap” と表示される。
Gap Locks	インデックスレコード間（rec1 <= x < rec2）へのレコードの挿入を阻止するロック。REPEATABLE READ でファントムリードを防ぐ¹。InnoDB Lock Monitor では “locks gap before rec” と表示される。
Next-Key Locks	インデックスレコードと、その手前のギャップに対するロック。REPEATABLE READ でファントムリードを防ぐ。InnoDB Lock Monitor では “locks” と表示される。

idx_user_id に対するロックを例にすると、user_id = 6 の record lock は user_id = 6 でヒットするインデックスレコードをロックします。これらのレコードは他のトランザクションから更新や削除はできません。
user_id = 3, user_id = 6 のインデックスレコード間の gap lock は [3, 6) (3 <= user_id < 6) の範囲への挿入を阻止します。
user_id = 6 の next-key lock は user_id = 6 の record lock と、[3, 6) の gap lock です。

テーブルの各行に対応するロックを primary key の record lock と考えれば、ロックとは、インデックスに対するロックです。

ロックの実例

InnoDB Lock Monitor をオンにすることで、SHOW ENGINE INNODB STATUS の TRANSACTIONS セクションに前述のロックの情報が表示されます。

set GLOBAL innodb_status_output=ON;
set GLOBAL innodb_status_output_locks=ON;

この状態で次の SQL を実行してみます。

BEGIN;
SELECT * FROM `orders` WHERE user_id = 6 FOR UPDATE;

mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:

(snip)

------------
TRANSACTIONS
------------
Trx id counter 2162
Purge done for trx's n:o < 2160 undo n:o < 0 state: running but idle
History list length 130
Total number of lock structs in row lock hash table 3
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 2161, ACTIVE 5 sec
4 lock struct(s), heap size 1248, 5 row lock(s)
MySQL thread id 46, OS thread handle 0x7000008bb000, query id 452 localhost 127.0.0.1 root init
SHOW ENGINE INNODB STATUS
TABLE LOCK table `test`.`orders` trx id 2161 lock mode IX
RECORD LOCKS space id 22 page no 5 n bits 88 index `idx_user_id` of table `test`.`orders` trx id 2161 lock_mode X
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 00000006; asc     ;;
 1: len 4; hex 00000003; asc     ;;

Record lock, heap no 13 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 00000006; asc     ;;
 1: len 4; hex 0000000c; asc     ;;

RECORD LOCKS space id 22 page no 3 n bits 88 index `PRIMARY` of table `test`.`orders` trx id 2161 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 00000003; asc     ;;
 1: len 6; hex 000000000865; asc      e;;
 2: len 7; hex e6000002090128; asc       (;;
 3: len 4; hex 00000001; asc     ;;
 4: len 4; hex 00000006; asc     ;;

Record lock, heap no 13 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 0000000c; asc     ;;
 1: len 6; hex 000000000865; asc      e;;
 2: len 7; hex e6000002090194; asc        ;;
 3: len 4; hex 00000002; asc     ;;
 4: len 4; hex 00000006; asc     ;;

RECORD LOCKS space id 22 page no 5 n bits 88 index `idx_user_id` of table `test`.`orders` trx id 2161 lock_mode X locks gap before rec
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 0000000a; asc     ;;
 1: len 4; hex 00000004; asc     ;;

(snip)

RECORD LOCKS ... index `idx_user_id` ... lock_mode X は idx_user_id に対する next-key lock で、最初のフィールドが user_id (0x00000006 = 6)、次のフィールドが id です。
user_id = 6 に対する next-key lock なので、user_id = 6 のレコードがロックされ、user_id が [3, 6) のレコードの挿入もできなくなります。

RECORD LOCKS ... index `PRIMARY` ... lock_mode X locks rec but not gap は id に対する record lock で、それぞれのフィールドは id、トランザクション ID、ロールポインタ、product_id、user_id です。²
よって、primary key 0x00000003 = 3, 0x0000000c = 12 のレコードがロックされます。

RECORD LOCKS ... index `idx_user_id` ... lock_mode X locks gap before rec は idx_user_id に対する gap lock で、フィールドの意味は next-key lock と同じです。
user_id = 0x0000000a = 10 の前のギャップ (gap before rec) に対する gap lock なので、user_id が [6, 10) のレコードの挿入ができなくなります。

以上より、user_id [3, 10) の挿入ができなくなり、primary key 3, 12 (user_id 6) のレコードがロックされます。

Deadlock の実例

以上のことまで理解できれば次の SQL がほぼ同時に実行されると deadlock になることも理解できるはずです。

TX 1	TX 2
BEGIN;
	BEGIN;
DELETE FROM `orders` WHERE `product_id` = 1 AND `user_id` IN (6, 20) ;
	DELETE FROM `orders` WHERE `product_id` = 2 AND `user_id` IN (10, 20) ;
INSERT INTO `orders` (`product_id`, `user_id`) VALUES (1, 6), (1, 20) ;
	Deadlock found when trying to get lock

deadlock に関連するロックに言及すると次のとおりです。

TX 1	TX 2
user_id = 20 の record lock を取得
	user_id = 10 の next-key lock によって [6, 10] の範囲への挿入をできなくするが、user_id = 20 の record lock の取得に失敗し待機
user_id = 6 のレコードを挿入しようとするが、TX 2 によって [6, 10] への挿入ができない => deadlock

僕の “行レベルロック” の理解では、TX 1 は product_id = 1 のレコードしか処理していないし、TX 2 は product_id = 2 のレコードしか処理していないから、deadlock は起こるはずないと思っていましたが、InnoDB で処理できない WHERE 条件が無視されることと、next-key lock や gap lock によって特定の範囲に対する挿入ができなくなることがわかれば説明が付きますね。

上記のようなケースで deadlock を回避するには、削除対象の id を取得して、取得した id を指定して削除すれば良いです。

TX 1	TX 2
BEGIN;
	BEGIN;
DELETE FROM `orders` WHERE `id` IN (3, 6) ;
	DELETE FROM `orders` WHERE `id` IN (15, 17) ;
INSERT INTO `orders` (`product_id`, `user_id`) VALUES (1, 6), (1, 20) ;

参考

ロックの理解に関しては以下のエントリーがとても参考になりました。

MySQL のドキュメントを読んでも gap lock の境界値がわからなかったり、「next-key lock が使われる」とだけ書かれていて、”locks gap before rec” な gap lock に関する言及がなかったり（見落としてるだけかもですが）で、いまいち疑問が解消されませんでしたが、最後にはやっぱり手を動かして色々試して理解を深めることが大事ですね！

ユニークインデックスに対する等価比較で既存のレコードに対してロックする場合などは、ファントムリードが起こり得ないので、gap lock は発生しません ↩
cf. MySQL :: MySQL 5.6 Reference Manual :: 14.8.3 Physical Row Structure of InnoDB Tables ↩