mysql next-key lock 问题

[cs8814336]

mysql Ver 14.14 Distrib 5.7.25, for Linux (x86_64) using EditLine wrapper

CREATE TABLE `ttt` (
  `uid` varchar(18) NOT NULL DEFAULT '',
  `gap` int NOT NULL ,

  PRIMARY KEY (`uid`),
  KEY `gap` (`gap`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='ccc_11332234';

insert into `ttt` values ('11111',1);
insert into `ttt` values ('11113',3);
insert into `ttt` values ('11115',5);
insert into `ttt` values ('11117',7);
insert into `ttt` values ('11119',9);

start transaction;

select * from `ttt` where `gap`=3 for update;

===============

另外一个客户端执行:

sql1: insert into ttt values ('22222',5);

sql2: insert into ttt values ('22233322',1);

阅读 mysql 技术内幕，其中对于 innodb 锁算法的时候看到,对于 1,3,5,7,9 数据存在的辅助索引，next-key 　 locking 应该(-无穷,1] (1,3] (3,5] (5,7]...　在执行上述 for update 时候，查询的时候辅助索引所以会加上 (1,3]的锁，而且会在下一个索引值加上 gap lock(3,5), 同时主键 11113 会加上 record lock.

但是上述实际执行时 sql 2 会被阻塞．就是说(1,3]左边也是闭区间，为什么？

同时想问为什么这种 next-key locking 能解决幻读问题.?

(提前感谢大家的回答)

[cs8814336]

@Wisho 恩，所以到现在我的问题还是没解决.看来源码...

[mmdsun]

https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html
Record Locks：该锁是对索引记录进行加锁！锁是在加索引上而不是行上的。注意了，innodb 一定存在聚簇索引，因此行锁最终都会落到聚簇索引上。
Gap Locks：是对索引的间隙加锁，其目的只有一个，防止其他事物插入数据。在 Read Committed 隔离级别下，不会使用间隙锁，隔离级别比 Read Committed 低的情况下，也不会使用间隙锁，如隔离级别为 Read Uncommited 时，也不存在间隙锁。当隔离级别为 Repeatable Read 和 Serializable 时，就会存在间隙锁。

Next-Key Locks：这个理解为 Record Lock+索引前面的 Gap Lock。锁住的是索引前面的间隙！比如一个索引包含值，10，11，13 和 20。那么，间隙锁的范围如下
(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)
好早以前整理的笔记。忘记是从哪里摘的了。

[cs8814336]

@mmdsun 感谢回答,你这个差不多就是技术内幕的原文

[junnplus]

试了下，好像锁的不是 1，sql2: insert into ttt values ('1',1);这个就不阻塞

[junnplus]

锁了 ('11111',1)~('11113',3)这个区间的，所以 gap=1 的时候，uid 大于'11111'都会阻塞

[cs8814336]

@junnplus 是真的耶，为什么呢

[phplin]

我也遇到同样情况了，楼主找到原因了吗，为啥和 innodb 内幕书上说的不一样啊

[cs8814336]

@phplin 他这种应该是根据现有记录锁的.select * from `ttt` where `gap`=3 for update; 锁住的是
记录 insert into `ttt` values ('11111',1) 记为 A 记录 到 insert into `ttt` values ('11113',3) 为 B 记录 的区间 和
记录 insert into `ttt` values ('11113',3); 到 insert into `ttt` values ('11115',5) 记录为 C 区间 的区间.
只是粗略记作 (1,3] (3,5), 实际上是锁住比 A 记录大 到 B 记录的区间 和 比 C 记录小却比 B 记录大的区间。

比 A 记录 (11111,1 )大的记录，实际则是锁住 gap 非聚集索引 的 B+tree 在他右边的记录。 则实际上排序为
('11111',1);('11113',3);('11115',5); 所以当 gap=1 时锁住了大于 11111 的区间，同理 gap=5 也被锁住小于 11115 的区间。