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 where id = 10 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 

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

可重复读隔离级别下：

• 对于 select 语句中 where 条件覆盖范围内的记录，默认加共享 Next-Key 锁。
• 对于 update、delete 语句中 where 条件覆盖范围内的记录，默认加排他 Next-Key 锁。

上面的 select 语句却对 <id = 10> 的记录加了共享普通记录锁，这是为什么呢？

这就要从可重复读隔离级别的特点说起了。可重复读隔离级别下，同一个事务多次执行同一条 select 语句能够读取到数量和内容都完全相同的记录。

可重复读隔离级别下，对 where 条件覆盖范围内的记录默认加 Next-Key 锁，既锁住索引记录本身，防止其它事务修改或者删除记录，又锁定索引记录前面的间隙，防止其它事务往间隙中插入记录。

示例 SQL 的 where 条件中只包含主键索引字段，主键索引的唯一约束能够保证：只要不删除表中 <id = 10> 的记录，就不会再有其它 <id = 10> 的记录插入到主键索引中。

这种情况下，只需要对 select 语句中 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 t1 where id = 10 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 

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

读已提交隔离级别下：

• 对于 select 语句中 where 条件覆盖范围内的记录，默认加共享普通记录锁。
• 对于 update、delete 语句中 where 条件覆盖范围内的记录，默认加排他普通记录锁。

示例 SQL 执行过程中，对主键索引中 <id = 10> 的记录加共享普通记录锁，属于默认情况，不需要其它解释了。

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</id></id></id></id></id></id>