kenken0807_DBメモ

つば九郎が好きなDBAです。Oracle Standard Editionでの運用やツールとかとかの備忘録。特に記載がない場合はoracle11gR2です。時々MySQL

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のロックの範囲とネクストキーロックの話 - かみぽわーる