MySQL Bugs: #118172: 猜测间隙锁可以加到普通索引上，导致只能防止插入操作而不能防止select ...for update操作

Bug #118172	猜测间隙锁可以加到普通索引上，导致只能防止插入操作而不能防止select ...for update操作
Submitted:	13 May 3:54	Modified:	23 Jul 7:57
Reporter:	hui wu	Email Updates:
Status:	Analyzing	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	8.0.31	OS:	Windows
Assigned to:	MySQL Verification Team	CPU Architecture:	x86 ( x86_64)
Tags:	gap locks

Description:
猜测间隙锁可以加到普通索引上，导致只能防止插入操作而不能防止select ...for update操作。

How to repeat:
设置select ...for update 语句的查询条件是普通索引+RR隔离级别。
复现过程请参考：https://fcneheqzlq8n.feishu.cn/wiki/JS0mwvniwiMAnSkUeivcJg5Tngf#share-HmJxd55cNolfgqx2Wtac...

Suggested fix:
请问这是否是bug？因为根据网上搜到的绝大多数资料都是显示间隙锁是加在两个索引之间而不能加到索引本身上的。因为Record锁 本身可以防止insert操作（已在隔离级别为 READ COMMITED 中实验过），这和Gap 锁防止insert操作的定位重复了，导致Next-key 锁并不完全等同于Record锁+Gap锁（因为你不能根据Next-key锁反推出Record锁和Gap锁分别是什么）。

这一点使我十分困扰，希望能够在文档上分清楚这些概念，否则排除死锁问题时可能会因为概念上的混淆浪费大量的时间。

Hello hui wu,

Thank you for the bug report.
Imho this is duplicate of Bug #107958, please see Bug #107958.
Let us know, if you feel that issue is different than the Bug #107958.

Regards,
Ashwini Patil

You believe that this bug is a duplicate of Bug #107958, but after carefully reading https://bugs.mysql.com/bug.php?id=107958&contribs=1, I feel that they are different. This is because Bug #107958, after being fixed, only allows the insert operation to acquire a record lock, which is not the same as my situation. The reproduction process I previously provided was in Chinese and not concise enough, so I have revised it and present the following reproduction process:

1.create table:

DROP TABLE IF EXISTS `test1`;
CREATE TABLE `test1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int NOT NULL,
  `address` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `test1_id_age_index` (`id`,`age`),
  KEY `test1_age_name_index` (`age`,`name`),
  KEY `test1_age_index` (`age`),
  KEY `test1_address_index` (`address`)
) ENGINE=InnoDB AUTO_INCREMENT=168 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

2. insert data into table:

INSERT INTO `test1` VALUES (20,'a',12,2),(143,'gg',4,2),(146,'gg',8,2),(152,'gg',24,2),(166,'ww',12,3),(167,'g',16,8);

3. start transaction 1:

begin ;
select age from test1 where age=12 for update;

4. start transaction 2:

begin;
select * from test1 where age=8 for update; -- not blocked and this may be a problem
insert into test1(name, age) VALUE ('gg', 8);-- is blocked
rollback;
-- commit;

5. Then you could find that gap locks can be applied to regular indexes, which only prevents insert operations but does not prevent select ... for update operations.

Sorry, I have no further questions, no need to reply. Indeed, as I thought, both gap locks and record locks can prevent insert operations (this is clearly demonstrated in cases involving ordinary indexes and unique indexes).  

Although my reproduction process revealed what appears to be a "bug" (an extra gap lock added at the left boundary during equality queries using an ordinary index), this "bug" currently has minimal impact on my work.