Open9
mysql ギャップロック
syyギャップロックはindexのギャップに対するロック。共有ロックと似た挙動。
ギャップロックとギャップロックは競合しない。
select でのロック取得, update, deleteで空振ったときはギャップロックが発生する。
mysql> desc a;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| param | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> select * from a;
+----+-------+
| id | param |
+----+-------+
| 1 | 1 |
| 5 | 5 |
| 8 | 8 |
| 9 | 9 |
+----+-------+
4 rows in set (0.00 sec)
存在するレコードに対して等価比較でロックを掛けた場合、レコードロックがかかり、ギャップロックは発生しない。
A
mysql> begin;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from a where id = 1 for update;
+----+-------+
| id | param |
+----+-------+
| 1 | 1 |
+----+-------+
1 row in set (0.01 sec)
B. ギャップにinsertできる
mysql> begin;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into a values (2,2);
Query OK, 1 row affected (0.01 sec)
存在するレコードにwhere in でロックを掛けた場合、レコードロックがかかり、ギャップロックは発生しない
A
mysql> select * from a where id in (1,5) for update;
+----+-------+
| id | param |
+----+-------+
| 1 | 1 |
| 5 | 5 |
+----+-------+
2 rows in set (0.01 sec)
B
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into a values (2,2);
Query OK, 1 row affected (0.00 sec)
存在しないレコードにロックをかけると、ギャップロックがかかる。
idが1, 5とあるので、2 ~ 4のギャップがロックされる。2 ~ 4へのinsertはロック待ちになる
別のギャップへの排他ロックは取得できる。
A
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from a where id = 2 for update;
Empty set (0.00 sec)
B
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
# Aのギャップロックによって待たされる
mysql> insert into a values (2,2);
キャンセル
# Aのギャップロックによって待たされる
mysql> insert into a values (3,3);
キャンセル
# Aのギャップロックでロックしているギャップではないギャップへのinsertなのですぐにinsertできる
mysql> insert into a values (6,6);
# Aがギャップロックしてても共有ロックは取得できる。
mysql> select * from a where id = 2 lock in share mode;
Empty set (0.00 sec)
# deleteは空振ったときにギャップロックを取得する。ギャップロックとギャップロックは競合しない。
mysql> delete from a where id = 2;
Query OK, 0 rows affected (0.00 sec)
# updateは空振ったときにギャップロックを取得する。ギャップロックとギャップロックは競合しない。
mysql> update a set param = 2 where id = 2;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 0 Changed: 0 Warnings: 0
# select for updateは空振ったときにギャップロックを取得する。ギャップロックとギャップロックは競合しない。
mysql> select * from a where id = 2 for update;
Empty set (0.00 sec)
indexのないカラムでギャップにロックを掛けた場合、
ギャップロックと言うかindexがないのでテーブル自体がロックされる。
A
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from a where param = 2 for update;
Empty set (0.01 sec)
B
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
## ロック競合
mysql> select * from a where param = 10 for update;
mysql> select * from a where param = 1 for update;
## idを条件にしてもロック競合
mysql> select * from a where id = 1 for update;
## indexを指定したギャップのロックは取得できる
mysql> select * from a where id = 2 for update;
Empty set (0.00 sec)
別のindexのギャップロックも競合しない
↓indexを貼ったカラムを追加
mysql> desc b;
+-----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| idx_param | int(11) | YES | MUL | NULL | |
+-----------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
A
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from b where id = 2 for update;
Empty set (0.00 sec)
B
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from b where idx_param = 2 for update;
Empty set (0.00 sec)
ギャップロックではなく行ロックの話になるけど、別のindexで排他ロックをかけると競合する。
セカンダリーインデックス上にロックを掛けているのではなく、クラスタインデックスにロックをかけている感じなのかな?
A
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from b where id = 1 for update;
+----+-----------+
| id | idx_param |
+----+-----------+
| 1 | 1 |
+----+-----------+
1 row in set (0.00 sec)
B
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
## 競合
mysql> select * from b where idx_param = 1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
ネクストキーロック = 行ロック + ギャップロックのこと。
範囲検索などで存在するレコードとギャップにまたがって検索すると、ネクストキーロックが発生する。裏側では行ロックとギャップロックがそれぞれ発生しているだけ。
mysql> select * from a;
+----+-------+
| id | param |
+----+-------+
| 1 | 1 |
| 5 | 5 |
| 8 | 8 |
| 9 | 9 |
+----+-------+
4 rows in set (0.00 sec)
A
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from a where id < 4 for update;
+----+-------+
| id | param |
+----+-------+
| 1 | 1 |
+----+-------+
1 row in set (0.00 sec)
B
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
## id=1は行ロックがかかっているので競合する
mysql> select * from a where id = 1 for update;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
## id = 2 はギャップなのでギャップロックがかかり、ギャップロックは競合しないので取得できる。
mysql> select * from a where id = 2 for update;
Empty set (0.00 sec)
## id 2 ~ 4のギャップにはギャップロックがかかっているのでinsertができない
mysql> insert into a values (2,2);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
## id 2 ~ 4のギャップなのでid = 5もロックが競合する
mysql> update a set param = 2 where id = 5;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
## 別のギャップにはロックがかかっていないのでinsertできる
mysql> insert into a values (6,6);
Query OK, 1 row affected (0.00 sec)
範囲比較と等価比較のときでロックの範囲が異なる
(1と5のレコードがあり、2 ~ 4がギャップのとき)
等価比較のとき
id = 2 for updateをすると2~4にギャップロックがかかる。
その際、id = 5にはロックがかからない。
範囲比較のとき
id = 2 for updateをすると2~4にギャップロックが、5に行ロックがかかる。
id = 1へにはロックがかからない。
id = 5がロックされているので、ほかトランザクションではid = 5へのupdateやdeleteはロックされる
なお、範囲比較でも2<=column and column <= 2のように実質等価比較のときは等価比較の挙動になる。