MySQLのInnoDBセカンダリインデックスのロック
MySQLのセカンダリインデックスのロックで嵌ったのでメモ。
isolationがREPEATABLE-READ
のときギャップロックやらネクストキーロックはセカンダリーインデックスの場合どうなるかよくわかってなかった。
例えば、こんなテーブル作ってcol2
にインデックスを作成する。で、インデックスのロックを見る。
CREATE TABLE `t1` ( `col_pk` int(11) NOT NULL AUTO_INCREMENT, `col2` int(11) DEFAULT NULL, `col3` varchar(10) DEFAULT NULL, PRIMARY KEY (`col_pk`), KEY `col2` (`col2`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
データはこんな。PKが3は空いている状態。
insert into t1(col_pk,col2,col3) values (1,10,'AAA'); insert into t1(col_pk,col2,col3) values (2,20,'AAA'); insert into t1(col_pk,col2,col3) values (4,40,'AAA'); insert into t1(col_pk,col2,col3) values (5,50,'AAA'); insert into t1(col_pk,col2,col3) values (6,60,'AAA'); +--------+------+------+ | col_pk | col2 | col3 | +--------+------+------+ | 1 | 10 | AAA | | 2 | 20 | AAA | | 4 | 40 | AAA | | 5 | 50 | AAA | | 6 | 60 | AAA | +--------+------+------+
これで、
UPDATE t1 SET col3='ZZZ' WHERE col2=50;
した場合どの範囲でロックが取得されるか。
InnoDB Lock Monitorで確認する。
---TRANSACTION 14903, ACTIVE 3 sec 4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1 MySQL thread id 71, OS thread handle 0x7fcab3430700, query id 957 localhost root cleaning up TABLE LOCK table `oo`.`t1` trx id 14903 lock mode IX ★1 RECORD LOCKS space id 68 page no 4 n bits 72 index `col2` of table `oo`.`t1` trx id 14903 lock_mode X Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000032; asc 2;; 1: len 4; hex 80000005; asc ;; ★2 RECORD LOCKS space id 68 page no 3 n bits 72 index `PRIMARY` of table `oo`.`t1` trx id 14903 lock_mode X locks rec but not gap Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000005; asc ;; 1: len 6; hex 000000003a37; asc :7;; 2: len 7; hex 55000001cc0312; asc U ;; 3: len 4; hex 80000032; asc 2;; 4: len 3; hex 5a5a5a; asc ZZZ;; ★3 RECORD LOCKS space id 68 page no 4 n bits 72 index `col2` of table `oo`.`t1` trx id 14903 lock_mode X locks gap before rec Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 8000003c; asc <;; 1: len 4; hex 80000006; asc ;;
3つの種類のRECORD LOCKSが確認できる。まずその内容の理解。
まず★1
RECORD LOCKS space id 68 page no 4 n bits 72 index `col2` of table `oo`.`t1` trx id 14903 lock_mode X Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000032; asc 2;; 1: len 4; hex 80000005; asc ;;
index `col2`
がcol2のインデックス
lock_mode X
がネクストキーロック
0: len 4; hex 80000032; asc 2;;
がcol2の値の16進数(16進数で32は10進数で50)
よって、col2インデックスの値50に対してネクストキーロック取得。
続いて★2
RECORD LOCKS space id 68 page no 3 n bits 72 index `PRIMARY` of table `oo`.`t1` trx id 14903 lock_mode X locks rec but not gap Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000005; asc ;; 1: len 6; hex 000000003a37; asc :7;; 2: len 7; hex 55000001cc0312; asc U ;; 3: len 4; hex 80000032; asc 2;; 4: len 3; hex 5a5a5a; asc ZZZ;;
index `PRIMARY`
がプライマリーキー
lock_mode X locks rec but not gap
がレコードロック
0: len 4; hex 80000005; asc ;;
がプライマリーキーの値の16進数(16進数で5は10進数で5)
よって、プライマリーキーの値5に対してレコードロック取得。
最後★3
RECORD LOCKS space id 68 page no 4 n bits 72 index `col2` of table `oo`.`t1` trx id 14903 lock_mode X locks gap before rec Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 8000003c; asc <;; 1: len 4; hex 80000006; asc ;;
index `col2`
がcol2のインデックス
lock_mode X locks gap before rec
がギャップロック
0: len 4; hex 8000003c; asc <;;
がcol2の値の16進数(16進数で3cは10進数で60)
よって、col2インデックスの値60までギャップロックを取得。
これをまとめると、
col2 40 ------------------------- 50 --------------------------- 60 | | | 値50のネクストキーロック 値60ギャップロック primary key 5にレコードロック
col2への挿入は問答無用で41~59まではロックされるのはわかっていたが、40と60でも挿入できないパターンがあった。
別トランザクションを起動して歯抜けだったPKの3と最大値の7でそれぞれ実行できるか待機するか確認。
SQL | col2の値 | 結果 |
---|---|---|
insert into t1(col_pk,col2,col3) values (7,39,'AAA'); | 39 | OK |
insert into t1(col_pk,col2,col3) values (7,40,'AAA'); | 40 | 待機 |
insert into t1(col_pk,col2,col3) values (7,41,'AAA'); | 41 | 待機 |
insert into t1(col_pk,col2,col3) values (7,59,'AAA'); | 59 | 待機 |
insert into t1(col_pk,col2,col3) values (7,60,'AAA'); | 60 | OK |
insert into t1(col_pk,col2,col3) values (3,40,'AAA'); | 40 | OK |
insert into t1(col_pk,col2,col3) values (3,41,'AAA'); | 41 | 待機 |
insert into t1(col_pk,col2,col3) values (3,59,'AAA'); | 59 | 待機 |
insert into t1(col_pk,col2,col3) values (3,60,'AAA'); | 60 | 待機 |
insert into t1(col_pk,col2,col3) values (3,61,'AAA'); | 61 | OK |
PKの3に対してはcol2の41~60が待機
PKの7に対してはcol2の40~59が待機
という結果に。41~59だけだと思ってたのでここで嵌った。
これは、セカンダリーインデックスはリーフノードにプライマリーキーを持つのでロックの範囲がそのプライマリーキーも意識しないとだめなようだった。
値50のネクストキーロック 値60ギャップロック col2 40 ------------------------- 50 --------------------------- 60 | | | col_pk 4 ------------------------- 5 ----------------------------- 6 ↑ ↑ (7,40,'AAA')待機 (3,60,'AAA')待機 ↑ ↑ (3,40,'AAA')OK (7,60,'AAA')OK
こうなっているわけだから、
(7,40,'AAA')はcol_pk:4より大きいため待機するが(3,40,'AAA')はcol_pk:4より小さいので挿入可能。
(3,60,'AAA')はcol_pk:6より小さいため待機するが(7,60,'AAA')はcol_pk:6より大きいので挿入可能。
参考
ギャップロックやらネクストキーロックはこちらの記事で大変理解できました。
InnoDBのロックの範囲とネクストキーロックの話 - かみぽわーる