MySQL RR级别下，UPDATE在非主键索引上加锁分析

详细过程分析

让我们通过一个具体的例子来理解。假设我们有一张表 user_table：

id (主键索引A)	score (二级索引B)	name
5	10	Bob
10	20	Alice
15	20	Tom
20	30	Jerry

现在，我们执行以下更新语句：

-- 假设当前事务隔离级别为 RR
BEGIN;
UPDATE user_table SET name = ‘New’ WHERE score BETWEEN 15 AND 25;

第一步：在二级索引 B (score) 上加锁

1. 定位区间：首先，InnoDB 通过二级索引 score 找到满足条件 score BETWEEN 15 AND 25 的所有记录。

   • 找到 score=20 的两条记录（对应主键 id=10 和 id=15）。
   • 找到 score=30 的记录（id=20），它不满足条件（30 > 25），但它是第一个大于 25 的值，对于确定右边界很重要。

2. 添加 Next-Key Locks：为了防止其他事务插入 score 值在 (15, 25] 这个范围内的新记录（幻读），InnoDB 会在 score 索引上加上以下锁：

   • (10, 20]：这是一个 Next-Key Lock，锁住 score=20 这个索引项本身（行锁）以及它和上一个值 score=10 之间的间隙（间隙锁）。
   • (20, 30]：这是另一个 Next-Key Lock，锁住 score=30 这个索引项（虽然它本身不满足条件，但为了锁住 (20, 30) 这个间隙）以及它和 score=20 之间的间隙。

   实际上，锁住的间隙范围是 (10, 30)。这意味着其他事务无法插入 score 为 11 到 29 之间的任何新记录。

第二步：回表到主键索引 A (id) 上加锁

1. 回表操作：对于在二级索引上找到的每一条满足条件的记录（即 score=20 的两条记录），InnoDB 都需要通过它们存储的主键值（id=10 和 id=15）回到主键索引（聚簇索引）中找到完整的行数据。

2. 添加行锁（Record Locks）：在回表定位到主键索引上具体的行之后，InnoDB 会对这些行的主键索引记录加上行锁。

   • 对 id=10 这行加行锁。
   • 对 id=15 这行加行锁。

3. 为什么主键索引不加间隙锁？

   • 主键索引是唯一的，唯一索引上的范围查询在找到满足条件的记录后，就会停止继续查找（例如，找到 id=15 后，不会再去锁一个不存在的 id=16）。更重要的是，防止幻读的任务已经由二级索引上的间隙锁完成了。
   • 其他事务无法插入 score 在 (15,25] 范围内的新记录，自然也就无法产生拥有新主键 ID 的“幻行”。既然幻行的根源已经被切断，就没有必要再在主键索引的间隙上加锁了，这样可以减少锁的冲突，提高并发性能。

---

总结对比

索引类型	加的锁类型	目的
二级索引 B	Next-Key Locks (Gap Lock + Record Lock)	防止幻读，阻止其他事务在锁定范围内插入新数据
主键索引 A	Record Locks (行锁) ONLY	保证事务本身更新的数据一致性，防止被其他事务修改或删除