问 MySQL 非唯一索引等值查询加什么锁？
可重复读、读已提交两种隔离级别下，非唯一索引的等值查询会加什么锁？为什么这么加锁？
> 作者：操盛春，爱可生技术专家，公众号『一树一溪』作者，专注于研究 MySQL 和 OceanBase 源码。 > 爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。 > 本文基于 MySQL 8.0.32 源码，存储引擎为 InnoDB。
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 = 13 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 | 13, 3
***************************[ 2. 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
***************************[ 3. row ]***************************
engine_transaction_id | 281479856983976
object_name | t2
index_name | idx_i1
lock_type | RECORD
lock_mode | S,GAP
lock_status | GRANTED
lock_data | 14, 4
lock_data = 13,3、lock_mode = S 表示对二级索引 idx_i1 中 <i1 = 13, id="3"> 的记录加了共享 Next-Key 锁。
lock_data = 3、lock_mode = S,REC_NOT_GAP 表示对主键索引中 <id = 3> 的记录加了共享普通记录锁。
lock_data = 14,4、lock_mode = S,GAP 表示对二级索引 idx_i1 中 <i1 = 14, id="4"> 的记录加了共享间隙锁。
大家对这样的加锁情况是否有疑问呢？
如果你也有疑问，就让我们一起来看看 InnoDB 为什么会这样加锁吧。
可重复读隔离级别下：
- 对于 select 语句中 where 条件覆盖范围内的记录，默认加共享 Next-Key 锁。
- 对于 update、delete 语句中 where 条件覆盖范围内的记录，默认加排他 Next-Key 锁。
示例 SQL 对二级索引 idx_i1 中 <i1 = 13, id="3"> 的记录加了共享 Next-Key 锁，这属于默认行为，不多解释。
示例 SQL 执行过程中，从二级索引 idx_i1 中读取 <id = 13, id="3"> 的记录之后，需要根据其中的主键字段 <id = 13> 回表查询主键记录。
主键索引字段等值查询，读取记录之后，只需要对这条记录加普通记录锁，防止其它事务修改或者删除这条记录，就能保证可重复读。
这就是示例 SQL 对主键索引中 <id = 3> 的记录加共享普通记录锁的原因。
InnoDB 从二级索引 idx_i1 中读取 <i1 = 13, id="3"> 的记录之后，再回表找到主键索引中 <id = 3> 的记录，返回给 server 层。
where 条件命中的二级索引 idx_i1 是非唯一索引，server 层不能确定刚刚读取到的就是满足 where 条件的最后一条记录，所以会要求 InnoDB 继续读取下一条记录。
InnoDB 从二级索引 idx_i1 中读取下一条记录，得到 <i1 = 14, id="4"> 的记录，发现这条记录不匹配 server 层下推到 InnoDB 的 where 条件（i1 = 13），不需要锁定这条记录。
为了保证可重复读，要防止其它事务往 <i1 = 14, id="4"> 这条记录前面的间隙插入 <i1 = 13> 的记录，InnoDB 需要锁定这条记录前面的间隙，所以，对二级索引 idx_i1 中 <i1 = 14, id="4"> 的记录加共享间隙锁。
InnoDB 已经根据下推条件判断出 <i1 = 14, id="4"> 的记录不匹配 where 条件，不需要回表读取主键索引记录，也就不会对主键索引中 <id = 4> 的记录加锁了。
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 = 13 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 | 13, 3
***************************[ 2. 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 = 13,3、lock_mode = S,REC_NOT_GAP 表示对二级索引 idx_i1 中 <i1 = 13, id="3"> 的记录加了共享普通记录锁。
lock_data = 3、lock_mode = S,REC_NOT_GAP 表示对主键索引中 <id = 3> 的记录加了共享普通记录锁。
读已提交隔离级别下：
- 对于 select 语句中 where 条件覆盖范围内的记录，默认加共享普通记录锁。
- 对于 update、delete 语句中 where 条件覆盖范围内的记录，默认加排他普通记录锁。
示例 SQL 对二级索引 idx_i1 中 <i1 = 13, id="3"> 的记录加共享普通记录锁，属于默认行为，不多解释。
示例 SQL 从二级索引 idx_i1 中读取 <i1 = 13, id="3"> 的记录之后，根据主键字段值回表查询主键索引记录，因为读已提交隔离级别不需要保证可重复读，只需要防止其它事务修改或者删除主键索引中 <id = 3> 的记录，加共享普通记录锁就可以了。
回表读取到主键索引中 <id = 3> 的记录之后，InnoDB 会把记录返回给 server 层。
where 条件命中的二级索引 idx_i1 是非唯一索引，server 层不能确定刚刚读取到的就是满足 where 条件的最后一条记录，所以会要求 InnoDB 继续读取下一条记录。
InnoDB 从二级索引 idx_i1 中读取下一条记录，得到 <i1 = 14, id="4"> 的记录，发现这条记录不匹配 server 层下推到 InnoDB 的 where 条件（i1 = 13），不需要锁定这条记录。
读已提交隔离级别不需要保证可重复读，也就不需要对二级索引 idx_i1 中 <i1 = 14, id="4"> 的记录前面的间隙加共享间隙锁了。
4. 总结
没有需要总结的内容。
更多技术文章，请访问：https://opensource.actionsky.com/
关于 SQLE
SQLE 是一款全方位的 SQL 质量管理平台，覆盖开发至生产环境的 SQL 审核和管理。支持主流的开源、商业、国产数据库，为开发和运维提供流程自动化能力，提升上线效率，提高数据质量。
✨ Github：https://github.com/actiontech/sqle
📚 文档：https://actiontech.github.io/sqle-docs/
💻 官网：https://opensource.actionsky.com/sqle/
👥 微信群：请添加小助手加入 ActionOpenSource
🔗 商业支持：https://www.actionsky.com/sqle</i1></i1></id></id></i1></i1></id></i1></id></i1></i1></i1></i1></i1></id></i1></id></id></id></i1></i1></id></i1>
