プライマリインデックスに対する等価検索、空振り
lock_mode X locks gap before rec
select * from t1 where id = 5 for update; ------------ TRANSACTIONS ------------ Trx id counter 1957 Purge done for trx's n:o < 1939 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 1956, ACTIVE 10 sec 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 2, OS thread handle 139829509609216, query id 318 localhost root starting show engine innodb status Trx read view will not see trx with id >= 1956, sees < 1956 TABLE LOCK table `test`.`t1` trx id 1956 lock mode IX RECORD LOCKS space id 24 page no 3 n bits 80 index PRIMARY of table `test`.`t1` trx id 1956 lock_mode X locks gap before rec Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000006; asc ;; 1: len 6; hex 000000000722; asc ";; 2: len 7; hex 3b00000130036d; asc ; 0 m;; 3: len 3; hex 626262; asc bbb;;
プライマリインデックスに対する範囲検索、空振り
lock_mode X
select * from t1 where id between 4 and 5 for update; ------------ TRANSACTIONS ------------ Trx id counter 1958 Purge done for trx's n:o < 1939 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 1957, ACTIVE 3 sec 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 2, OS thread handle 139829509609216, query id 322 localhost root starting show engine innodb status TABLE LOCK table `test`.`t1` trx id 1957 lock mode IX RECORD LOCKS space id 24 page no 3 n bits 80 index PRIMARY of table `test`.`t1` trx id 1957 lock_mode X Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000006; asc ;; 1: len 6; hex 000000000722; asc ";; 2: len 7; hex 3b00000130036d; asc ; 0 m;; 3: len 3; hex 626262; asc bbb;;
プライマリインデックスに対する等価検索
lock_mode X locks rec but not gap
------------ TRANSACTIONS ------------ Trx id counter 1959 Purge done for trx's n:o < 1939 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 1958, ACTIVE 3 sec 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 2, OS thread handle 139829509609216, query id 326 localhost root starting show engine innodb status TABLE LOCK table `test`.`t1` trx id 1958 lock mode IX RECORD LOCKS space id 24 page no 3 n bits 80 index PRIMARY of table `test`.`t1` trx id 1958 lock_mode X locks rec but not gap Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000006; asc ;; 1: len 6; hex 000000000722; asc ";; 2: len 7; hex 3b00000130036d; asc ; 0 m;; 3: len 3; hex 626262; asc bbb;;
