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

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

一、前言

叶金荣老师分享了一篇文章如下:
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

剩下我们需要考虑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

实际上我们从图中可以看出这种情况下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

我们可以发现锁定的范围小了很多很多，这种情况如下语句：
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模式下。
这里再次感谢叶金荣老师给我们带来这么有趣的案例。

时间： 2024-09-20 00:16:55

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

RR模式下NEXT-KEY LOCK范围到底有多大

我们知道MYSQL NEXT-KEY LOCK是用来防止幻读,在RR模式下就有了用武之地 实际就是当前行锁+前后的一个区间,但是这个区间到底有多大? 是简单的一个辅助索引列上的闭区间吗? 测试全部是在RR模式下RC模式不存在 建立测试表: CREATE TABLE `test` (   `a` int(11) NOT NULL DEFAULT '0',   `b` int(11) DEFAULT NULL,   PRIMARY KEY (`a`),   KEY `b` (`b`) ) ENGIN

微软Edge被曝储存隐私模式下的浏览数据

按照微软的计划,Edge在Windows 10的部署应当算是公司发展迈出的重要一步,不过新的研究结果可能会让这个计划变得不那么顺利.据悉,Edge的InPrivate(隐私)可能并没有看起来那么隐私.根据Ashish Singh的研究了解到,InPrivate下的访问数据可以通过WebCache文件在用户硬盘中进行恢复. 被访问网站数据被储存在跟传统浏览历史记录一样的"Container_n"表格中.无论是否处在隐私模式下,攻击者都可通过检索这份表格重建用户的整个浏览历史记录. 外媒T

MYSQL RC模式insert update 可能死锁的情况

涉及的语句为 RC模式下 update根据主键更新和insert 其实这样的问题在RC模式下,要么是简单update问题,要么是insert造成的主键和唯一键检查唯一性时出现问题. 下面以主键问题为列子进行分析一下可能出现的情况. update  where条件更新为主键,锁结构出现在单行主键上,辅助索引包含隐含锁结构,当前读RC非唯一索引模式没有GAP锁, insert  插入印象锁,主键和辅助索引上会出现隐含锁结构, 但是在RC模式下没有GAP所以插入印象锁一般不会成为问题 表结构: +--

养成一个好习惯:在严格模式下开发Javascript

前言 坦白说对于Javascript所谓的严格模式在今年早些时候我还一无所知,真正在开发中应用也就几个月.对于这个ECMAScript5就已经引入的东西,已经不能算新了,对于这个东西一直处在字面的理解上,就是"更加严格,规范的限制Javscript代码的书写",今天打算结合使用的经验好好研究下它到底是怎么回事. 正文 Javascript中如果你想你的代码在严格模式下运行,非常简单,只要在代码的第一行,也就是首行加上"use strict"指令即可.对于那些不支持E

软件定义IT已成为数据中心下一个发展模式

虚拟化在数据中心的应用意味着软件定义计算逐渐成为主流,而在软件定义计算的成功摸索下,软件定义存储也迅速获得市场的认可,虽然SDS的商业解决方案还处于刚刚起步的阶段,但其大大提高了存储丹云的灵活性和可用性,这意味着存储单元在私有云和公有云数据中心中可以自动和动态的分配. 迈向下一个数据中心模式?很明显的是,软件定义计算正在数据中心中快速的增长,例如,如今在数据中心中增长非常快的软件定义网络,能够提高灵活性和配置网络虚拟化. 根据知名调研机构IDC表示,2014年至2018年,企业和云数据中心领域的

Oracle IMU模式下REDO格式详解

1. 什么是IMU?IMU的主要作用是什么,也就是说为了解决什么问题? IMU--->In Memory Undo,10g新特性,数据库会在shared pool开辟独立的内存区域用于存储Undo信息, 每个新事务都会分配一个IMU buffer(私有的),一个buffer里有很多node,一个node相当于一个block(回滚块). IMU特性: IMU顾名思义就是在内存中的undo,现在每次更改data block,Oracle 不用去更改这个undo block(也不会生成相应的redo了

MongoDB学习笔记(三) 在MVC模式下通过Jqgrid表格操作MongoDB数据

下面我们将逐步讲解怎么在MVC模式下将MongoDB数据读取,并展示在前台Jqgrid表格上.这个"简易系 统"的基本设计思想是这样的:我们在视图层展示表格,Jqgrid相关Js逻辑全部放在一个Js文件中,控制层实现了"增删查改"四个业 务,MongoDB的基本数据访问放在了模型层实现   看到下图,是通过Jqgrid实现表格数据的基本增删查改的操作.表格数据增删改是一般企业应用系统开发的常见功能,不过不同的是这个表格数据来源是非关 系型的数据库MongoDB.no

【MySQL】可重复读模式下 unique key失效案例

一 [背景]    今天上午文能提笔安天下,武能上马定乾坤的登博给团队出了一道题目,谁先复现问题,奖励星巴克一杯.激起了一群忙碌的屌丝DBA的极大热情.问题是这样滴,如下图登博提示了几个细节:   1. code上的uk并未失效.   2. rr隔离级别.   3. 有并发线程的操作.二 [原理分析]1 事务隔离级别的基础知识:  未提交读(Read Uncommitted):允许脏读,也就是可能读取到其他会话中未提交事务修改的数据.  提交读(Read Committed):只能读取到已经提交

命令行模式下备份、还原 MySQL 数据库的语句小结_Mysql

为了安全起见,需要经常对数据库作备份,或者还原.对于 MySQL 而言,最方便的方法可能就是用 phpMyAdmin 的导出.导入功能了,但如果你的数据库体积比较大,作为 Web 应用的 phpMyAdmin 可能会遭遇"超时"而操作失败.所以,学会在命令行模式下备份.还原数据库,还是很有必要的. 1.备份数据库 在 Linux 命令行模式下备份 MySQL 数据库,用的是 mysqldump 命令: 复制代码 代码如下: mysqldump -u mysqluser -p test_