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

微信扫一扫,分享到朋友圈

从一个案例 复习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的 加锁机制,同时 也墙裂 安利 丁奇的课程,不管是新人还是老司机 ,都值得一读。

死锁案例十五

insert 语句加锁机制

show status和set gtid_mode 导致线程死锁案例

死锁案例之九

微信扫一扫,分享到朋友圈

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

多角度审视推荐系统

上一篇

华为全新折叠屏手机专利曝光 外观设计与翻盖机类似

下一篇

你也可能喜欢

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

长按储存图像,分享给朋友