MySQL 非唯一索引范围查询加什么锁？

头像头饰
爱可生开源社区
     上海
    阅读 6 分钟

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

    作者：操盛春，爱可生技术专家，公众号『一树一溪』作者，专注于研究 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 中 <i1 = 12, id = 2> 的记录加了共享 Next-Key 锁。

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

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

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

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

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

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

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

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

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

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

    这里对 <i1 = 14, id = 4> 的记录加了 Next-Key 锁，即锁住了记录前面的间隙，又锁住了记录本身，也扩大了锁定范围。

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

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

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

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

    因为读取 <i1 = 14, id = 4> 的记录之后，InnoDB 根据下推的 where 条件判断出来这条记录不匹配 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 >= 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 中 <i1 = 12, id = 2> 的记录加了共享普通记录锁，这是读已提交隔离级别的默认行为，不多解释了。

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

    示例 SQL 为什么没有对二级索引 idx_i1 中 <i1 = 14, id = 4> 的记录加锁呢？

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

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

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

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

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

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

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

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

    4. 总结

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

    mysql数据库
    爱可生开源社区
    426 声望208 粉丝

    成立于 2017 年，以开源高质量的运维工具、日常分享技术干货内容、持续的全国性的社区活动为社区己任；目前开源的产品有：SQL审核工具 SQLE，分布式中间件 DBLE、数据传输组件DTLE。

    « 上一篇
    MySQL 主键索引范围查询加什么锁？
    下一篇 »
    MySQL 死锁案例分析（1）插入意向锁

    引用和评论

    推荐阅读
    头像
    MySQL 死锁案例分析（1）插入意向锁

    爱可生开源社区

    头像
    在Java程序中监听mysql的binlog

    huan1993赞 11阅读 2.8k

    头像
    Mysql数据库sql优化案例

    杜若赞 5阅读 2.8k

    头像
    数据集汇总｜18个电影/音乐数据集汇总，覆盖影片/歌曲推荐、电影评价、歌词识别、音乐流派······

    超神经HyperAI赞 3阅读 2k

    头像
    使用 MongoDB 构建 AI：Gradient Accelerator Block 如何在几秒钟内让您从零开发 AI

    MongoDB数据平台赞 1阅读 13k

    头像
    MongoDB Atlas与YoMio.AI近乎完美适配:推理更快速、查询更灵活、场景更丰富

    MongoDB数据平台赞 1阅读 12.4k

    头像
    高效工程师的七个习惯

    Bytebase赞 3阅读 1.3k

    0 条评论
    得票最新