Lock special case under InnoDB RC level

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