非ユニークインデックスの場合は、等価条件であっても範囲条件に近いロックを取るという話。空振ってなくても思ったよりロック広い？？ってなるケースとかはこれの可能性がありそう。ユニーク制約の付いていないカラムには同値を入れることができるため、現在存在しているnot_uniquのレコードだけロックしてもレコードの挿入を防ぐことはできない。
mysql> desc t1;
+
| Field | Type | Null | Key | Default | Extra |
+
| ID | int(11) | NO | PRI | NULL | |
| name | varchar(30) | YES | MUL | NULL | |
+
mysql> show index from t1\G
*************************** 1. row ***************************
Table: t1
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: ID
Collation: A
Cardinality: 3
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: t1
Non_unique: 1
Key_name: id_index
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 3
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
mysql> select * from t1;
+
| ID | name |
+
| 3 | aaa |
| 6 | bbb |
| 9 | ddd |
| 21 | ggg |
+
例えばこんなテーブルがあるとしてnameは非ユニーク。ここでdddで条件検索で排他ロックをとってみる
mysql> begin;
mysql> select * from t1 where name = 'ddd' for update;
この時のinnodbの値はこんな感じ。PRIMARYはレコードロックをとっているがセカンダリインデックスの方はlock_mode X locks gap before recとなっておりGAPロック持ちとなっている
4 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 2, OS thread handle 139829509609216, query id 396 localhost root starting
show engine innodb status
TABLE LOCK table `test`.`t1` trx id 1986 lock mode IX
RECORD LOCKS space id 24 page no 4 n bits 72 index id_index of table `test`.`t1` trx id 1986 lock_mode X
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 3; hex 646464; asc ddd;;
1: len 4; hex 80000009; asc ;;
RECORD LOCKS space id 24 page no 3 n bits 80 index PRIMARY of table `test`.`t1` trx id 1986 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000009; asc ;;
1: len 6; hex 000000000707; asc ;;
2: len 7; hex a70000011b0128; asc (;;
3: len 3; hex 646464; asc ddd;;
RECORD LOCKS space id 24 page no 4 n bits 72 index id_index of table `test`.`t1` trx id 1986 lock_mode X locks gap before rec
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 3; hex 676767; asc ggg;;
1: len 4; hex 80000015; asc ;;
別のトランザクションを起動してinsertを試してみる。bbb ~ gggまでのGAPに対してロックが取られているのがわかる
mysql> insert into t1 values(1, 'fff');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t1 values(8, 'aaab');
Query OK, 1 row affected (0.01 sec)
supremumとのロック
存在するレコードの末尾を排他ロックするとsupremumとのGAPロックを獲得するので予想外の範囲のロックを取る。
mysql> select * from t1 where name = 'ggg' for update;
+----+------+
| ID | name |
+----+------+
| 21 | ggg |
+----+------+
------------
TRANSACTIONS
------------
Trx id counter 2016
Purge done for trx's n:o < 2015 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421304856972944, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421304856972024, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 2015, ACTIVE 183 sec
3 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 2, OS thread handle 139829509609216, query id 445 localhost root starting
show engine innodb status
TABLE LOCK table `test`.`t1` trx id 2015 lock mode IX
RECORD LOCKS space id 24 page no 4 n bits 80 index id_index of table `test`.`t1` trx id 2015 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 3; hex 676767; asc ggg;;
1: len 4; hex 80000015; asc ;;
RECORD LOCKS space id 24 page no 3 n bits 80 index PRIMARY of table `test`.`t1` trx id 2015 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000015; asc ;;
1: len 6; hex 000000000707; asc ;;
2: len 7; hex a70000011b0134; asc 4;;
3: len 3; hex 676767; asc ggg;;
こうなってしまうとggg以降へのinsertができなくなってしまう。
mysql> insert into t1 values(11, 'gggg');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t1 values(8, 'hhhh');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
