MySQL Bugs: #107958: Document for gap lock of InnoDB needs improvement

Bug #107958	Document for gap lock of InnoDB needs improvement
Submitted:	25 Jul 2022 9:01	Modified:	26 Jul 2022 1:22
Reporter:	Jia Howard	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.7	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	gap lock, innodb

Description:
In https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html, I can see

"However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row."

But if the statement didn't found any record, a gap lock is generated.

How to repeat:
1 create table:

CREATE TABLE `bar` (
  `id` int(11) NOT NULL,
  `age` int(11) NOT NULL,
  `value` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2 insert data:

INSERT INTO bar (id, age, value) VALUES(1, 1, 1);
INSERT INTO bar (id, age, value) VALUES(5, 5, 5);
INSERT INTO bar (id, age, value) VALUES(9, 9, 9);

3 query a unique row using a unique index

start transaction;
update bar set value = 0 where id =8;

4 will see a gap lock

---TRANSACTION 5224, ACTIVE 3 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 25, OS thread handle 123145566404608, query id 2727 localhost 127.0.0.1 root
TABLE LOCK table `bar`.`bar` trx id 5224 lock mode IX
RECORD LOCKS space id 85 page no 3 n bits 72 index PRIMARY of table `bar`.`bar` trx id 5224 lock_mode X locks gap before rec

Suggested fix:
Maybe only an index record lock is required for statements that lock rows using a unique index to search for a unique row, but if they didn't found any row, gap locks are still required?

Hi Mr. 宏超 贾,

Thank you for your bug report.

However, it is not a bug, but expected behaviour.

The UPDATE on a non-existing row is behaving the same as if you would issue a locking SELECT statement on the same row. This is done in order to maintain consistent views for each session.

Not a bug.

Hi Mr . 宏超 贾,

We agree with you that this should be documented.

Hence, this is a verified documentation bug.

I also see

SELECT ... LOCK IN SHARE MODE sets shared next-key locks on all index records the search encounters. However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row.

SELECT ... FOR UPDATE sets an exclusive next-key lock on every record the search encounters. However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row.

So, please update these together, since they make end users like us confused. Thanks.