共计 10014 个字符，预计需要花费 26 分钟才能阅读完成。
本篇内容介绍了“mysql 中一个 RR 模式下 UPDATE 锁范围扩大案例分析”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让丸趣 TV 小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！
一、前言
这里只研究下锁的模式, 借用叶老师的表和语句
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
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+------------------------------+
| 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
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+
| 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/
type:index 不使用索引 B + 树结构, 只使用索引叶子结点链表结构进行扫描，我们知道在索引的叶子结点有一个叶子结点之间的双向指针，
并且叶子结点的数据是排序好的。他和 ALL 的方式类似，访问效率并不高，其主要的应用场景为用于避免 order by 使用 using filesort
也就是避免排序。他是一种访问数据的方式，和 range、const、ref、eq_ref 等一样。
type:range 显然用于范围查询比如 between 等，其访问方式是考虑到索引的 B + 树结构的，需要通过根结点 – 分支节点 – 叶子结点的顺序访问
其实 const、ref、eq_ref 等一样也需要这样的定位过程。
我大概画一个图，示意图而已，但是足以解释我的意思
1.jpg
剩下我们需要考虑 RR 模式下，如下语句有哪些所结构：
mysql desc update t1 set c4=123 where c2
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+
| 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
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+------------------------------+
| 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
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+
| 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 |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+-----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
“mysql 中一个 RR 模式下 UPDATE 锁范围扩大案例分析”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注丸趣 TV 网站，丸趣 TV 小编将为大家输出更多高质量的实用文章！
正文完
