mysql - MySQL InnoDB 下一键锁定中唯一索引和非唯一索引之间差异背后的基本原理

Question

MySQL InnoDB 在事务中的非唯一索引上使用下一个键锁定，从而锁定扫描索引前后的间隙(顺便说一下，MySQL 手册未能以清晰的方式传达，手册页next-key locks 表示只有扫描索引之前的间隙被锁定:http://dev.mysql.com/doc/refman/5.7/en/innodb-record-level-locks.html。

但是，我无法理解这背后的全部原理......

使用的设置:

CREATE TABLE test (a int, b int, index (a));
INSERT INTO test VALUES (5,5), (10,10), (15,15);

第一个连接的客户端启动事务 A 并发出以下 UPDATE 查询:

UPDATE test set b = 10 where a = 10;

从下一个传入连接开始事务 B 运行以下查询会得到以下结果:

INSERT INTO test VALUES(5,5); //On hold
INSERT INTO test VALUES(9,9); //On hold
INSERT INTO test VALUES(14,14); //On hold
INSERT INTO test VALUES(4,4); //Works
INSERT INTO test VALUES 15,15); //Works
UPDATE test SET a = 1 WHERE a = 5; //Works
UPDATE test SET a = 8 WHERE a = 5; //On hold
UPDATE test SET a = 7 WHERE a = 15; //On hold
UPDATE test SET a = 100 WHERE a = 15; //Works

事务 B 似乎无法在 a 为 [5,15) (5 incl. - 15 excl.) 的位置插入行，也无法修改现有行并将 a 设置为 (5, 15) (5 excl. - 15 excl.) .

现在，将 a 列更改为具有 PRIMARY KEY:

ALTER TABLE test DROP INDEX a;
ALTER TABLE test ADD PRIMARY KEY (a);

在事务 B 中重做上面的运行现在给出以下结果(插入第 5 行和第 15 行给出了关于重复键的错误，这就是它们不包括在内的原因):

INSERT INTO test VALUES(9,9); //Works
INSERT INTO test VALUES(14,14); //Works
INSERT INTO test VALUES(4,4); //Works
INSERT INTO test VALUES(10,10); //On hold
UPDATE test SET a = 1 WHERE a = 5; //Works
UPDATE test SET a = 8 WHERE a = 5; //Works
UPDATE test SET a = 7 WHERE a = 15; //Works
UPDATE test SET a = 100 WHERE a = 15; //Works
UPDATE test SET a = 10 WHERE a = 15; //On hold
UPDATE test SET a = 100 WHERE a = 10; //On hold

使用主键的行为似乎完全可以理解，我不质疑它(即使缺少间隙锁，使用间隙锁来防止幻读的基本原理，也不会阻止幻读)。我根本不质疑这种行为，我只是很难理解处理常规索引的方式以及为什么以不同的方式处理它们。

问题:

1. 我们使用 next-key 锁的原因是防止幻读(这似乎暗示遵守 SELECT 查询不应返回的规则在隔离级别 REPEATABLE READ 中整个事务中的不同结果)或者是因为 InnoDB 推断用户可能希望插入接近查询的结果(这将然后成为用户的启发式服务)？第三个原因可能是整个系统原则似乎是锁定查询结果的任何行，而 InnoDB 这样做是没有考虑的(然后会遵守一些关于并发规则的总体原则) .来自 http://dev.mysql.com/doc/refman/5.7/en/innodb-next-key-locking.html似乎仅当 WHERE 子句具有像 a > 10 这样有意义的条件时，才使用 next-key 锁定来防止幻读，但如果是这样，为什么当 WHERE 子句专门针对某些行时，是否也应用了下一键锁定？也许有几个不相干的原因？
2. 鉴于下一个键锁定的原因，当列具有唯一索引与非唯一索引时，为什么需要有不同的行为？至少上面建议的前两个原因似乎并不需要这样做，即使第三个原因可能是 InnoDB 在列具有非唯一索引时必须搜索更多行。否则，对我来说，无论该列是否具有非唯一索引或唯一索引，人们似乎都希望插入附近的行......另一方面，更新行时，有没有理由相信用户会想在附近插入一行，所以为什么不锁定整个表，为什么要锁定它...？
3. 为什么行 a = 5 被锁定用于 INSERT 而不是用于 UPDATE？就好像同时有两种锁定原则在起作用，一种是锁定对现有行的修改，另一种是锁定插入，现有行 a = 5 未锁定，但行的插入a = 5 被锁定。这是正确的吗？如果是这样，为什么索引 5 包含在用于插入的间隙锁中？

我的 MySQL 版本是 5.5.24，我使用默认隔离级别 REPEATABLE READ。

Answer 1

你的问题太多了。 :)

我不是数据库专家，我只是给你一些提示。

一个)。索引约束不一定是唯一性的。当条件列没有索引或不是唯一索引时，MySQL 使用间隙锁。因为主键是唯一性索引，所以它只是锁定选中的记录。

b).当您更新索引列时，实际上记录需要重新索引。因为innodb使用了聚簇索引，也就是说记录在主索引B+树的叶子上。所以当需要找到一个位置放置更新后的 inode 时，数据库需要授予锁请求。

UPDATE test SET a = 10 WHERE a = 15; //On hold

a = 15 处没有锁，但是当您想将索引放置在 a = 10 处时，那里有一个现有锁。所以它成立。