MySqlのネクストキーロックについて

MySQLのネクストキーロックについてなかなか覚えられないので、手元で試しながら頭の中を整理してみる。

手元のMySQLのバージョンは5.7。

テーブルに登録されているデータの状態

code: ddl.sql

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

;

code: registered

mysql> INSERT INTO test VALUES (1, 'a'), (2, 'b'), (3, 'c'), (10, 'd'), (15, 'e');

Query OK, 5 rows affected (0.01 sec)

Records: 5 Duplicates: 0 Warnings: 0

mysql> select * from test;

+----+-----+

| id | val |

+----+-----+

| 1 | a |

| 2 | b |

| 3 | c |

| 10 | d |

| 15 | e |

+----+-----+

5 rows in set (0.00 sec)

各クライアントの状態

トランザクション分離レベルはREPEATABLE-READ

MySQLはREPEATABLE-READとかの分離レベルでロッキングリードした場合は、ネクストキーロックの仕組みによってファントムリードを防げるようになっている

特定のインデックス間でネクストキーロックがかかるので、そのロックの範囲の要素に対してのinsert / deleteはロックがかかり待ち状態になる。よって先行してネクストキーロックをかけたトランザクション側からはトランザクションが終了するまで

ちなみにREPEATABLE-READでロッキングリードしない場合も、MySQLのMVCCによってファントムリードは防がれてる

Pattern1: あるインデックスの直前までロック

code: select(client1).sql

begin;

-- 今回はselect-for updateでやってるけど、delete, updateでも同じようにロックかかる

select * from test where id >= 3 and id < 10 for update;

+----+-----+

| id | val |

+----+-----+

| 3 | c |

+----+-----+

1 row in set (0.00 sec)

code: insert(client2).sql

begin;

insert test (id, val) values (3, 'xxx');

-- 返ってこない

SHOW ENGINE INNODB STATUS \G

...

LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)

MySQL thread id 59, OS thread handle 140442620094208, query id 943 localhost test update

insert test (id, val) values (3, 'xxx')

------- TRX HAS BEEN WAITING 44 SEC FOR THIS LOCK TO BE GRANTED:

insert test (id, val) values (4, 'xxx');

-- 返ってこない

SHOW ENGINE INNODB STATUS \G

...

MySQL thread id 59, OS thread handle 140442620094208, query id 748 localhost test update

insert test (id, val) values (4, 'xxx')

------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:

lock_mode X locks gap before rec insert intention waiting

insert test (id, val) values (10, 'xxx');

-- 返ってこない

SHOW ENGINE INNODB STATUS \G

...

MySQL thread id 59, OS thread handle 140442620094208, query id 750 localhost test update

insert test (id, val) values (10, 'xxx')

------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:

mysql> insert test (id, val) values (11, 'xxx');

Query OK, 1 row affected (0.00 sec)

この場合、直感通りid: 3〜10の間でロックが掛かる。

table: ロック範囲のイメージ

登録されてるレコード 1 2 3 10 15

ロック範囲 □ → ← □

→の範囲はギャップロック、□はレコードロック

ネクストキーロックが以下の組み合わせで起こっていそう

ギャップロック

index(indexが張られていて登録されているカラム)の合間で張られるので、4~9の範囲で張られる

レコードロック

条件範囲内のレコードロック (3)

ギャップロックの次のindexのレコード (10)

Pattern2: あるインデックスまでロック

code: select(client1).sql

begin;

-- 今回はselect-for updateでやってるけど、delete, updateでも同じようにロックかかる

select * from test where id > 3 and id <= 10 for update;

+----+-----+

| id | val |

+----+-----+

| 10 | d |

+----+-----+

2 rows in set (0.00 sec)

code: insert(client2).sql

begin;

insert test (id, val) values (3, 'xxx');

ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'

insert test (id, val) values (4, 'xxx');

-- 返ってこない

SHOW ENGINE INNODB STATUS \G

...

LOCK WAIT 3 lock struct(s), heap size 1136, 3 row lock(s)

MySQL thread id 59, OS thread handle 140442620094208, query id 956 localhost test update

insert test (id, val) values (4, 'xxx')

------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:

insert test (id, val) values (10, 'xxx');

-- 返ってこない

SHOW ENGINE INNODB STATUS \G

...

LOCK WAIT 3 lock struct(s), heap size 1136, 4 row lock(s)

MySQL thread id 59, OS thread handle 140442620094208, query id 960 localhost test update

