Help us understand the problem. What is going on with this article?

MySQL - ネクストキーロックってどこまでロックされんの?

More than 3 years have passed since last update.

個人的に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までがネクストキーロックされるイメージ)

スクリーンショット 2013-09-19 12.34.47.png

以下のクエリでは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
#待機状態

何回か実験して得られた傾向なのでこれが正解とは言えないけど、参考までに。自分でも色々試してみるといいと思います。

mizzwithliam
サービス設計とUIデザインを主にやっています。フロントエンド中心にコード書くこともあります。
http://mizuhiro.me
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした