mysql中RR模式UPDATE锁范围扩展案例分析

1.前言

这里只研究加锁方式，借用叶老师的表和语句

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 10 | 10 4 | 0 |+----+----+----+----+

创建表 `t1` ( `c1` int(10 ) unsigned NOT NULL DEFAULT '0' , `c2` int(10) 无符号非空默认值 '0', `c3` int(10) 无符号非空默认值'0', `c4` int(10) 无符号非空默认值'0', PRIMARY KEY (` c1`), KEY `c2` (`c2`)) 引擎=InnoDB 默认字符集=utf8

2. RR模式下锁定模式

我们先看一下下面两条语句的执行计划

mysql> desc update t1 set c4=123 where c2>=8;+----+- ------------ +--------+------------+--------+-------- -------+---- --+---------+--------+------+------------ +------------ ------------------+|编号 |选择类型 |表|隔断|类型 |可能的键 |关键| key_len |参考|行 |过滤|额外 - +----------------+------+-------------+-------+-------------- + ----------+--------------------------------+| 1 |更新 | t1 |空|范围 | c2 | c2 | 4 |常量 | 2 | 100.00 |使用地点；使用临时 |+----+-------------+--------+- -----------+-------- -+---------------+------+-------- --+--------+-----+ ----------+--------------------- ---------+

mysql > desc 更新 t1 设置 c4=123 其中 c2>=6;+----+------------ -+--------+-------- ----+--------+----------------+---------+---------+- ------+ ------+---------+---------------------------------+|编号 |选择类型 |表|隔断|类型 |可能的键 |关键| key_len |参考|行 |过滤|额外 | +----+-------------+------ -+----------------+----- --+----------------+---------+- --------+--------+---- --+----------+---------------- ---------------+| 1 |更新 | t1 |空|索引 | c2 |小学 | 4 |空 |7 | 100.00 |使用地点；使用临时 |+----+-------------+--------+------------ +-------- -+----------------+----------+------------+----- -+--- ---+--------------------+--------------------------------+ 

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

我们首先要了解type:index和range的区别
这里借用一个我之前写的文章
http://blog.itpub.net/7728585/viewspace-2139010/

type:index没有使用索引B+树结构只使用了索引叶子节点链表结构用于扫描。我们知道索引叶子节点中叶子节点之间存在一个双向指针，
并且叶子节点的数据是排序的。与ALL类似，访问效率不高。它的主要应用场景是通过使用using filesort
来避免排序，即避免排序。它是一种访问数据的方式，与range、const、ref、eq_ref等相同。

type:range显然是用于> Between等范围查询，它的访问方式考虑到了索引的B+树结构，需要经过根节点-->分支节点-->叶子节点。顺序访问
其实const、ref、eq_ref等也需要这样的定位过程。

我可能会画一个图，只是一个示意图，但足以解释我的意思

1.jpg< /p>

剩下的就是我们需要考虑RR模式下下面语句的结构：

mysql> desc update t1 set c4=123 where c2>=6;+--- ---+------ -------+-------+------------+--------+----------------+---------+- --------+------+------+----------+ ---------------- ------------------+|编号 |选择类型 |表|隔断|类型 |可能的键 |关键| key_len |参考|行 |过滤|额外 |+----+-------------+-----+------------+--- ----+- --------------+---------+---------+-----+-- ----+- ------+----------------------------------------+| 1 |更新 | t1 |空|索引 | c2 |小学 | 4 |空| 7|| 100.00 |使用地点；使用临时 |+----+-------------+--------+------------ +-------- -+----------------+----------+------------+----- -+--- ---+--------------------+--------------------------------+ 

