38 期 | 非唯一索引范围查询加什么锁？

可重复读、读已提交两种隔离级别下，非唯一索引范围查询加什么锁？

作者：操盛春，爱可生技术专家，公众号『一树一溪』作者，专注于研究 MySQL 和 OceanBase 源码。
爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。
本文基于 MySQL 8.0.32 源码，存储引擎为 InnoDB。

目录
[TOC]

正文

1. 准备工作

创建测试表：

CREATE TABLE `t2` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `i1` int DEFAULT '0',
  `i2` int DEFAULT '0',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_i1` (`i1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

插入测试数据：

INSERT INTO `t2` (`id`, `i1`, `i2`) VALUES
(1, 11, 21), (2, 12, 22),(3, 13, 23),
(4, 14, 24),(5, 15, 25),(6, 16, 26);

2. 可重复读

把事务隔离级别设置为 REPEATABLE-READ（如已设置，忽略此步骤）：

SET transaction_isolation = 'REPEATABLE-READ';

-- 确认设置成功
SHOW VARIABLES like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+

执行以下 select 语句：

begin;
select * from t2
where i1 >= 12 and i1 < 14
for share;

查看加锁情况：

select
   engine_transaction_id, object_name, index_name,
   lock_type, lock_mode, lock_status, lock_data
 from performance_schema.data_locks
 where object_name = 't2'
 and lock_type = 'RECORD'\G

***************************[ 1. row ]***************************
engine_transaction_id | 281479856983976
object_name           | t2
index_name            | idx_i1
lock_type             | RECORD
lock_mode             | S
lock_status           | GRANTED
lock_data             | 12, 2
***************************[ 2. row ]***************************
engine_transaction_id | 281479856983976
object_name           | t2
index_name            | idx_i1
lock_type             | RECORD
lock_mode             | S
lock_status           | GRANTED
lock_data             | 13, 3
***************************[ 3. row ]***************************
engine_transaction_id | 281479856983976
object_name           | t2
index_name            | idx_i1
lock_type             | RECORD
lock_mode             | S
lock_status           | GRANTED
lock_data             | 14, 4
***************************[ 4. row ]***************************
engine_transaction_id | 281479856983976
object_name           | t2
index_name            | PRIMARY
lock_type             | RECORD
lock_mode             | S,REC_NOT_GAP
lock_status           | GRANTED
lock_data             | 2
***************************[ 5. row ]***************************
engine_transaction_id | 281479856983976
object_name           | t2
index_name            | PRIMARY
lock_type             | RECORD
lock_mode             | S,REC_NOT_GAP
lock_status           | GRANTED
lock_data             | 3

lock_data = 12,2、lock_mode = S 表示对二级索引 idx_i1 中 的记录加了共享 Next-Key 锁。

这本来是可重复读隔离级别的默认行为，不需要多解释什么。

但是，从逻辑上来说，这里其实是可以优化的，InnoDB 却没有优化，所以这里要多说两句。

二级索引 idx_i1 中只有一条记录的 i1 字段值为 13，示例 SQL 执行过程中，从 的记录开始扫描，id 小于 13 的记录位于 where 条件范围之外，不需要锁住 的记录前面的间隙，只要锁住 的记录本身，就能够保证可重复读。

然而，示例 SQL 却对二级索引 idx_i1 中 的记录加了共享 Next-Key 锁，即锁定了 的记录本身，又锁定了它前面的间隙，这实际上扩大了锁定范围。

之所以这么加锁，我推测原因如下。

二级索引 idx_i1 是非唯一索引，允许存在 i1 字段值相同的多条记录，要对扫描范围内的第一条记录区别对待（只加普通记录锁），会增加代码逻辑的复杂性，所以干脆一视同仁，都按照可重复读隔离级别的默认行为加 Next-Key 锁。

lock_data = 13,3、lock_mode = S 表示对二级索引 idx_i1 中 的记录加了共享 Next-Key 锁，这是可重复读隔离级别的默认行为，不多解释了。

lock_data = 14,4、lock_mode = S 表示对二级索引 idx_i1 中 的记录加了共享 Next-Key 锁。

这本来也是可重复读隔离级别的默认行为，不需要多解释什么。

但是，因为二级索引 idx_i1 中 的记录位于 where 条件范围之外，为了保证可读性，只要锁住记录前面的间隙，防止其它事务插入 i1 小于 14 的记录就能满足了。

