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

Question

MySQL InnoDB在事务中对非唯一索引使用下键锁定，其中扫描索引(Es)被锁定之前和之后的间隙(顺便说一下，MySQL手册未能以清晰的方式传递，下一个键锁上的手动页表示只有扫描索引(Es)之前的空白被锁定：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)的行。-不包括15 )也不修改现有行，并将a设为(5，15) (5除外)。-不包括15 )。

现在，将列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

使用主键的行为似乎是完全可以理解的，我并不怀疑它(尽管使用gap锁来阻止幻影读取的gap锁的缺乏并不能阻止幻影读取)。我一点也不怀疑这种行为，我只是很难理解规则索引是如何处理的，以及为什么它们被以不同的方式处理)。

问题：

1. 我们希望使用的下一个键锁是为了防止幻影读取(这似乎意味着遵守SELECT查询不应在整个事务中以隔离级别REPEATABLE READ返回不同结果的规则)，还是因为InnoDB推断用户可能希望在查询的结果附近进行插入(这将是用户的启发式服务)？第三个原因可能是，总体系统原则似乎是锁住查询在中产生的任何行，而InnoDB不需要考虑就可以这样做(这将遵循关于并发规则的一些总体原则)。从http://dev.mysql.com/doc/refman/5.7/en/innodb-next-key-locking.html中可以看出，只有当WHERE子句有类似于a > 10的条件时，才会使用下键锁定来防止幻影读取，但如果是这样的话，当WHERE子句具体地寻址某些行时，为什么还要应用下一个键锁定呢？也许有几个中断的原因？
2. 考虑到下一个键锁定的原因，当列有唯一索引和非唯一索引时，为什么有必要有不同的行为？至少上面提到的前两个原因似乎不需要这样做，尽管第三个原因可能是如果当列具有非唯一索引时，InnoDB必须搜索更多的行。否则，在我看来，无论列是非唯一索引还是唯一索引，都很可能希望插入一个封闭行.另一方面，在更新一行时，没有理由相信用户希望在旁边插入一行，那么为什么不锁定整个表--为什么要在它旁边.？
3. 为什么行a = 5为INSERT而不是UPDATE而被锁定？似乎同时有两个锁原则在起作用，一个锁现有行的修改，另一个锁定插入，现有行a = 5没有被锁定，但是行a = 5的插入被锁定。这是正确的吗?如果是的话，为什么索引5包含在插入的间隙锁中？

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

Answer 1

你的问题太多了。:)

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

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

b)。更新索引列时，记录实际上需要重新索引。因为innodb使用聚集索引，这意味着记录位于主索引B+树的叶子上。因此，当需要找到放置更新索引节点的位置时，数据库需要授予锁请求。

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

在a= 15处没有锁，但是当您想将索引放置在a =10 (其中有一个现有锁)时。所以它还能用。