记录锁空间 id 532 页号 3 n 位 80 表 `test`.`t1` 的主索引 trx id 348084 lock_mode X(LOCK_X) 记录锁，堆号 1 物理记录：n_fields 1；格式;信息位 0 0：长度 8；十六进制 73757072656d756d； asc supremum;;记录锁，堆编号 2 物理记录：n_fields 6;格式紧凑；信息位 0 0：长度 4；十六进制 00000000；紧凑上升 ;; 1：长度6；十六进制 000000054abd；上升 J ;; 2：长度7；十六进制 ba00000e180110；升序;; 3：长度4；十六进制 00000000；升序;; 4：长度4；十六进制 00000000；升序;; 5：长度4；十六进制 00 000000； asc ;;记录锁，堆编号 3 物理记录：n_fields 6;格式紧凑；信息位 0 0：长度 4；十六进制 00000001；升序;; 1：长度6；十六进制 000000054abd；上升 J ;; 2：长度7；十六进制 ba0 0000e18011d；上升; ; 3：长度4；十六进制 00000001；升序;; 4：长度4；十六进制 00000001;asc ;; 5：长度4；十六进制 00000000； asc ;;记录锁，堆编号 4 物理记录：n_fields 6;格式紧凑；信息位 0 0：长度 4；十六进制 00000003；升序;; 1：长度6；十六进制 000000054abd ；上升 J ;; 2：长度7；十六进制 ba00000e18012a；升序 *;; 3：长度4；十六进制 00000003；升序;; 4：长度4；十六进制 00000003；升序;; 5：长度4；十六进制 00000000；升序;;记录锁，堆编号 5 物理记录：n_fields 6；格式紧凑；信息位 0 0：len4；十六进制 00000004；升序;; 1：长度6；十六进制 000000054abd；上升 J ;; 2：长度7；十六进制 ba00000e180137；上升 7;; 3：长度4；十六进制 00000002；升序;; 4：长度4；十六进制 00000002 ；升序;; 5：长度4；十六进制 00000000；asc ;;记录锁，堆编号 6 物理记录：n_fields 6;格式紧凑；信息位 0 0：长度 4；十六进制 00000006；升序;; 1：长度6；十六进制 000000054fb4；上升 O ;; 2：长度7；十六进制 3300000c430b49；上升 3 C I;; 3：长度 4；十六进制 00000008；升序;; 4：长度4；十六进制 00000005；升序;; 5：长度4；十六进制 0000007b； asc {;;记录锁，堆号 7 物理记录：n_fields 6;紧凑行为格式；信息位 0 0：长度 4；十六进制 00000007；升序;; 1：长度6；十六进制 000000054fb4；上升 O ;; 2：长度7；十六进制 3300000c430b6b； asc 3 C k;; 3：长度4；十六进制 00000006；上升; ; 4：长度4；十六进制 00000006；升序;; 5：长度4；十六进制 0000007b； asc {;;记录锁，堆编号 8 物理记录：n_fields 6;格式紧凑；信息位 0 0：长度 4；十六进制 0000000a；升序;; 1：长度6；十六进制 000000054fb4；上升 O ;; 2：长度7；十六进制 3300000c430 b8d；上升 3 C ;; 3：长度4；十六进制 0000000a；升序;; 4：长度4；十六进制 00000004；升序;; 5：长度4；十六进制 0000007b； asc {;;

这里我们不考虑表级意向锁，只考虑这里打印的锁结构
行锁为：lock_mode 我们用一张图来表示

2.jpg

其实从图中我们可以看出，RR模式在这种情况下面就是在主键上的所有行上添加了NEXT_KEY LOCK，所以你执行的任何其他DML操作都将被锁定

那么下面语句的锁结构是什么呢？

mysql> desc update t1 set c4=123 where c2>=8;+----+------------+--------+ ----------- -+--------+----------------+--------+-------- ----+-------- +------+----------+-------------------- ----------------+ |编号 |选择类型 |表|隔断|类型 |可能的键 |关键| key_len |参考|行 |过滤|额外--+----------------+--------------------+----------------+----- ------------+--------+------+------------+------------ ----- -------------+| 1 |更新 | t1 |空|范围 | c2 | c2 | 4 | |常量 | 2 | 100.00 |使用地点；使用临时 |+----+-------------+--------+----------+-------- +---------------+------+--------+--- ----+------+- ---------+--------------------------- ---集合中+1行（0.01秒）

如下：

-----TRX NO:348661 LOCK STRUCT(1) (高鹏添加)TABLE LOCK table `test`.`t1` trx id 348661 锁定模式 IX----TRX NO:348661 LOCK STRUCT(1)(高鹏添加)RECORD LOCKS space id 532 page no 4 n bits表 `test` 的 80 索引 c2 .`t1` trx id 348661 lock_mode X(LOCK_X)记录锁，堆编号 1 物理记录：n_fields 1；格式紧凑；信息位 0 0:len 8;十六进制 73757072656d756d； asc supremum;;记录锁，堆编号 6 物理记录：n_fields 2;格式紧凑；信息位 0 0：长度 4；十六进制 00000008；升序;; 1：长度4； h 前 00000006；升序;;记录锁，堆编号 8 物理记录：n_fields 2；格式紧凑；信息位 0 0：长度 4；十六进制 0000000a；升序;; 1：长度4；十六进制 0000000a； asc ;;-----TRX NO:348661 LOCK STRUCT(1)(由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) 锁定rec但不锁定gap(LOCK_REC_NOT_GAP)记录锁，堆编号为6 物理记录：n_fields 6；紧凑型f奥马特；信息位 0 0：长度 4；十六进制 00000006；升序;; 1：长度6；十六进制 000000 0551f5；上升 Q； ; 2：长度7；十六进制 71000002700ad1；上升 q p ;; 3：长度4；十六进制 00000008；升序;; 4：长度4；十六进制 00000005；升序;; 5：长度4；十六进制 0000007b ； asc {;;记录锁，堆编号 8 物理记录：n_fields 6;格式紧凑；信息位 0 0：长度 4；十六进制 0000000a；上升 ;; 1：长度6；十六进制 0000000551f5；上升 Q ;; 2：长度7；十六进制 71000002700af3；上升 q p ;; 3：长度4；十六进制 0000000a；升序;; 4：长度4；十六进制 00000004；上升; ; 5：长度4；十六进制 0000007b； asc {;;

我们可以清楚地观察到INDEX c2包含
lock_mode，其行包含C2:8/C1:6 C2:10/C2:10，还包含supremum
At同时，主键PRIMARY锁结构被传递给
lock_mode X(LOCK_X)|rec但不传递gap(LOCK_REC_NOT_GAP)
即只锁定C1:6 C1:10两行主键，并且它不是间隙锁。如果需要画图的话，如下：

3.jpg

我们可以发现锁定范围是小得多。本例中如下语句为：
select * from t1 where c1 = 7 for update;
（不知道叶老师这里写的c2=7有没有错）
可以完成，因为不会落入PRIMARY的锁定范围。

3. RC模式下的加锁方式

这里我们只看一下RC模式的加锁结构如下：

mysql> desc update t1 set c4=123 where c2>=6;+- ---+------------+--------+------------ --+--------+--- ------------+----------+----------+--- ---+------+- ---------+---------------------------------------- --+|编号 |选择类型 |表|隔断|类型 |可能的键 |关键| key_len |参考|行 |过滤|额外 |+----+-------------+-- -----+----------------+--- ----+----------------+-------- --+--------+--------+-- ---+----------+------------ ------------------+| 1 |更新 | t1 |空|索引 | c2 |小学 | 4 |空| 7 | 100.00 |使用地点；使用临时 |+----+-------------+--------+---- --------+-------- -+----------------+----------+-------- --+------+--------+------------+-------- --------------- --------+1 行集合（0.22 秒）

-----TRX NO:348596 LOCK STRUCT (1)(高朋添加)RECORD LOCKS space id 532 page no 3 n bits 80 index PRIMARY of table `test`.`t1` trx id 348596 lock_mode 0 : len 4;十六进制 00000006；升序;; 1：长度6；十六进制 0000000551b4；上升 Q ;; 2：长度7；十六进制 3300000c430c03；上升 3 C ;; 3：长度4；十六进制 00000008；升序;; 4：长度4；十六进制 00000 005；升序;; 5：长度4；十六进制 0000007b； asc {;;记录锁，堆号 7 物理记录：n_fields 6;格式紧凑；信息位 0 0：长度 4；上升 Q ;; 2：长度7；十六进制 3300000c430c25；上升 3 C %;; 3：长度4；十六进制 00000006；升序;; 4：长度4；十六进制 00000006；升序;; 5：长度4；十六进制 0000007b； asc {;;记录锁，堆编号 8 物理记录：n_fields 6;格式紧凑；信息位 0 0：长度 4；十六进制 0000000a；升序;; 1：长度6；十六进制 0000000551b4；上升 Q ;; 2：长度7；十六进制 3300000c430c47；上升 3 C G;; 3：长度4；十六进制 0000000a；升序;; 4：长度4；十六进制 00 000004；上升; ; 5：长度4；十六进制 0000007b； asc LOCK_X) 锁|rec但不是gap(LOCK_REC_NOT_GAP)
这里注意NOT GAP
这里已经介绍了《MySQL RR模式下UPDATE锁范围扩展的案例分析》。感谢您的阅读。如果您想了解更多行业资讯，可以关注网站，小编将为大家输出更多优质实用文章！