in MySQL5.7 while 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 have evaluatedthe WHERE condition.
There is something wrong with the verification manual. In fact, the test is not the case, duplicate key check and foreign key check is not bound to add gap lock.
FOREIGN key Check
The parent table does not have the updated value in the child table, and the parent table has a gap lock
1, MariaDB [mytest]> update t4 set id=3 where Id=1;//insert with
2, MariaDB [mytest]> show engine InnoDB status\g
------------
Transactions
------------
......
4 Lockstruct (s), heap size, 2 row lock (s)
Mysqlthread ID 2, OS thread handle 0XA63A6B90, query ID 118 localhost root init
Showengine InnoDB Status
Trx Readview won't see Trx with ID >= 270882, sees < 270882
Tablelock table ' mytest '. ' T4 ' Trx ID 270881 Lock Mode IX
RECORDLOCKS Space ID, page No 3 n bits, 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: 3 n bits of the index ' PRIMARY ' of table ' mytest '. ' T3 ' TRX ID 270881 lock modeS locks g AP 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
Child table update, parent table has value, parent table plus record lock
1, MariaDB [mytest]> update t4 set id=4 where Id=1;//insert with
2, MariaDB [mytest]> show engine InnoDB status\g
...
------------
Transactions
------------
...
4 lock struct (s), heap size, 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, page No 3 n bits, 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 3 n bits of the 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
============================
Primary key check: No gap lock is added
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 (3,3), (5,5);
Query ok,3 rows Affected (0.01 sec)
Records:3 duplicates:0 warnings:0
Insert, update
Has a value, it is not a gap.
Mariadb[mytest]> Update T5 set id=1 where id=5;
------------
Transactions
------------
3 Lockstruct (s), heap size, 2 row lock (s)
Mysqlthread ID 2, OS thread handle 0XA63A6B90, query ID 168 localhost root init
Showengine InnoDB Status
Trx Readview won't see Trx with ID >= 270907, sees < 270907
Tablelock table ' mytest '. ' T5 ' TRX ID 270906 Lock Mode IX
RECORDLOCKS Space ID Page No 3 n bits 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, page No 3 n bits, 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;;
Lock special case under InnoDB RC level