mysql在可重复读(Repeatable Read)隔离级别下,会产生间隙锁(gap lock),其他隔离级别不会产生这个问题。
问题描述
- 表初始化
CREATE TABLE z (
id INT PRIMARY KEY AUTO_INCREMENT,
b INT,
KEY b(b)
)
ENGINE = InnoDB
DEFAULT CHARSET = utf8;
INSERT INTO z (id, b)
VALUES (1, 2),
(3, 4),
(5, 6),
(7, 8),
(9, 10);
- session A
BEGIN;
SELECT *
FROM z
WHERE b = 6 FOR UPDATE;
- session B
INSERT INTO z VALUES (2, 4);/*success*/
INSERT INTO z VALUES (2, 8);/*blocked*/
INSERT INTO z VALUES (4, 4);/*blocked*/
INSERT INTO z VALUES (4, 8);/*blocked*/
INSERT INTO z VALUES (8, 4);/*blocked*/
INSERT INTO z VALUES (8, 8);/*success*/
INSERT INTO z VALUES (0, 4);/*blocked*/
INSERT INTO z VALUES (-1, 4);/*success*/
分别执行 session B中的insert 会出现上述情况,为什么?
加锁过程
在索引 b 上的等值查询,给索引 b 加上了 next-key lock (4, 6];索引向右遍历,且最后一个值不满足条件时退化为间隙锁;所以会再加上间隙锁 (6,8);所以索引 b 上的 next-key lock 的范围是(b=4,id=3)到(b=6,id=5)这个左开右闭区间和(b=6,id=5)到(b=8,id=7)这个开区间
for update 会给 b = 6 这一行加上行锁;因此 (b=6,id=5) 这一行上有行锁
- 这么看来上述语句都不在锁的范围内,为什么会被锁
这个问题其实是因为没有理解索引的结构,所以认为所有值都不应该被锁
- 如图所示,此时索引 b 上的锁:
图中绿色部分即为被锁范围;索引会根据 b 和 id 的值进行排序,插入不同的值,锁的范围是不一样的;分别插入 (b=4,id=2) 和(b=4,id=4),插入的位置如图所示:
- 因为索引是有序的,此时,由于记录(b=4,id=3)的存在,(b=4,id=2)不在锁的范围内,可以插入,但(b=4,id=4)在锁的范围内,所以插入时需要等待锁释放,被 blocked
- 对于其他(id,b)的值(2,8),(4,8),(8,4),(8,8)也是同样的道理;因此,得出以下结论:
- id <= 2 时,b 索引锁范围为 (4,8]
- 2 < id < 8 时,b 索引锁范围为 [4,8]
- a >= 8 时,b 索引锁范围为 [4,8)
- 但是,实践发现,当 id=0 时,被锁的范围为 [4,8),这和我们得到的第一个结论(4,8]不一样;此时分析得到的示意图为:
- 在 session A 中尝试插入 (b=4, id=0)并查询结果:
INSERT INTO z
VALUES (0, 4);
SELECT *
FROM z;
此时,发现表中并没有发现 (b=4, id=0)这条记录,但是多了 (b=4,id=10)这条;所以插入 (b=4, id=0)的时候真正插入的是 (b=4,id=10)这个值;这是因为我们在创建表的时候指定主键 id 的值 AUTO INCREMENT,当插入的主键值为0的时候,会被替换为 AUTO_INCREMENT的值,即10
对此,MySQL 官方文档中的解释是:在非 NO_AUTO_VALUE_ON_ZERO模式下,给自增的列赋值为 0,都会被替换为自增序列的下一个值;当该自增列值指定 NOT NULL 时赋值 NULL,也会被替换;当插入其他值时,自增序列的值会被替换为当前列中最大值的下一个值;参考 MySQL 8.0 Reference Manual 文档,Tutorial 的 Examples of Common Queries , 3.6.9 Using AUTO_INCREMENT
- 如果将主键修改为不自增,插入 (b=4, id=0) 会得到这条记录