原创转载请注明出处有误请指出
一、前言
叶金荣老师分享了一篇文章如下:
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/
- type:index 不使用索引B+树结构,只使用索引叶子结点链表结构进行扫描,我们知道在索引的叶子结点有一个叶子结点之间的双向指针,
并且叶子结点的数据是排序好的。他和ALL的方式类似,访问效率并不高,其主要的应用场景为用于避免order by使用using filesort
也就是避免排序。他是一种访问数据的方式,和range、const、ref、eq_ref等一样。 - type:range 显然用于范围查询比如> between 等,其访问方式是考虑到索引的B+树结构的,需要通过根结点-->分支节点-->叶子结点的顺序访问
其实const、ref、eq_ref等一样也需要这样的定位过程。
我大概画一个图,示意图而已,但是足以解释我的意思
![1.jpg](https://i-blog.csdnimg.cn/blog_migrate/4ee12661bbf2bf47b77194425b231119.webp?x-image-process=image/format,png)
剩下我们需要考虑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](https://i-blog.csdnimg.cn/blog_migrate/605cfb2196a74a22d4036fa9b68e5a3e.webp?x-image-process=image/format,png)
实际上我们从图中可以看出这种情况下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](https://i-blog.csdnimg.cn/blog_migrate/bff6fd9964922d83d627678bf1edefb3.webp?x-image-process=image/format,png)
我们可以发现锁定的范围小了很多很多,这种情况如下语句:
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模式下。
这里再次感谢叶金荣老师给我们带来这么有趣的案例。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7728585/viewspace-2144025/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7728585/viewspace-2144025/