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

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

mysql>select*fromt1;+----+----+----+----+|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|+----+----+----+----+

CREATETABLE`t1`(`c1`int(10)unsignedNOTNULLDEFAULT'0',`c2`int(10)unsignedNOTNULLDEFAULT'0',`c3`int(10)unsignedNOTNULLDEFAULT'0',`c4`int(10)unsignedNOTNULLDEFAULT'0',PRIMARYKEY(`c1`),KEY`c2`(`c2`))ENGINE=InnoDBDEFAULTCHARSET=utf8二、RR模式下的锁模式

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

mysql>descupdatet1setc4=123wherec2>=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|Usingwhere;Usingtemporary|+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+------------------------------+

mysql>descupdatet1setc4=123wherec2>=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|Usingwhere;Usingtemporary|+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+

下面两个语句的执行计划不一致，主要注意
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>descupdatet1setc4=123wherec2>=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|Usingwhere;Usingtemporary|+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+

RECORDLOCKSspaceid532pageno3nbits80indexPRIMARYoftable`test`.`t1`trxid348084lock_modeX(LOCK_X)Recordlock,heapno1PHYSICALRECORD:n_fields1;compactformat;infobits00:len8;hex73757072656d756d;ascsupremum;;Recordlock,heapno2PHYSICALRECORD:n_fields6;compactformat;infobits00:len4;hex00000000;asc;;1:len6;hex000000054abd;ascJ;;2:len7;hexba00000e180110;asc;;3:len4;hex00000000;asc;;4:len4;hex00000000;asc;;5:len4;hex00000000;asc;;Recordlock,heapno3PHYSICALRECORD:n_fields6;compactformat;infobits00:len4;hex00000001;asc;;1:len6;hex000000054abd;ascJ;;2:len7;hexba00000e18011d;asc;;3:len4;hex00000001;asc;;4:len4;hex00000001;asc;;5:len4;hex00000000;asc;;Recordlock,heapno4PHYSICALRECORD:n_fields6;compactformat;infobits00:len4;hex00000003;asc;;1:len6;hex000000054abd;ascJ;;2:len7;hexba00000e18012a;asc*;;3:len4;hex00000003;asc;;4:len4;hex00000003;asc;;5:len4;hex00000000;asc;;Recordlock,heapno5PHYSICALRECORD:n_fields6;compactformat;infobits00:len4;hex00000004;asc;;1:len6;hex000000054abd;ascJ;;2:len7;hexba00000e180137;asc7;;3:len4;hex00000002;asc;;4:len4;hex00000002;asc;;5:len4;hex00000000;asc;;Recordlock,heapno6PHYSICALRECORD:n_fields6;compactformat;infobits00:len4;hex00000006;asc;;1:len6;hex000000054fb4;ascO;;2:len7;hex3300000c430b49;asc3CI;;3:len4;hex00000008;asc;;4:len4;hex00000005;asc;;5:len4;hex0000007b;asc{;;Recordlock,heapno7PHYSICALRECORD:n_fields6;compactformat;infobits00:len4;hex00000007;asc;;1:len6;hex000000054fb4;ascO;;2:len7;hex3300000c430b6b;asc3Ck;;3:len4;hex00000006;asc;;4:len4;hex00000006;asc;;5:len4;hex0000007b;asc{;;Recordlock,heapno8PHYSICALRECORD:n_fields6;compactformat;infobits00:len4;hex0000000a;asc;;1:len6;hex000000054fb4;ascO;;2:len7;hex3300000c430b8d;asc3C;;3:len4;hex0000000a;asc;;4:len4;hex00000004;asc;;5:len4;hex0000007b;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>descupdatet1setc4=123wherec2>=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|Usingwhere;Usingtemporary|+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+------------------------------+1rowinset(0.01sec)

如下：

-----TRXNO:348661LOCKSTRUCT(1)(Addbygaopeng)TABLELOCKtable`test`.`t1`trxid348661lockmodeIX-----TRXNO:348661LOCKSTRUCT(1)(Addbygaopeng)RECORDLOCKSspaceid532pageno4nbits80indexc2oftable`test`.`t1`trxid348661lock_modeX(LOCK_X)Recordlock,heapno1PHYSICALRECORD:n_fields1;compactformat;infobits00:len8;hex73757072656d756d;ascsupremum;;Recordlock,heapno6PHYSICALRECORD:n_fields2;compactformat;infobits00:len4;hex00000008;asc;;1:len4;hex00000006;asc;;Recordlock,heapno8PHYSICALRECORD:n_fields2;compactformat;infobits00:len4;hex0000000a;asc;;1:len4;hex0000000a;asc;;-----TRXNO:348661LOCKSTRUCT(1)(Addbygaopeng)RECORDLOCKSspaceid532pageno3nbits80indexPRIMARYoftable`test`.`t1`trxid348661lock_modeX(LOCK_X)locksrecbutnotgap(LOCK_REC_NOT_GAP)Recordlock,heapno6PHYSICALRECORD:n_fields6;compactformat;infobits00:len4;hex00000006;asc;;1:len6;hex0000000551f5;ascQ;;2:len7;hex71000002700ad1;ascqp;;3:len4;hex00000008;asc;;4:len4;hex00000005;asc;;5:len4;hex0000007b;asc{;;Recordlock,heapno8PHYSICALRECORD:n_fields6;compactformat;infobits00:len4;hex0000000a;asc;;1:len6;hex0000000551f5;ascQ;;2:len7;hex71000002700af3;ascqp;;3:len4;hex0000000a;asc;;4:len4;hex00000004;asc;;5:len4;hex0000007b;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模式的锁定结构如下：

mysql>descupdatet1setc4=123wherec2>=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|Usingwhere;Usingtemporary|+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+1rowinset(0.22sec)

-----TRXNO:348596LOCKSTRUCT(1)(Addbygaopeng)RECORDLOCKSspaceid532pageno3nbits80indexPRIMARYoftable`test`.`t1`trxid348596lock_modeX(LOCK_X)locksrecbutnotgap(LOCK_REC_NOT_GAP)Recordlock,heapno6PHYSICALRECORD:n_fields6;compactformat;infobits00:len4;hex00000006;asc;;1:len6;hex0000000551b4;ascQ;;2:len7;hex3300000c430c03;asc3C;;3:len4;hex00000008;asc;;4:len4;hex00000005;asc;;5:len4;hex0000007b;asc{;;Recordlock,heapno7PHYSICALRECORD:n_fields6;compactformat;infobits00:len4;hex00000007;asc;;1:len6;hex0000000551b4;ascQ;;2:len7;hex3300000c430c25;asc3C%;;3:len4;hex00000006;asc;;4:len4;hex00000006;asc;;5:len4;hex0000007b;asc{;;Recordlock,heapno8PHYSICALRECORD:n_fields6;compactformat;infobits00:len4;hex0000000a;asc;;1:len6;hex0000000551b4;ascQ;;2:len7;hex3300000c430c47;asc3CG;;3:len4;hex0000000a;asc;;4:len4;hex00000004;asc;;5:len4;hex0000007b;asc{;;

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

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