MySQL Forums
Forum List  »  Quality Assurance

the bug of innodb next-key locks
Posted by: da DA
Date: February 14, 2022 01:24AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
the bug of innodb next-key locks
220
February 14, 2022 01:24AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.