MySQL 30 用动态的观点看加锁

技术分享 7个月前 (09-01) 0 999+

关注

首先复习一下加锁规则：

• 原则1：加锁的基本单位是next-key lock，是一个前开后闭区间；

• 原则2：查找过程中访问到的对象才会加锁；

• 优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁；

• 优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁；

• 一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

接下来的讨论基于下表t：

CREATE TABLE `t` (   `id` int(11) NOT NULL,   `c` int(11) DEFAULT NULL,   `d` int(11) DEFAULT NULL,   PRIMARY KEY (`id`),   KEY `c` (`c`) ) ENGINE=InnoDB;  insert into t values(0,0,0),(5,5,5), (10,10,10),(15,15,15),(20,20,20),(25,25,25); 

不等号条件里的等值查询

等值查询和遍历有什么区别？为什么当where条件是不等号，这个过程也有等值查询？

begin; select * from t where id>9 and id<12 order by id desc for update; 

利用加锁规则，这个语句的加锁范围是主键索引上的(0,5]、(5,10]、(10,15)。id=15没有加上行锁是因为用到了优化2，退化为了间隙锁。

但是查询语句里where条件不是等号，这里的等值查询又是从哪来的呢？

分析索引id的示意图：

• 由于语义是order by id desc，要拿到满足条件的所有行，优化器必须先找到第一个id<12的值；

• 该过程需要搜索索引树找到id=12的值，但最终没找到，只找到(10,15)的间隙；

• 然后向左遍历，该遍历过程不是等值查询，会扫描到id=5这一行，会加一个(0,5]。

也就是说，在执行过程中，通过树搜索方式定位记录时用的是等值查询的方法。

等值查询的过程

下面这个语句的加锁范围是什么呢？

begin; select id from t where c in(5,20,10) lock in share mode; 

先看语句的explain结果：

该语句使用了索引c并且rows=3，说明这三个值都是通过B+树搜索定位的。

在查找c=5时，先锁住了(0,5]，但因为c不是唯一索引，为了确认还有没有其他c=5的记录，需要向右遍历，直到c=10才确认没有，该过程满足优化2，所以加间隙锁(5,10)。

同样的，执行c=10 的时候，加锁的范围是(5,10]和(10,15)；执行c=20的时候，加锁的范围是(15,20]和(20,25)。

这些锁是在执行过程中一个一个加的，而不是一次性加上去的。

假设同时有另外一个语句：

select id from t where c in(5,20,10) order by c desc for update; 

间隙锁不互锁，但这两条语句都会在索引c上的c=5、10、20三行记录上加记录锁。

由于两条语句要加锁相同的资源，但加锁顺序相反，当这两条语句并发执行的时候，就可能出现死锁。

关于死锁的信息，MySQL只保留了最后一个死锁的现场，但这个现场还是不完备的。接下来就分析上面例子的死锁现场。

怎么看死锁？

出现死锁后，执行show engine innodb status命令能输出很多信息，其中有一节LATESTDETECTED DEADLOCK，就是记录的最后一次死锁信息。

该结果分为三部分：

• (1) TRANSACTION：是第一个事务的信息；

• (2) TRANSACTION：是第二个事务的信息；

• WE ROLL BACK TRANSACTION (1)：是最终处理结果，表示回滚了第一个事务。

第一个事务的信息中：

• WAITING FOR THIS LOCK TO BE GRANTED：表示这个事务在等待的锁信息；

• index c of table test.t：说明在等的是表t的索引c上面的锁；

• lock mode S waiting：表示这个语句要自己加一个读锁，当前的状态是等待中；

• Record lock：说明这是一个记录锁；

• n_fields 2：表示这个记录是两列，也就是字段c和主键字段id；

• 0: len 4; hex 0000000a; asc ;;：是第一个字段c。值是十六进制a，也就是10；

• 1: len 4; hex 0000000a; asc ;;：是第二个字段，也就是主键id，值也是10；

• 这两行里面的asc表示的是，接下来要打印出值里面的“可打印字符”，但10不是可打印字符，因此就显示空格；

• 第一个事务信息就只显示出了等锁的状态，在等待(c=10,id=10)这一行的锁；

第二个事务的信息中：

• “ HOLDS THE LOCK(S)”用来显示这个事务持有哪些锁；

• index c of table test.t 表示锁是在表t的索引c上；

• hex 0000000a和hex 00000014表示这个事务持有c=10和c=20这两个记录锁；

• WAITING FOR THIS LOCK TO BE GRANTED，表示在等(c=5,id=5)这个记录锁。

从上面这些信息中能知道：

• lock in share mode这条语句，持有c=5的记录锁，在等c=10的锁；

• for update这个语句，持有c=20和c=10的记录锁，在等c=5的记录锁。

因此导致死锁，由此得到结论：

• 由于锁是一个个加的，要避免死锁，对同一组资源要按照尽量相同的顺序访问；

• 在发生死锁的时刻，for update语句占用的资源更多，回滚成本更大，因此InnoDB选择了回滚成本更小的lock in share mode语句来回滚。

怎么看锁等待？

看完死锁，再看一个锁等待的例子。