MySQL InnoDB在事务中的非唯一索引上使用下一键锁定,从而锁定扫描索引之前和之后的间隙(这是因为MySQL手册无法以清晰的方式传达,下一键的手册页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包括 – 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

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

问题：

>使用我们想要阻止幻像读取的下一键锁定的原因(这似乎意味着遵守SELECT查询不应在隔离级别REPEATABLE READ中的整个事务中返回不同结果的规则),还是因为InnoDB推断用户可能希望插入接近查询的结果(这将是对用户的启发式服务)？第三个原因可能是整个系统原理似乎是锁定查询导致的任何行,并且InnoDB不加考虑地执行此操作(这将遵循关于并发规则的一些总体原则).从http://dev.mysql.com/doc/refman/5.7/en/innodb-next-key-locking.html开始,似乎只有当WHEREclause具有类似于>的条件时才使用下一键锁定来防止幻像读取. 10哪里有意义,但如果是这样,为什么在WHERE子句特定地解决某些行时也应用了下一键锁定？也许有几个分离的原因？
>鉴于下一键锁定的原因,当列具有唯一索引与非唯一索引时,为什么必须具有不同的行为？至少上面提出的前两个原因似乎没有要求这个,即使第三个可能是InnoDB在列具有非唯一索引时必须搜索更多行.否则,对我来说,无论列是否具有非唯一索引或唯一索引,似乎都可能想要插入一个近似行…另一方面,当更新行时,有没有理由相信用户会想要在附近插入一行,为什么不锁定整个表为什么你在它…？
>为什么行a = 5被锁定为INSERT而不是UPDATE？就好像同时有两个锁定原则在起作用,一个锁定现有行的修改,一个锁定插入并且现有行a = 5未被锁定但是行a = 5的插入被锁定.这是正确的,如果是这样,为什么索引5包含在插入的间隙锁中？

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

解决方法:

你的问题太多了.