個人的にMySQL一番の鬼門のネクストキーロック。未だにまともな正解はわからないけれど、法則性らしきものが理解できてきたのでまとめてみる。
そもそもネクストキーロックとは
InnoDBの行ロックはネクストキーロックを採用している。検索時はネクストキーロックを用いてインデックス走査を行うので、ギャップロックが起こる場合は常に先のギャップもロックされており、これによってファントムリードを防ぐ。一意のインデックスを持つ固有値検索の場合はギャップロックする必要がないが、値域検索の場合はギャップロックをする。
固有値検索はギャップロックしないはずだが、存在しない行を読み取ろうとした場合は排他・共有ロックではなく、ギャップロックがかかる。同時にネクストキーロックもかかるのでproduct_id = 19にはINSERTできない。
mysql> select * from products where product_id = 18 for update; #Tx1
Empty set (0.00 sec)
mysql> insert into products values (19, 'line', 200000); #Tx2
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
値域検索の条件によっては無限大にロックをかけることもある
mysql> select * from products where product_id > 20 for update; #Tx1
+------------+--------------+--------+
| product_id | product_name | price |
+------------+--------------+--------+
| 21 | line | 200000 |
+------------+--------------+--------+
1 row in set (0.00 sec)
mysql> insert into products values (37, 'line', 200000); #Tx2
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
ネクストキーロックのロック範囲
ネクストキーロックの次の行とはテーブルの下の行ではなく、インデックス走査時の向きから見て先の行と捉えるべき。よって、product_id < 15などのような検索条件の場合、インデックス上を一度15まで走査し、その後15以下の方向へ走査していくので、16や10などにロックがかかることが予想される。 参考 また、ネクストキーロックがかかる範囲については、インデックス走査時に条件値の次に存在している値までがギャップロックされると推測できる。(product_id <= 15の条件で、15の次は20だった場合、20までがネクストキーロックされるイメージ)
以下のクエリではproduct_id = 12にネクストキーロックがかかる。その際にインデックス上で周辺行をまとめてギャップロックし、それとTx2のINSERTが競合している。
mysql> select * from products where product_id <= 12 for update; #Tx1
+------------+--------------+-------+
| product_id | product_name | price |
+------------+--------------+-------+
| 1 | macbook pro | 150 |
| 2 | iphone 5 | 150 |
| 3 | iPad | 150 |
| 4 | NULL | 1000 |
| 5 | sample | 150 |
| 6 | sasa | 150 |
| 7 | sasa | 150 |
| 8 | thinkpad | 150 |
| 9 | lenovo | 150 |
| 10 | eeee | 150 |
| 11 | fff | 100 |
| 12 | fff | 800 |
+------------+--------------+-------+
12 rows in set (0.00 sec)
mysql> insert into products values (13, 'fff', 800); #Tx2
#待機状態
何回か実験して得られた傾向なのでこれが正解とは言えないけど、参考までに。自分でも色々試してみるといいと思います。