Why does innodb next-key locks lock more range than the condition?
2
votes
1
answer
108
views
Assume the following table:
DROP TABLE IF EXISTS person;
CREATE TABLE person (
id int unsigned auto_increment primary key,
name varchar(255) not null,
age tinyint unsigned,
key (age)
);
INSERT INTO person (name, age)
VALUES ('bob', 10), ('alice', 16), ('jack', 19), ('william', 20);
And the following query:
begin;
select * from person where age < 12 for update;
rollback;
Why does innodb lock range [12, 16] as reported by:
select LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA
from performance_schema.data_locks;
TABLE,IX,GRANTED,
RECORD,X,GRANTED,"10, 1"
RECORD,X,GRANTED,"16, 2"
RECORD,"X,REC_NOT_GAP",GRANTED,1
Even there're new records inserted or modified between [12, 16], it will not cause phantom reads, right?
Asked by William
(155 rep)
Feb 15, 2025, 04:39 PM
Last activity: Feb 17, 2025, 02:41 PM
Last activity: Feb 17, 2025, 02:41 PM