【MySQL】非ユニークなカラムのロックは広いかもしれない

非ユニークインデックスの場合は、等価条件であっても範囲条件に近いロックを取るという話。空振ってなくても思ったよりロック広い？？ってなるケースとかはこれの可能性がありそう。ユニーク制約の付いていないカラムには同値を入れることができるため、現在存在している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ロック持ちとなっている

---TRANSACTION 1986, ACTIVE 17 sec
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