| Bug #112463 | MySQL Delete, Insert Lock Occur | ||
|---|---|---|---|
| Submitted: | 26 Sep 2023 9:01 | Modified: | 4 Oct 2023 7:46 |
| Reporter: | JAEHYUN LEE | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 8.0.34 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[26 Sep 2023 9:01]
JAEHYUN LEE
[27 Sep 2023 0:27]
JAEHYUN LEE
In the first step, table setup is like this. The insert syntax have problems. mysql> select * from test02; +----+--------+--------+ | id | value1 | value2 | +----+--------+--------+ | 1 | 100 | 100 | | 3 | 100 | 101 | | 2 | 101 | 100 | | 5 | 101 | 102 | | 4 | 102 | 101 | +----+--------+--------+
[28 Sep 2023 12:04]
MySQL Verification Team
Hi Mr. LEE, Thank you for your bug report. However, this is not a bug. First of all, your test case contains duplicate values for the column which is PRIMARY KEY. Second, the explanation for the GAP locks is very simple. Every INSERT has to take insert intention locks, which can bee shown as GAP locks. And indeed if you analyse the output from the EXPLAIN of the SELECT: d select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index NULL idx01 10 NULL 5 100.00 Using index that means that table is so small that INSERT has to read it sequentially by the Primary Key. Also, isolation levels are more about handling of the locks from the concurrent transactions, except for the SERIALIZABLE isolation level. Not a bug.
[4 Oct 2023 7:46]
JAEHYUN LEE
Thanks for your description. But I think the problem is not about insert intention lock. I'll add new test case. Situation. mysql> show create table test01; +--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | test01 | CREATE TABLE `test01` ( `id` int NOT NULL, `name1` varchar(10) DEFAULT NULL, `name2` varchar(10) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `idx01` (`name1`,`name2`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 | +--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select * from test01; +-----+-------+-------+ | id | name1 | name2 | +-----+-------+-------+ | 1 | i1 | i1 | | 100 | i100 | i100 | | 111 | i101 | i101 | | 6 | i6 | i6 | | 8 | i8 | i8 | +-----+-------+-------+ 5 rows in set (0.00 sec) Transaction 1 mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> delete from test01 where id = 6; Query OK, 1 row affected (0.00 sec) mysql> insert into test01 values (7, 'i6', 'i6'); Query OK, 1 row affected (0.00 sec) Then, in transaction 2, mysql> insert into test01 values (117, 'i7', 'i7'); it freezed. In performance_schema, mysql> select * from performance_schema.data_locks; +--------+------------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+------------------------+-------------+---------------+ | ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +--------+------------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+------------------------+-------------+---------------+ | INNODB | 140023637532672:1536:140023517210240 | 2797530 | 407 | 61 | test | test01 | NULL | NULL | NULL | 140023517210240 | TABLE | IX | GRANTED | NULL | | INNODB | 140023637532672:375:5:7:140023517207136 | 2797530 | 407 | 61 | test | test01 | NULL | NULL | idx01 | 140023517207136 | RECORD | X,GAP,INSERT_INTENTION | WAITING | 'i8', 'i8', 8 | | INNODB | 140023637531864:1536:140023517204160 | 2797524 | 205 | 457 | test | test01 | NULL | NULL | NULL | 140023517204160 | TABLE | IX | GRANTED | NULL | | INNODB | 140023637531864:375:4:6:140023517201104 | 2797524 | 205 | 457 | test | test01 | NULL | NULL | PRIMARY | 140023517201104 | RECORD | X,REC_NOT_GAP | GRANTED | 6 | | INNODB | 140023637531864:375:5:6:140023517201448 | 2797524 | 205 | 458 | test | test01 | NULL | NULL | idx01 | 140023517201448 | RECORD | X,REC_NOT_GAP | GRANTED | 'i6', 'i6', 6 | | INNODB | 140023637531864:375:5:6:140023517201792 | 2797524 | 205 | 458 | test | test01 | NULL | NULL | idx01 | 140023517201792 | RECORD | S,GAP | GRANTED | 'i6', 'i6', 6 | | INNODB | 140023637531864:375:5:7:140023517201792 | 2797524 | 205 | 458 | test | test01 | NULL | NULL | idx01 | 140023517201792 | RECORD | S,GAP | GRANTED | 'i8', 'i8', 8 | | INNODB | 140023637531864:375:5:14:140023517201792 | 2797524 | 205 | 458 | test | test01 | NULL | NULL | idx01 | 140023517201792 | RECORD | S,GAP | GRANTED | 'i6', 'i6', 7 | +--------+------------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+------------------------+-------------+---------------+ 8 rows in set (0.00 sec) I think the result shows that insert intentional lock waits for this lock. | INNODB | 140023637531864:375:5:7:140023517201792 | 2797524 | 205 | 458 | test | test01 | NULL | NULL | idx01 | 140023517201792 | RECORD | S,GAP | GRANTED | 'i8', 'i8', 8 | But It is not an insert intentional lock. If it does, then transaction 2's insert do not have to wait. Also, the mysql document says that read committed isolation level does not use gap lock for pk or uk delete / update. So I think the transaction2 does not have to wait until the transaction1 ends.
[4 Oct 2023 11:24]
MySQL Verification Team
Hi Mr. LEE, This is still not a bug. InnoDB uses lock escalations, which includes the gap locks. The gap lock comes from DELETE. Please, let us know the full sub-chapter number in our Reference Manual regarding READ-COMMITTED behaviour that you describe.
