innodb RC層級下加鎖特殊情況，innodbrc

innodb RC層級下加鎖特殊情況，innodbrc

In MySQL5.7, when READ COMMITTED isolation level is used, or the deprecated innodb_locks_unsafe_for_binlogsystem variable is enabled, there is no InnoDB gap locking exceptfor foreign-key constraint checking and duplicate-key checking.Also, record locks for nonmatching rows arereleased after MySQL has evaluatedthe WHERE condition.

經驗證手冊裡的東西有誤。其實，測試情況並不是這樣，重複鍵檢查和外鍵檢查並不是一定加gap鎖。

外鍵檢查

 

子表中更新的值父表沒有，對父表加間隙鎖

1、  MariaDB [mytest]> update t4 set id=3 where id=1;//insert同

2、  MariaDB [mytest]> show engine innodb status\G

------------

TRANSACTIONS

------------

……

4 lockstruct(s), heap size 320, 2 row lock(s)

MySQLthread id 2, OS thread handle 0xa63a6b90, query id 118 localhost root init

showengine innodb status

Trx readview will not see trx with id >= 270882, sees < 270882

TABLELOCK table `mytest`.`t4` trx id 270881 lock mode IX

RECORDLOCKS space id 75 page no 3 n bits 72 index `PRIMARY` of table `mytest`.`t4`trx id 270881 lock_mode X locks rec but not gap

Recordlock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

 0: len 4; hex 80000001; asc     ;;

 1: len 6; hex 00000004221c; asc     " ;;

 2: len 7; hex 9a000001530110; asc     S ;;

 3: len 4; hex 80000001; asc     ;;

 

TABLE LOCKtable `mytest`.`t3` trx id 270881 lock mode IS

RECORDLOCKS space id 74 page no 3 n bits 72 index `PRIMARY` of table `mytest`.`t3`trx id 270881 lock modeS locks gap before rec

Recordlock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

 0: len 4; hex 80000004; asc     ;;

 1: len 6; hex 000000042216; asc     " ;;

 2: len 7; hex 960000014f0128; asc     O (;;

 3: len 4; hex 80000004; asc     ;;

 

----------------------------

END OF INNODB MONITOR OUTPUT

子表更新，父表有值，父表加記錄鎖

1、  MariaDB [mytest]> update t4 set id=4 where id=1;//insert同

2、  MariaDB [mytest]> show engine innodb status\G

…

------------

TRANSACTIONS

------------

…

4 lock struct(s),heap size 320, 2 row lock(s), undo log entries 2

MySQLthread id 2, OS thread handle 0xa63a6b90, query id 145 localhost root init

showengine innodb status

TABLELOCK table `mytest`.`t4` trx id 270887 lock mode IX

RECORDLOCKS space id 75 page no 3 n bits 72 index `PRIMARY` of table `mytest`.`t4`trx id 270887 lock_mode X locks rec but not gap

Recordlock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 32

 0: len 4; hex 80000001; asc     ;;

 1: len 6; hex 000000042227; asc     "';;

 2: len 7; hex 1f000001fb0bab; asc        ;;

 3: len 4; hex 80000001; asc     ;;

 

TABLELOCK table `mytest`.`t3` trx id 270887 lock mode IS

RECORDLOCKS space id 74 page no 3 n bits 72 index `PRIMARY` of table `mytest`.`t3`trx id 270887 lock mode S locks rec but not gap

Recordlock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

 0: len 4; hex 80000004; asc     ;;

 1: len 6; hex 000000042216; asc     " ;;

 2: len 7; hex 960000014f0128; asc     O (;;

 3: len 4; hex 80000004; asc     ;;

 

----------------------------

END OFINNODB MONITOR OUTPUT

============================

主鍵檢查：不會加間隙鎖

MariaDB[mytest]> create table t5 (id int primary key,id1 int);

Query OK,0 rows affected (0.11 sec)

 

MariaDB[mytest]> insert into t5 values(1,1),(3,3),(5,5);

Query OK,3 rows affected (0.01 sec)

Records:3  Duplicates: 0  Warnings: 0

 

Insert、update

 

有值，則不是間隙。

MariaDB[mytest]> update t5 set id=1 where id=5;

 

------------

TRANSACTIONS

------------

3 lockstruct(s), heap size 320, 2 row lock(s)

MySQLthread id 2, OS thread handle 0xa63a6b90, query id 168 localhost root init

showengine innodb status

Trx readview will not see trx with id >= 270907, sees < 270907

TABLELOCK table `mytest`.`t5` trx id 270906 lock mode IX

RECORDLOCKS space id 76 page no 3 n bits 72 index `PRIMARY` of table `mytest`.`t5`trx id 270906 lock_mode X locks rec but not gap

Recordlock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

 0: len 4; hex 80000005; asc     ;;

 1: len 6; hex 000000042234; asc     "4;;

 2: len 7; hex a9000001620128; asc     b (;;

 3: len 4; hex 80000005; asc     ;;

 

RECORDLOCKS space id 76 page no 3 n bits 72 index `PRIMARY` of table `mytest`.`t5`trx id 270906 lock mode S locks rec but not gap

Recordlock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

 0: len 4; hex 80000001; asc     ;;

 1: len 6; hex 000000042234; asc     "4;;

 2: len 7; hex a9000001620110; asc     b ;;

 3: len 4; hex 80000001; asc     ;;