这里对 的记录加了 Next-Key 锁，即锁住了记录前面的间隙，又锁住了记录本身，也扩大了锁定范围。

lock_data = 2、lock_mode = S,REC_NOT_GAP 表示对主键索引中 的记录加了共享普通记录锁。

lock_data = 3、lock_mode = S,REC_NOT_GAP 表示对主键索引中 的记录加了共享普通记录锁。

从二级索引 idx_i1 中读取 和 两条记录之后，根据其中的主键字段值回表查询主键索引记录，只需要防止其它事务修改或者删除对应的主键记录，就能保证示例 SQL 的可重复读，加普通记录锁就能满足需求了。

示例 SQL 执行过程中，还从二级索引 idx_i1 中读取了 的记录，为什么没有对主键索引中 的记录加锁呢？

因为读取 的记录之后，InnoDB 根据下推的 where 条件判断出来这条记录不匹配 where 条件，不需要回表查询主键索引记录，也就不会对主键索引记录中 的记录加锁了。

3. 读已提交

把事务隔离级别设置为 READ-COMMITTED（如已设置，忽略此步骤）：

SET transaction_isolation = 'READ-COMMITTED';

-- 确认设置成功
SHOW VARIABLES like 'transaction_isolation';
+-----------------------+----------------+
| Variable_name         | Value          |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+

执行以下 select 语句：

begin;
select * from t2
where i1 >= 12 and i1 < 14
for share;

查看加锁情况：

select
   engine_transaction_id, object_name, index_name,
   lock_type, lock_mode, lock_status, lock_data
 from performance_schema.data_locks
 where object_name = 't2'
 and lock_type = 'RECORD'\G

***************************[ 1. row ]***************************
engine_transaction_id | 281479856983976
object_name           | t2
index_name            | idx_i1
lock_type             | RECORD
lock_mode             | S,REC_NOT_GAP
lock_status           | GRANTED
lock_data             | 12, 2
***************************[ 2. row ]***************************
engine_transaction_id | 281479856983976
object_name           | t2
index_name            | idx_i1
lock_type             | RECORD
lock_mode             | S,REC_NOT_GAP
lock_status           | GRANTED
lock_data             | 13, 3
***************************[ 3. row ]***************************
engine_transaction_id | 281479856983976
object_name           | t2
index_name            | PRIMARY
lock_type             | RECORD
lock_mode             | S,REC_NOT_GAP
lock_status           | GRANTED
lock_data             | 2
***************************[ 4. row ]***************************
engine_transaction_id | 281479856983976
object_name           | t2
index_name            | PRIMARY
lock_type             | RECORD
lock_mode             | S,REC_NOT_GAP
lock_status           | GRANTED
lock_data             | 3

lock_data = 12,2、lock_mode = S,REC_NOT_GAP 表示对二级索引 idx_i1 中 的记录加了共享普通记录锁，这是读已提交隔离级别的默认行为，不多解释了。

lock_data = 13,3、lock_mode = S,REC_NOT_GAP 表示对二级索引 idx_i1 中 的记录加了共享普通记录锁，这是读已提交隔离级别的默认行为，也不多解释了。

示例 SQL 为什么没有对二级索引 idx_i1 中 的记录加锁呢？

按照读已提交隔离级别的默认行为，也应该对 的记录加共享普通记录锁才符合逻辑。

这是因为我们没有看到加锁过程，只看到了最终结果。

示例 SQL 执行过程中，从二级索引 idx_i1 中读取 的记录之后，对这条记录加了共享普通记录锁。

然后，InnoDB 根据下推条件判断出来这条记录不匹配 where 条件，就把刚刚加的共享普通记录锁给释放了。

lock_data = 2、lock_mode = S,REC_NOT_GAP 表示对主键索引中 的记录加了共享普通记录锁，这是读已提交隔离级别的默认行为，不多解释了。

lock_data = 3、lock_mode = S,REC_NOT_GAP 表示对主键索引中 的记录加了共享普通记录锁，这是读已提交隔离级别的默认行为，也不多解释了。

示例 SQL 为什么没有对主键索引中 的记录加锁呢？

这是因为示例 SQL 执行过程中，从二级索引 idx_i1 中读取 的记录之后，InnoDB 根据下推条件判断出来这条记录不匹配 where 条件，不需要回表查询主键索引记录，也就不会对主键索引记录加锁了。

4. 总结

读已提交隔离级别下，我们看到的加锁情况，只是最终结果，不一定能完全代表加锁过程。