mysql中一个RR模式下UPDATE锁范围扩大案例分析

本篇内容介绍了“mysql中一个RR模式下UPDATE锁范围扩大案例分析”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

一、前言

这里只研究下锁的模式,借用叶老师的表和语句

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-/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=123where 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 |+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+------------------------------+1rowin 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

“mysql中一个RR模式下UPDATE锁范围扩大案例分析”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注网站，小编将为大家输出更多高质量的实用文章！