| Bug #109596 | Strange behavior about innodb gap lock. | ||
|---|---|---|---|
| Submitted: | 12 Jan 2023 2:25 | Modified: | 17 Jan 2023 1:39 |
| Reporter: | wei yao | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 8.0.25 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[12 Jan 2023 2:25]
wei yao
[12 Jan 2023 13:17]
MySQL Verification Team
Hi Mr. yao, Thank you for your bug report. However, it is not a bug. With such a small table, all rows will be locked with that SELECT ..... Try the same with a table that has 1000 rows or similar ..... Not a bug.
[13 Jan 2023 1:40]
wei yao
Hi,Dear mysql server team. In my select for update sql, the hint force index(i_gg) is added. So the exection plan is not full table scan. And refer to the data_locks, mysql> select object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks; +-------------+-----------------+-----------+---------------+--------------------+ | object_name | index_name | lock_type | lock_mode | lock_data | +-------------+-----------------+-----------+---------------+--------------------+ | gg | NULL | TABLE | IX | NULL | | gg | i_gg | RECORD | X | 30, 0x00000000040D | | gg | GEN_CLUST_INDEX | RECORD | X,REC_NOT_GAP | 0x00000000040D | | gg | i_gg | RECORD | X,GAP | 50, 0x00000000040F | +-------------+-----------------+-----------+---------------+--------------------+ 4 rows in set (0.00 sec) not all the record, gap is locked. mysql> select * from gg; +-----+-------+ | tid | tname | +-----+-------+ | 10 | 10 | | 20 | 20 | | 30 | 30 | | 50 | 50 | +-----+-------+ 4 rows in set (0.00 sec) and mysql> insert into gg values(19,null); Query OK, 1 row affected (0.01 sec) mysql> insert into gg values(20,null); --insert tid=20 will be blocked mysql> insert into gg values(50,null); Query OK, 1 row affected (0.00 sec) I can not understand what happened.
[13 Jan 2023 9:12]
wei yao
Also test with 1000 records in same table, same behavior as before.
[13 Jan 2023 13:29]
MySQL Verification Team
Hi, Do note that 8.0.25 is an old release. Also, do note that full diagnostic info can be found only in the full InnoDB status info, which is not provided here. Not a bug.
[13 Jan 2023 14:13]
huahua xu
insert into gg values(10,'10'),(20,'20'),(30,'30'),(40,'40'),(50,'50'); -- after the sql was executed, the secondary index records in leaf page: +-----------------+---------+----------+----------------+----------------+----------------+----------------+----------------+ | Data(leaf page) | infimum | supremum | (10, row_id_1) | (20, row_id_2) | (30, row_id_3) | (40, row_id_4) | (50, row_id_5) | +-----------------+---------+----------+----------------+----------------+----------------+----------------+----------------+ | heap_no | 0 | 1 | 2 | 3 | 4 | 5 | 6 | +-----------------+---------+----------+----------------+----------------+----------------+----------------+----------------+ For the system column `DATA_ROW_ID`, row_id_6 > row_id_5 > row_id_4 > row_id_3 > row_id_2 > row_id_1 set autocommit=0; select * from gg force index(i_gg) where tid = 30 for update; -- after the sql was executed, the locks which current transaction hold: mysql> select ENGINE_TRANSACTION_ID,ENGINE_LOCK_ID,INDEX_NAME,lock_type,lock_mode,lock_data,LOCK_STATUS from data_locks; +-----------------------+------------------------------------+-----------------+-----------+---------------+--------------------+-------------+ | ENGINE_TRANSACTION_ID | ENGINE_LOCK_ID | INDEX_NAME | lock_type | lock_mode | lock_data | LOCK_STATUS | +-----------------------+------------------------------------+-----------------+-----------+---------------+--------------------+-------------+ | 13878 | 2227003723824:1147:2226967457320 | NULL | TABLE | IX | NULL | GRANTED | | 13878 | 2227003723824:81:5:4:2226967454408 | i_gg | RECORD | X | 30, 0x000000039A02 | GRANTED | | 13878 | 2227003723824:81:4:4:2226967454760 | GEN_CLUST_INDEX | RECORD | X,REC_NOT_GAP | 0x000000039A02 | GRANTED | | 13878 | 2227003723824:81:5:5:2226967455112 | i_gg | RECORD | X,GAP | 40, 0x000000039A03 | GRANTED | +-----------------------+------------------------------------+-----------------+-----------+---------------+--------------------+-------------+ insert into gg values(19,null); -- the secondary index record is (19, row_id_6), and its next record is (20, row_id_2). the transaction need to request a `X|GAP|INSERT_INTENTION` lock on the next record (20, row_id_2) which there is not any lock on. so the sql will be executed success. insert into gg values(20,null); -- the secondary index record is (20, row_id_6), and its next record is (30, row_id_3). the transaction need to request a `X|GAP|INSERT_INTENTION` lock on the next record (30, row_id_3) which there is a X lock on. so the sql will be blocked. mysql> select ENGINE_TRANSACTION_ID,ENGINE_LOCK_ID,INDEX_NAME,lock_type,lock_mode,lock_data,LOCK_STATUS from data_locks; +-----------------------+------------------------------------+-----------------+-----------+------------------------+--------------------+-------------+ | ENGINE_TRANSACTION_ID | ENGINE_LOCK_ID | INDEX_NAME | lock_type | lock_mode | lock_data | LOCK_STATUS | +-----------------------+------------------------------------+-----------------+-----------+------------------------+--------------------+-------------+ | 13892 | 2227003724824:1147:2226967462376 | NULL | TABLE | IX | NULL | GRANTED | | 13892 | 2227003724824:81:5:4:2226967459464 | i_gg | RECORD | X,GAP,INSERT_INTENTION | 30, 0x000000039A02 | WAITING | | 13878 | 2227003723824:1147:2226967457320 | NULL | TABLE | IX | NULL | GRANTED | | 13878 | 2227003723824:81:5:4:2226967454408 | i_gg | RECORD | X | 30, 0x000000039A02 | GRANTED | | 13878 | 2227003723824:81:4:4:2226967454760 | GEN_CLUST_INDEX | RECORD | X,REC_NOT_GAP | 0x000000039A02 | GRANTED | | 13878 | 2227003723824:81:5:5:2226967455112 | i_gg | RECORD | X,GAP | 40, 0x000000039A03 | GRANTED | +-----------------------+------------------------------------+-----------------+-----------+------------------------+--------------------+-------------+ insert into gg values(39,null); -- the secondary index record is (39, row_id_6), and its next record is (40, row_id_4). the transaction need to request a `X|GAP|INSERT_INTENTION` lock on the next record (40, row_id_4) which there is a X|GAP lock on. so the sql will be blocked. mysql> select ENGINE_TRANSACTION_ID,ENGINE_LOCK_ID,INDEX_NAME,lock_type,lock_mode,lock_data,LOCK_STATUS from data_locks; +-----------------------+------------------------------------+-----------------+-----------+------------------------+--------------------+-------------+ | ENGINE_TRANSACTION_ID | ENGINE_LOCK_ID | INDEX_NAME | lock_type | lock_mode | lock_data | LOCK_STATUS | +-----------------------+------------------------------------+-----------------+-----------+------------------------+--------------------+-------------+ | 13893 | 2227003724824:1147:2226967462376 | NULL | TABLE | IX | NULL | GRANTED | | 13893 | 2227003724824:81:5:5:2226967459464 | i_gg | RECORD | X,GAP,INSERT_INTENTION | 40, 0x000000039A03 | WAITING | | 13878 | 2227003723824:1147:2226967457320 | NULL | TABLE | IX | NULL | GRANTED | | 13878 | 2227003723824:81:5:4:2226967454408 | i_gg | RECORD | X | 30, 0x000000039A02 | GRANTED | | 13878 | 2227003723824:81:4:4:2226967454760 | GEN_CLUST_INDEX | RECORD | X,REC_NOT_GAP | 0x000000039A02 | GRANTED | | 13878 | 2227003723824:81:5:5:2226967455112 | i_gg | RECORD | X,GAP | 40, 0x000000039A03 | GRANTED | +-----------------------+------------------------------------+-----------------+-----------+------------------------+--------------------+-------------+ insert into gg values(40,null); -- the secondary index record is (40, row_id_6), and its next record is (50, row_id_5). the transaction need to request a `X|GAP|INSERT_INTENTION` lock on the next record (50, row_id_5) which there is not any lock on. so the sql will be executed success. insert into gg values(50,null); -- the secondary index record is (50, row_id_6), and its next record is supremum. the transaction need to request a `X|GAP|INSERT_INTENTION` lock on the next record supremum which there is not any lock on. so the sql will be executed success.
[13 Jan 2023 14:29]
MySQL Verification Team
Hi, You have not provided us with a full innodb status and we are still waiting on it .......
[16 Jan 2023 1:47]
wei yao
After mysql> truncate table gg; Query OK, 0 rows affected (0.03 sec) mysql> insert into gg values(10,'10'),(20,'20'),(30,'30'),(40,'40'),(50,'50'); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> select * from gg force index(i_gg) where tid = 30 for update; +-----+-------+ | tid | tname | +-----+-------+ | 30 | 30 | +-----+-------+ 1 row in set (0.00 sec) The show innodb status info key part: ---TRANSACTION 3314, ACTIVE 16 sec 4 lock struct(s), heap size 1136, 3 row lock(s) MySQL thread id 33, OS thread handle 140591194830592, query id 2483 localhost root TABLE LOCK table `yaowei`.`gg` trx id 3314 lock mode IX RECORD LOCKS space id 14 page no 5 n bits 72 index i_gg of table `yaowei`.`gg` trx id 3314 lock_mode X Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 8000001e; asc ;; 1: len 6; hex 000000000802; asc ;; RECORD LOCKS space id 14 page no 4 n bits 72 index GEN_CLUST_INDEX of table `yaowei`.`gg` trx id 3314 lock_mode X locks rec but not gap Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 000000000802; asc ;; 1: len 6; hex 000000000ced; asc ;; 2: len 7; hex 81000000d1012e; asc .;; 3: len 4; hex 8000001e; asc ;; 4: len 2; hex 3330; asc 30;; RECORD LOCKS space id 14 page no 5 n bits 72 index i_gg of table `yaowei`.`gg` trx id 3314 lock_mode X locks gap before rec Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000028; asc (;; 1: len 6; hex 000000000803; asc ;;
[16 Jan 2023 1:49]
wei yao
Is dear [13 Jan 14:13] huahua xu show the correct explaination?
[16 Jan 2023 2:06]
wei yao
As metioned by [13 Jan 14:13] huahua xu insert into gg values(20,null); -- the secondary index record is (20, row_id_6), and its next record is (30, row_id_3). the transaction need to request a `X|GAP|INSERT_INTENTION` lock on the next record (30, row_id_3) which there is a X lock on. so the sql will be blocked. After i execute: mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> insert into gg values(20,null); Query OK, 1 row affected (0.00 sec) The innodb only shows table lock: ---TRANSACTION 3315, ACTIVE 295 sec 1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 1 MySQL thread id 33, OS thread handle 140591194830592, query id 2500 localhost root TABLE LOCK table `yaowei`.`gg` trx id 3315 lock mode IX -------- So, it seems the explaination about [13 Jan 14:13] huahua xu is incorrect?
[16 Jan 2023 8:08]
huahua xu
Hi wei yao, There is an implicit row lock optimization mechanism in the InnoDB, and the implicit lock on the record will be explicit(by creating an explicit record lock struct) when a record conflict is detected. so, you only find the table lock at the moment.
[17 Jan 2023 1:39]
wei yao
Hi, dear huahua xu, As metioned before, insert into gg values(20,null); -- the secondary index record is (20, row_id_6), and its next record is (30, row_id_3). the transaction need to request a `X|GAP|INSERT_INTENTION` lock on the next record (30, row_id_3) which there is a X lock on. so the sql will be blocked. there will be a record/gap lock on next record(30,row_id_3) after execute insert into gg values(20,null) But the following SQL execute success in another session(we call session b): mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> select * from gg where tid >20 for update; +-----+-------+ | tid | tname | +-----+-------+ | 30 | 30 | | 40 | 40 | | 50 | 50 | +-----+-------+ 3 rows in set (0.00 sec) So session b also acquired the X gap/record lock on (30,row_id_3), it seems impossible. Am i right?
[17 Jan 2023 7:36]
huahua xu
No, the transaction request the `X|LOCK_ORDINARY` lock on the records (30, row_id_3), (40, row_id_4), (50, row_id_5) and supremum. mysql> set autocommit=0; select * from gg where tid >20 for update; mysql> select ENGINE_TRANSACTION_ID,ENGINE_LOCK_ID,INDEX_NAME,lock_type,lock_mode,lock_data,LOCK_STATUS from performance_schema.data_locks; +-----------------------+------------------------------------+-----------------+-----------+---------------+------------------------+-------------+ | ENGINE_TRANSACTION_ID | ENGINE_LOCK_ID | INDEX_NAME | lock_type | lock_mode | lock_data | LOCK_STATUS | +-----------------------+------------------------------------+-----------------+-----------+---------------+------------------------+-------------+ | 14389 | 2587264958512:1158:2587228741704 | NULL | TABLE | IX | NULL | GRANTED | | 14389 | 2587264958512:83:5:1:2587228738792 | i_gg | RECORD | X | supremum pseudo-record | GRANTED | | 14389 | 2587264958512:83:5:4:2587228738792 | i_gg | RECORD | X | 30, 0x000000039B02 | GRANTED | | 14389 | 2587264958512:83:5:5:2587228738792 | i_gg | RECORD | X | 40, 0x000000039B03 | GRANTED | | 14389 | 2587264958512:83:5:6:2587228738792 | i_gg | RECORD | X | 50, 0x000000039B04 | GRANTED | | 14389 | 2587264958512:83:4:4:2587228739144 | GEN_CLUST_INDEX | RECORD | X,REC_NOT_GAP | 0x000000039B02 | GRANTED | | 14389 | 2587264958512:83:4:5:2587228739144 | GEN_CLUST_INDEX | RECORD | X,REC_NOT_GAP | 0x000000039B03 | GRANTED | | 14389 | 2587264958512:83:4:6:2587228739144 | GEN_CLUST_INDEX | RECORD | X,REC_NOT_GAP | 0x000000039B04 | GRANTED | +-----------------------+------------------------------------+-----------------+-----------+---------------+------------------------+-------------+ For the range scan with some locks, the record lock is not well optimized that it hold `X|LOCK_ORDINARY` locks on all record instead of a Gap lock, which consumes a lot of lock structure(memory) and processing overhead
