What's the difference between Next-Key Locks And Gap Lock?

user_8229839 0
Viewed 788

Recently I read InnoDB Locks in MySQL5.7 doc, https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-gap-locks. I'm confuse about Next-Key Lock and Gap Lock. In the doc, the Next-Key Lock locks the record and the gap before it, and the Gap Lock locks the only the gap? for example:

CREATE TABLE `r` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`k` int(11) DEFAULT NULL,
`u` int(11) DEFAULT NULL,
`v` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_u` (`u`),
KEY `idx_k` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 insert into r values (2,2,2,2),(6,6,6,6),(7,7,7,7);

statement 1: select * from r where u = 4 for update;

statement 1 use the unique index search the record dose not exist, it use the gap lock, lock the area u B+tree from (u=2, id=2) to (u=6, id=6) prevent from inserting, but do not lock the record k B+tree (u=2, id=2) and (u=6, id=6).

statement 2: select * from r where k = 6 for update;

statement 2 use the non-unique index search the record existed, it use the next-key lock locks the area k B+tree from ((k=2, id=2), (k=6, id=6)] and a gap lock locks the area k B+tree from ((k=6, id=6), (k=7, id=7)) ,also lock the cluster-index where id = 6;

so my question is:
1, the lock area ((k=2, id=2), (k=6, id=6)] is a next-key lock, and the area ((k=6, id=6), (k=7, id=7)) is a gap lock, am I right?
2，Is the understanding of the two statement right?

Please forgive me for my English.

mysql
1 Answers

I think all your statements are right.

Another way to check the answer is to check the data_locks table for the locks performed by innodb.

select * from performance_schema.data_locks;

And you can check the LockMode column:

  • X/S :means next-key lock;

  • X/S, Gap :the combo means gap lock end;

  • X/S, Rec_not_gap :the combo means record lock.

user_126403050
Related