Sample Header Ad - 728x90

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