MySQL 主键索引范围查询加什么锁？

可重复读、读已提交两种隔离级别下，主键索引范围查询会加什么锁？为什么这么加锁？

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

正文

1. 准备工作

创建测试表：

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

插入测试数据：

INSERT INTO `t1` (`id`, `i1`) VALUES
(10, 101), (20, 201), (30, 301), (40, 401);

2. 可重复读

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

SET transaction_isolation = 'REPEATABLE-READ';

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

执行以下 select 语句：

begin;
select * from t1 ignore index(idx_i1)
where id >= 10 and id < 30 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 = 't1'
 and lock_type = 'RECORD'\G

***************************[ 1. row ]***************************
engine_transaction_id | 281479856983976
object_name           | t1
index_name            | PRIMARY
lock_type             | RECORD
lock_mode             | S,REC_NOT_GAP
lock_status           | GRANTED
lock_data             | 10
***************************[ 2. row ]***************************
engine_transaction_id | 281479856983976
object_name           | t1
index_name            | PRIMARY
lock_type             | RECORD
lock_mode             | S
lock_status           | GRANTED
lock_data             | 20
***************************[ 3. row ]***************************
engine_transaction_id | 281479856983976
object_name           | t1
index_name            | PRIMARY
lock_type             | RECORD
lock_mode             | S,GAP
lock_status           | GRANTED
lock_data             | 30

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

没有按照默认行为加共享 Next-Key 锁，是因为示例 SQL 使用主键索引进行范围扫描，从 <id = 10> 的记录开始，不关心它前面的记录。

示例 SQL 并不在意其它事务往 <id = 10> 的记录前面插入什么记录，不需要锁住它前面的间隙，加普通记录锁就可以了。

如果有其它事务往 <id = 10> 的记录前面的间隙插入记录，示例 SQL 还能保证可重复读吗？

这个是没问题的。

因为其它事务往 <id = 10> 的记录前面的间隙插入记录，这些记录的 id 字段值一定小于 10，在示例 SQL 的 where 条件覆盖范围之外，不影响示例 SQL 的可重复读。

其它事务要往 t1 表中插入记录，id 大于等于 10、小于等于 19 的记录都会插入到 <id = 10> 和 <id = 20> 之间的间隙。这个间隙不归 <id = 10> 的记录上的锁管辖。

当然了，因为存在主键索引，t1 表中 <id = 10> 的记录删除之前，其它事务想要再插入 <id = 10> 的记录是不可能的。

lock_data = 20、lock_mode = S 表示对主键索引中 <id = 20> 的记录加了共享 Next-Key 锁，这是可重复读隔离级别下的默认行为，不多解释。

lock_data = 30、lock_mode = S,GAP 表示对主键索引中 <id = 30> 的记录加了共享间隙锁。

没有按照默认行为加共享 Next-Key 锁，是因为 <id = 30> 的记录位于示例 SQL 的 where 条件覆盖范围之外。

示例 SQL 不关心 <id = 30> 的记录本身，只需要保证其它事务不能往这条记录前面的间隙插入记录，加共享间隙加就满足需求了。

3. 读已提交

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

SET transaction_isolation = 'READ-COMMITTED';

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

执行以下 select 语句：

begin;
select * from t1 ignore index(idx_i1)
where id >= 10 and id < 30 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 = 't1'
 and lock_type = 'RECORD'\G

***************************[ 1. row ]***************************
engine_transaction_id | 281479856983976
object_name           | t1
index_name            | PRIMARY
lock_type             | RECORD
lock_mode             | S,REC_NOT_GAP
lock_status           | GRANTED
lock_data             | 10
***************************[ 2. row ]***************************
engine_transaction_id | 281479856983976
object_name           | t1
index_name            | PRIMARY
lock_type             | RECORD
lock_mode             | S,REC_NOT_GAP
lock_status           | GRANTED
lock_data             | 20

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

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

可重复读隔离级别对主键索引中 <id = 30> 的记录加了锁，读已提交隔离级别为什么没有对主键索引中 <id = 30> 的记录加锁呢？

其实读已提交隔离级别下，InnoDB 从主键索引中读取 <id = 30> 的记录之后，也会加共享普通记录锁。

InnoDB 把这条记录返回给 server 层之后，server 层判断这条记录不匹配 where 条件，会通知 InnoDB 释放这条记录上刚刚加的共享普通记录锁。

我们最终看到的结果就是示例 SQL 没有对主键索引中 <id = 30> 的记录加锁。

这种加了锁又释放的方式，一般情况下没什么影响，但是如果因为这种方式造成了死锁，我们不了解这个逻辑，就会有点摸不着头脑了。

4. 总结

可重复读隔离级别下，对某条记录加了锁，要等到事务提交或者回滚时才释放。

读已提交隔离级别下，对某条记录加了锁，如果 server 层或者 InnoDB 发现记录不匹配 where 条件，会马上释放锁。