从一个案例 复习MySQL加锁机制

一前言

该文源自于和一个DBA 同行 @邱神医 (集数据库技术和医学知识于一身的DBA)的技术讨论。

RC级别:

create table t1(id int primary key, name varchar(30));
insert into t1 values(1, 'a'),(4, 'c'),
(7, 'b'),(10, 'a'),(20, 'd'),(30, 'b');
commit;

案例一

--sess1           --sess2
begin;                              
delete from t1
where id = 10;
                 begin;
                 delete from t1 
                 where id < 9; ## 被阻塞

如果会话A,会话B的执行顺序调整一下，会话B 则不会被阻塞。

案例二

--sess1           --sess2
begin;                              
delete from t1
where id < 9;
                  begin;
                  delete from t1 
                  where id = 10;#不会阻塞

案例三 sess2 使用 delete from t1 where id < 9 order by id desc; 则没有阻塞。

--sess1          --sess2
begin;                              
delete from t1
where id = 10;
                 begin;
                 delete from t1 
                 where id < 9 order by id desc; ## 没有阻塞

为什么呢？

二 基础知识

sql被block住说明存在锁等待/锁冲突，等待其他会话释放锁。其实问题的核心在于 加锁顺序和加锁范围。这里结合 丁奇 《MySQL 实战45讲》中的讲述的 加锁方式(2个原则2个优化1个bug)

原则 1：加锁的基本单位是 next-key lock。 原则 2：查找过程中访问到的对象才会加锁。 优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。 优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。 一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

在读提交隔离级别下还有一个优化，即：语句执行过程中加上的行锁，在语句执行完成后，就要把“不满足条件的行”上的行锁直接释放了，不需要等到事务提交

三 案例分析

案例一

sess1 持有id = 10 该记录的行锁 lock_mode X locks rec but not gap

sess2 where 条件是 id<9 ，因为id=9 的记录不存在，根据原则 一个bug 故需要申请第一个不满足条件的记录id=10 的next-key ，也就是(7,10]，此时id=10 的行锁被sess1 持有，故sess2产生锁等待被block住。

案例二

sess1 先执行delete t1 where id<9 ，根据RC模式的加锁方式 ，虽然要获取到第一个不满足记录的id=10 的next-key lock ，但是因为id=10 不符合 id<9 的条件，会释放锁，最终降级为 (7,10) 之间的gap lock。

再解释一下:这个过滤操作是 MySQL Sever层做的，也就是 innodb层把包括id=10的记录加锁然后发送给MySQL Server层，然后 MySQL Sever层判断是否where条件可以结束了，可以结束了则将不满足条件的id=10解锁。

sess2 delete where id=10 和 sess1 持有的gap lock不冲突，故可以顺利执行。

案例三

sess1 持有id = 10 该记录的行锁 lock_mode X locks rec but not gap

sess2 where 条件是 id<9 order by id desc ，通过innodb api接口访问数据的时候从获取到第一个满足条件的记录是id=7，不会访问 id=10这条记录。故也不会加上id=10 的next-key lock. 不会与sess1持有的锁冲突。

小结

经过这个几个案例又复习了一次 MySQL的 加锁机制，同时 也墙裂 安利 丁奇的课程，不管是新人还是老司机 ，都值得一读。