Special cases of innodb RC lock, innodbrc

Special cases of innodb RC lock, 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 InnoDBGap locking checks tfor foreign-key constraint checking and duplicate-keyChecking.Also, record locks for nonmatching rows arereleased after MySQL has evaluatedthe WHERE condition.

The items in the verified manual are incorrect. In fact, this is not the case in the test. The duplicate key check and foreign key check do not necessarily apply the gap lock.

Foreign key check

 

The value updated in the sub-table does not exist in the parent table. A gap lock is applied to the parent table.

1. MariaDB [mytest]> update t4 set id = 3 where id = 1; // insert is the same

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

Sub-Table update. The parent table has values. The parent table has a record lock.

1. MariaDB [mytest]> update t4 set id = 4 where id = 1; // insert is the same

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 modeS 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 applied

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 );

Query OK, 3 rows affected (0.01 sec)

Records: 3 Duplicates: 0 Warnings: 0

 

Insert and update

 

If there is a value, it is not a gap.

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_modeX 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 modeS 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 ;;