insert test (id, val) values (10, 'xxx')

------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:

insert test (id, val) values (11, 'xxx');

-- 返ってこない

SHOW ENGINE INNODB STATUS \G

...

LOCK WAIT 3 lock struct(s), heap size 1136, 5 row lock(s)

MySQL thread id 59, OS thread handle 140442620094208, query id 964 localhost test update

insert test (id, val) values (11, 'xxx')

------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:

insert test (id, val) values (15, 'xxx');

-- 返ってこない

SHOW ENGINE INNODB STATUS \G

...

LOCK WAIT 3 lock struct(s), heap size 1136, 7 row lock(s)

MySQL thread id 59, OS thread handle 140442620094208, query id 970 localhost test update

insert test (id, val) values (15, 'xxx')

------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:

insert test (id, val) values (16, 'xxx');

Query OK, 1 row affected (0.00 sec)

この場合、直感的にはid: 4〜10の間でロックが掛かりそうだが、かなり広い範囲でロックがかかる (範囲の次のインデックスまでロックかかるのでこのような広さになるのだなぁ)。

table: ロック範囲のイメージ

登録されてるレコード 1 2 3 10 15

ロック範囲 → ← □ → ← □

→の範囲はギャップロック、□はレコードロック

ネクストキーロックが以下の組み合わせで起こっていそう

ギャップロック

index(indexが張られていて登録されているカラム)の合間で張られるので、4~9, 11~14の範囲で張られる

レコードロック

範囲内のindexレコード (10)

最後のindexの次のindex (15)

Pattern3: あるインデックスより大きい値をロック

こちらは後日mysql8系で操作してる

code: select client1.sql

begin;

select * from test where id > 15 for update;

この状態でパフォーマンススキーマのdata_locksでロック状態を確認する。

code: select performance_schama.data_locks.sql

mysql> select engine_transaction_id as tx_id, index_name, lock_mode, lock_data from performance_schema.data_locks where lock_type = 'RECORD' and object_name = 'test';

+--------+------------+-----------+------------------------+

| tx_id | index_name | lock_mode | lock_data |

+--------+------------+-----------+------------------------+

| 372426 | PRIMARY | X | supremum pseudo-record |

+--------+------------+-----------+------------------------+

1 row in set (0.00 sec)

ギャップロックと挿入インテンションロックの挙動について

ギャップロックの目的はドキュメントにもあるように、ギャップ範囲にinsertされないようにすること (ファントムリードを防ぐこと) である

REPEATABLE READの分離レベルで使われる

そのためギャップロックがかぶっても特に競合は起きない

反面、ギャップロックの範囲にinsertが行われると、挿入インテンションロックと競合するようになっている

挿入インテンションロックはinsert時に事前取得されるギャップロックの一種とのことで、これがギャップロックと範囲がかぶると競合して、insertが待たされる (これによってREPEATABLE-READでロッキングリードした場合にファントムリードが防がれている)。

SNOW INNODB ENGINE STATUSで見たときのネクストキーロックの表示のされ方

ネックストキーロックがかかっていた際は、ロックがかかったレコードの情報 (Record lockで説明される)のキーの値が supremum と表示されることがある (一番大きいIDより後ろに対してネクストキーロックがかかった場合とおもう)

superemumは上限値を表している特殊な行とのこと

ネクストキーロックが上限でないIDにかかっていた場合は、通常通りRecord lockにレコードの値が返される

こんな感じで

SELECT * FROM t2 WHERE k >= 1 AND k <= 10 FOR UPDATE;

code: innodb engine status

Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

0: len 4; hex 80000001; asc ;;

1: len 6; hex 00000005846b; asc k;;

2: len 7; hex 81000000990110; asc ;;

3: len 4; hex 80000000; asc ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

0: len 4; hex 8000000a; asc ;;

1: len 6; hex 00000005846b; asc k;;

2: len 7; hex 8100000099011d; asc ;;

3: len 4; hex 80000000; asc ;;

key a(10)の部分はネクストキーロックかかっているが、Record lockにちゃんとレコードの情報が出ている

また共有ロックがロックとっているばあい、他のロックと衝突していない場合はengine innodb statusにも表示されないので注意が必要 (ネクストキーロックに限らずそうなる)

参考

トランザクションがブロックする、ブロックしないというのは、つまるところロックの範囲が被ったか被ってないかなので

んーなるほどな……

各MySQLの操作でどのようなロックがかかるんだっけ？のはなし