关于叶老师一个RR模式下UPDATE锁范围扩大案例的研究

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 原创转载请注明出处有误请指出 一、前言 叶金荣老师分享了一篇文章如下:https://mp.weixin.qq.com/s/09DJCyMq8kBn4mlezgzUgg 这里只研究下锁的模式,借用叶老师的表和语句 my...

原创转载请注明出处有误请指出


一、前言

叶金荣老师分享了一篇文章如下:
https://mp.weixin.qq.com/s/09DJCyMq8kBn4mlezgzUgg
这里只研究下锁的模式,借用叶老师的表和语句

mysql> select * from t1;
+----+----+----+----+
| c1 | c2 | c3 | c4 |
+----+----+----+----+
|  0 |  0 |  0 |  0 |
|  1 |  1 |  1 |  0 |
|  3 |  3 |  3 |  0 |
|  4 |  2 |  2 |  0 |
|  6 |  8 |  5 |  0 |
|  7 |  6 |  6 | 10 |
| 10 | 10 |  4 |  0 |
+----+----+----+----+ 
CREATE TABLE `t1` (
  `c1` int(10) unsigned NOT NULL DEFAULT '0',
  `c2` int(10) unsigned NOT NULL DEFAULT '0',
  `c3` int(10) unsigned NOT NULL DEFAULT '0',
  `c4` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`c1`),
  KEY `c2` (`c2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 

二、RR模式下的锁模式

我们先来看看下面两个语句的执行计划

mysql> desc  update t1 set c4=123 where c2>=8;
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref   | rows | filtered | Extra                        |
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+------------------------------+
|  1 | UPDATE      | t1    | NULL       | range | c2            | c2   | 4       | const |    2 |   100.00 | Using where; Using temporary |
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+------------------------------+ 
mysql> desc  update t1 set c4=123 where c2>=6;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+
|  1 | UPDATE      | t1    | NULL       | index | c2            | PRIMARY | 4       | NULL |    7 |   100.00 | Using where; Using temporary |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+ 

下面两个语句的执行计划不一致,主要注意
type:index和range
key:PRIMARY和c2

我们先要清楚type:index和range的区别
这里借用我以前写的一篇文章
http://blog.itpub.net/7728585/viewspace-2139010/

  1. type:index 不使用索引B+树结构,只使用索引叶子结点链表结构进行扫描,我们知道在索引的叶子结点有一个叶子结点之间的双向指针,
    并且叶子结点的数据是排序好的。他和ALL的方式类似,访问效率并不高,其主要的应用场景为用于避免order by使用using filesort
    也就是避免排序。他是一种访问数据的方式,和range、const、ref、eq_ref等一样。
  2. type:range 显然用于范围查询比如> between 等,其访问方式是考虑到索引的B+树结构的,需要通过根结点-->分支节点-->叶子结点的顺序访问
    其实const、ref、eq_ref等一样也需要这样的定位过程。

我大概画一个图,示意图而已,但是足以解释我的意思

1.jpg
1.jpg

剩下我们需要考虑RR模式下,如下语句有哪些所结构:

mysql> desc  update t1 set c4=123 where c2>=6;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+
|  1 | UPDATE      | t1    | NULL       | index | c2            | PRIMARY | 4       | NULL |    7 |   100.00 | Using where; Using temporary |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+ 
RECORD LOCKS space id 532 page no 3 n bits 80 index PRIMARY of table `test`.`t1` trx id 348084 lock_mode X(LOCK_X) 
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 00000000; asc     ;;
 1: len 6; hex 000000054abd; asc     J ;;
 2: len 7; hex ba00000e180110; asc        ;;
 3: len 4; hex 00000000; asc     ;;
 4: len 4; hex 00000000; asc     ;;
 5: len 4; hex 00000000; asc     ;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 00000001; asc     ;;
 1: len 6; hex 000000054abd; asc     J ;;
 2: len 7; hex ba00000e18011d; asc        ;;
 3: len 4; hex 00000001; asc     ;;
 4: len 4; hex 00000001; asc     ;;
 5: len 4; hex 00000000; asc     ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 00000003; asc     ;;
 1: len 6; hex 000000054abd; asc     J ;;
 2: len 7; hex ba00000e18012a; asc       *;;
 3: len 4; hex 00000003; asc     ;;
 4: len 4; hex 00000003; asc     ;;
 5: len 4; hex 00000000; asc     ;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 00000004; asc     ;;
 1: len 6; hex 000000054abd; asc     J ;;
 2: len 7; hex ba00000e180137; asc       7;;
 3: len 4; hex 00000002; asc     ;;
 4: len 4; hex 00000002; asc     ;;
 5: len 4; hex 00000000; asc     ;;
Record lock, heap no 6 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 00000006; asc     ;;
 1: len 6; hex 000000054fb4; asc     O ;;
 2: len 7; hex 3300000c430b49; asc 3   C I;;
 3: len 4; hex 00000008; asc     ;;
 4: len 4; hex 00000005; asc     ;;
 5: len 4; hex 0000007b; asc    {;;
Record lock, heap no 7 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 00000007; asc     ;;
 1: len 6; hex 000000054fb4; asc     O ;;
 2: len 7; hex 3300000c430b6b; asc 3   C k;;
 3: len 4; hex 00000006; asc     ;;
 4: len 4; hex 00000006; asc     ;;
 5: len 4; hex 0000007b; asc    {;;
Record lock, heap no 8 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 0000000a; asc     ;;
 1: len 6; hex 000000054fb4; asc     O ;;
 2: len 7; hex 3300000c430b8d; asc 3   C  ;;
 3: len 4; hex 0000000a; asc     ;;
 4: len 4; hex 00000004; asc     ;;
 5: len 4; hex 0000007b; asc    {;; 

我们这里先不考虑表级意向锁,只考虑这里打印出来的锁结构
行锁为:lock_mode X(LOCK_X)|LOCK_ORDINARY(next key lock)
同时我们注意到 0: len 8; hex 73757072656d756d; asc supremum
那么我们用一张图来表示

2.jpg
2.jpg

实际上我们从图中可以看出这种情况下RR模式下是主键上所有的行都加上了NEXT_KEY LOCK,所以你其他任何DML操作都会锁定

那么如下语句的锁结构呢?

mysql> desc  update t1 set c4=123 where c2>=8;
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref   | rows | filtered | Extra                        |
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+------------------------------+
|  1 | UPDATE      | t1    | NULL       | range | c2            | c2   | 4       | const |    2 |   100.00 | Using where; Using temporary |
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+------------------------------+
1 row in set (0.01 sec) 

如下:

-----TRX NO:348661 LOCK STRUCT(1)(Add by gaopeng)
TABLE LOCK table `test`.`t1` trx id 348661 lock mode IX
-----TRX NO:348661 LOCK STRUCT(1)(Add by gaopeng)
RECORD LOCKS space id 532 page no 4 n bits 80 index c2 of table `test`.`t1` trx id 348661 lock_mode X(LOCK_X)
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 00000008; asc     ;;
 1: len 4; hex 00000006; asc     ;;
Record lock, heap no 8 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 0000000a; asc     ;;
 1: len 4; hex 0000000a; asc     ;;

-----TRX NO:348661 LOCK STRUCT(1)(Add by gaopeng)
RECORD LOCKS space id 532 page no 3 n bits 80 index PRIMARY of table `test`.`t1` trx id 348661 lock_mode X(LOCK_X) locks rec but not gap(LOCK_REC_NOT_GAP)
Record lock, heap no 6 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 00000006; asc     ;;
 1: len 6; hex 0000000551f5; asc     Q ;;
 2: len 7; hex 71000002700ad1; asc q   p  ;;
 3: len 4; hex 00000008; asc     ;;
 4: len 4; hex 00000005; asc     ;;
 5: len 4; hex 0000007b; asc    {;;
Record lock, heap no 8 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 0000000a; asc     ;;
 1: len 6; hex 0000000551f5; asc     Q ;;
 2: len 7; hex 71000002700af3; asc q   p  ;;
 3: len 4; hex 0000000a; asc     ;;
 4: len 4; hex 00000004; asc     ;;
 5: len 4; hex 0000007b; asc    {;; 

我们可以清晰的观察到INDEX c2上包含
lock_mode X(LOCK_X)|LOCK_ORDINARY(next key lock)
其行包含了 C2:8/C1:6 C2:10/C2:10 还包含 supremum
同时传递到了主键PRIMARY锁结构为
lock_mode X(LOCK_X)|rec but not gap(LOCK_REC_NOT_GAP)
也就是主键上只是锁定了C1:6 C1:10这两行,并且不是gap lock,如果需要画图就是如下:


3.jpg
3.jpg

我们可以发现锁定的范围小了很多很多,这种情况如下语句:
select * from t1 where c1 = 7 for update;
(这里叶老师写的c2=7不知道是不是写错了)
是可以完成的,因为不会落到PRIMARY的锁定范围内。

三、RC模式下的锁定模式

这里只是看看RC模式的锁定结构如下:

mysql> desc  update t1 set c4=123 where c2>=6;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+
|  1 | UPDATE      | t1    | NULL       | index | c2            | PRIMARY | 4       | NULL |    7 |   100.00 | Using where; Using temporary |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+
1 row in set (0.22 sec) 
-----TRX NO:348596 LOCK STRUCT(1)(Add by gaopeng)
RECORD LOCKS space id 532 page no 3 n bits 80 index PRIMARY of table `test`.`t1` trx id 348596 lock_mode X(LOCK_X) locks rec but not gap(LOCK_REC_NOT_GAP)
Record lock, heap no 6 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 00000006; asc     ;;
 1: len 6; hex 0000000551b4; asc     Q ;;
 2: len 7; hex 3300000c430c03; asc 3   C  ;;
 3: len 4; hex 00000008; asc     ;;
 4: len 4; hex 00000005; asc     ;;
 5: len 4; hex 0000007b; asc    {;;
Record lock, heap no 7 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 00000007; asc     ;;
 1: len 6; hex 0000000551b4; asc     Q ;;
 2: len 7; hex 3300000c430c25; asc 3   C %;;
 3: len 4; hex 00000006; asc     ;;
 4: len 4; hex 00000006; asc     ;;
 5: len 4; hex 0000007b; asc    {;;
Record lock, heap no 8 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 0000000a; asc     ;;
 1: len 6; hex 0000000551b4; asc     Q ;;
 2: len 7; hex 3300000c430c47; asc 3   C G;;
 3: len 4; hex 0000000a; asc     ;;
 4: len 4; hex 00000004; asc     ;;
 5: len 4; hex 0000007b; asc    {;; 

我们可以清晰的看到RC模式下不考虑隐含锁的情况下只是锁定了PRIMARY的相应的行:
lock_mode X(LOCK_X) locks|rec but not gap(LOCK_REC_NOT_GAP)
注意这里NOT GAP

四、总结

就如叶老师所说,执行计划居然影响了锁的范围,听起来不可能但是他确实就是,所以大家RR模式下应该注意这一点,
同时这里RC模式下

 update t1 set c4=123 where c2>=6; 

不堵塞实际上是SEMI UPDATE的功劳,如果RR模式下开启参数innodb_locks_unsafe_for_binlog也不会堵塞
但是至少大家应该如叶老师所说的那么去做,特别是RR模式下。
这里再次感谢叶金荣老师给我们带来这么有趣的案例。

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
相关文章
|
8天前
|
弹性计算 关系型数据库 微服务
基于 Docker 与 Kubernetes(K3s)的微服务:阿里云生产环境扩容实践
在微服务架构中,如何实现“稳定扩容”与“成本可控”是企业面临的核心挑战。本文结合 Python FastAPI 微服务实战,详解如何基于阿里云基础设施,利用 Docker 封装服务、K3s 实现容器编排,构建生产级微服务架构。内容涵盖容器构建、集群部署、自动扩缩容、可观测性等关键环节,适配阿里云资源特性与服务生态,助力企业打造低成本、高可靠、易扩展的微服务解决方案。
1196 4
|
7天前
|
机器学习/深度学习 人工智能 前端开发
通义DeepResearch全面开源!同步分享可落地的高阶Agent构建方法论
通义研究团队开源发布通义 DeepResearch —— 首个在性能上可与 OpenAI DeepResearch 相媲美、并在多项权威基准测试中取得领先表现的全开源 Web Agent。
1111 87
|
6天前
|
机器学习/深度学习 物联网
Wan2.2再次开源数字人:Animate-14B!一键实现电影角色替换和动作驱动
今天,通义万相的视频生成模型又又又开源了!Wan2.2系列模型家族新增数字人成员Wan2.2-Animate-14B。
566 11
|
18天前
|
人工智能 运维 安全
|
8天前
|
云栖大会
阿里云云栖大会2025年9月24日开启,免费申请大会门票,速度领取~
2025云栖大会将于9月24-26日举行,官网免费预约畅享票,审核后短信通知,持证件入场
1675 12
|
1天前
|
资源调度
除了nrm-pm,还有哪些工具可以管理多个包管理器的源?
除了nrm-pm,还有哪些工具可以管理多个包管理器的源?
212 127
|
8天前
|
弹性计算 Kubernetes jenkins
如何在 ECS/EKS 集群中有效使用 Jenkins
本文探讨了如何将 Jenkins 与 AWS ECS 和 EKS 集群集成,以构建高效、灵活且具备自动扩缩容能力的 CI/CD 流水线,提升软件交付效率并优化资源成本。
344 0
|
8天前
|
消息中间件 Java Apache
SpringBoot集成RocketMq
RocketMQ 是一款开源的分布式消息中间件,采用纯 Java 编写,支持事务消息、顺序消息、批量消息、定时消息及消息回溯等功能。其优势包括去除对 ZooKeeper 的依赖、支持异步和同步刷盘、高吞吐量及消息过滤等特性。RocketMQ 具备高可用性和高可靠性,适用于大规模分布式系统,能有效保障消息传输的一致性和顺序性。
490 2

热门文章

最新文章