为什么mysql间隙锁中非唯一索引的next-key lock不退化成行锁？

Question

CREATE TABLE `t` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`k` int(11) DEFAULT NULL,
PRIMARY KEY (`Id`),
KEY `k` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

表里三行数据(0,0)(5,5)(10,10)

我们知道，如果唯一索引等值查询会退化成行锁，比如：
select * from t where id=5 for update 只会锁住id=5这一行。
但是
select * from t where k=5 for update，就要锁住k in (0,10)整个区间。

难道是后一种情况下插入类似(3,3)这样的行会产生幻读或者导致binlog日志和数据不一致的问题，能举一个例子吗。我想了半天没想出这样的例子。
既然能在唯一索引上做退化成行锁的优化，为什么就不能应用在非唯一索引上呢？是工程上的问题吗，还是说其实非唯一索引也能应用这样的优化，只是mysql没做？

Answer 1

楼主说得没错，就是退化成行级别锁可能导致幻读问题。

如果将 非唯一 索引上存在重复值时使用行级别锁来代替 Next-Key Lock ，则可能允许其他事务插入一个与当前正在进行查询或扫描操作相关联但具有相同键值（但不同主键）的新记录。这就导致了幻读现象，破坏了事务的隔离性。

我举个例子，假设有如下表 employees

ID	Name	Salary
1	张三	5000
2	李四	6000
3	王五	7000

事务A：

BEGIN;
SELECT * FROM employees WHERE Salary > 5500 FOR UPDATE;

事务B：

BEGIN;
INSERT INTO employees (Name, Salary) VALUES ('马六', 6500);
COMMIT;

此时，事务A将会锁住满足条件 Salary > 5500 的记录，并且在查询结果上设置 Next-Key Locks 。这意味着除了已经存在的记录（ID=2 和 ID=3）之外，也会锁住间隙 (5000,6000] 和 (6000,7000]。

如果非唯一索引的Next-Key Lock可以退化成行级别锁，那么事务B将能够成功插入一条工资为6500的记录，导致幻读 问题。

所以事务B必须 等待 事务A释放对间隙 (6000,7000] 的锁定才能继续执行（所以 Next-Key Locks 不能是行级别锁）。这确保了数据的完整性和一致性，并避免了幻读现象。

Answer 2

next-key lock锁定的区间是左开右闭。当你只有(0, 0)、(5, 5)、(10, 10）三条数据时，字段K，其区间范围为(-∞, 0]、(0, 5]、(5, 10]、(10, ∞]，因此，当K=5的情况时，锁定了，两个区间，加起来就是(0，10]区间了。

next-key lock的锁定区间，其主要目的是为了解决幻读。我理解如果是在RR级别下，有next-key lock解决幻读，这样的例子应该是举不出来。可以在RC级别试试，这时候没解决幻读，应该不存在next-key lock了。

在唯一索引上，退化成行锁，是因为可以找到唯一的一条记录，就可以使用Record lock（单个行记录的锁）。注意：如果查询不存在的唯一记录也是会导致gap lock。例如：select * from t where id=6 for update。而非唯一索引，本身就可能在数据库中存在多行数据，因此，我理解不能退化成Record lock是正常的。

Answer 3

其实要锁住(0,5]和(5,10)的最主要原因，就是普通索引的key可能重复，当key重复时，就是按主键来排序了，对于普通索引的B+树，数据(4,5)在数据(5,5)的左侧，而(6,5)会在数据(5,5)的右侧，所以将(5,5)两侧的区间锁住，可以防止幻读。

而且楼主你可以试下，记录(9,10)是插入不进去的，而(11,10)却是可以插入进去的，为什么呢？因为(11,10)是在(10,10)的右侧，并没有在被锁定的区间里