the bug of innodb next-key locks

[da DA]

mysql version 8.0.27, isolation level RR





CREATE TABLE `user`
(
`id` int(0) NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`age` int(0) NULL DEFAULT NULL,
`value` int(0) NOT NULL,
`uni` bigint(0) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `value`(`value`) USING BTREE,
UNIQUE INDEX `uni`(`uni`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;



records

INSERT INTO `user`(`id`, `name`, `age`, `value`, `uni`) VALUES (440, 'Ed Venture', 57, 50, 76);
INSERT INTO `user`(`id`, `name`, `age`, `value`, `uni`) VALUES (514, 'Justin Casey Howells', 77, 17, 32);
INSERT INTO `user`(`id`, `name`, `age`, `value`, `uni`) VALUES (626, 'Dee Kay', 18, 3, 60);
INSERT INTO `user`(`id`, `name`, `age`, `value`, `uni`) VALUES (839, 'Bjorn Free', 75, 61, 80);
INSERT INTO `user`(`id`, `name`, `age`, `value`, `uni`) VALUES (880, 'Barb Dwyer', 70, 42, 52);


sql1:

start transaction;
select * from user where value <= 17 for update;

sql2:

start transaction;
select id from user where value <= 17 for update;

but, sql2 has one more than than sql1 an index record lock.

performance.schema#data_locks
lock_data: 880
lock_mode: X,REC_NOT_GAP
index_name: PRIMARY