innodb RC级别下加锁特殊情况

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 mode S 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     ;;