30 | 再分析Innodb引擎的锁（看完这篇就可以拍肚皮了）

最新推荐文章于 2024-04-29 21:11:11 发布

笔仙笔仙

最新推荐文章于 2024-04-29 21:11:11 发布

阅读量63

点赞数

分类专栏： MySQL笔记文章标签： mysql java

原文链接：https://time.geekbang.org/column/article/78427

版权

MySQL笔记专栏收录该内容

48 篇文章 1 订阅

订阅专栏

本文探讨了MySQL的RR（Repeatable Read）隔离级别下，加锁的规则和示例，包括不等号条件下的等值查询、IN操作的锁行为，以及死锁信息的查看和死锁例子。同时，分析了锁膨胀和锁等待的情况，举例说明了更新语句如何造成锁等待。课后题涉及间隙锁和Next-Key锁的区别，并解释了死锁日志中的锁模式含义。

摘要由CSDN通过智能技术生成

一、前言

最近公司述职加上家里事情较多耽误了两周，现在开始继续～fighting！！！

现有表结构及数据：

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);

二、正文Begin

1.回忆数据库在RR隔离下的加锁5大规则？

2.“不等号条件里的等值查询”例子？加锁过程是如何的？

begin;select * from t where id>9 and id<12 order by id desc for update;
1）首先这个查询语句的语义是 order by id desc，要拿到满足条件的所有行，优化器必须先找到“第一个 id<12 的值”。2）在引擎内部，其实是要找到 id=12 的这个值，只是最终没找到，但找到了 (10,15] 这个间隙，根据优化2向右遍历15不满足因此退化位gap锁（10, 15）。3）然后向左遍历，在遍历过程中，就不是等值查询了，会扫描到 id=5 这一行，所以会加一个 next-key lock (0,5]。4）此外还会加（5, 10] next-key锁。

3.“in操作属于等值查询”例子？加锁过程是如何的？另外执行什么语句并发情况会造成死锁？

begin;select id from t where c in(5,20,10) lock in share mode;
1）在查找 c=5 的时候，先锁住了 (0,5]。但是因为 c 不是唯一索引，为了确认还有没有别的记录 c=5，就要向右遍历，找到 c=10 才确认没有了，这个过程满足优化 2，所以加了间隙锁 (5,10)。2）同样的，执行 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;

4.如何查看最后一次死锁信息？下图中的锁信息如何解读（第一个/第二个事务持有什么锁在等待什么锁）？

show engine innodb status

5.锁执行过程中会造成锁膨胀，导致锁等待，举个例子？主要是dml中什么操作会造成？

session A = begin;select * from t where id > 10 and id <=15 for update; session B = delete from t where id = 10;(query ok) insert into t values(10,10,10);(blocked) --备注：在删除之前间隙锁只有一个(10, 15)，删除了数据之后，导致间隙锁左侧扩张成了5，间隙锁成为了(5, 15)。这里是8.0.18以后高版本了，没有唯一索引范围加锁的BUG了
insert和delete

6.锁执行过程中造成的锁等待，举个例子？update语句拆解？主要是dml中的什么操作会造成？

session A = begin;select c from t where c > 5 lock inshare mode; session B = update t set c = 1 where c = 5;(query ok) update t set c = 5 where c = 1;(blocked)
之后 session B 的第一个 update 语句，要把 c=5 改成 c=1，你可以理解为两步：插入 (c=1, id=5) 这个记录；删除 (c=5, id=5) 这个记录。
update语句

三、课后题时间

所谓“间隙”，其实根本就是由“这个间隙右边的那个记录”定义的。那么，一个空表有间隙吗？这个间隙是由谁定义的？你怎么验证这个结论呢？

答：一个空表就只有一个间隙。比如，在空表上执行：begin;select * from t where id>1 for update;这个查询语句加锁的范围就是 next-key lock (-∞, supremum]。验证方式：用session B = insert into t values(2)

四、评论出人才

7.⭐️这TM必须要考试，死锁日志中的各个单词含义？

lock_mode X waiting表示next-key lock；lock_mode X locks rec but not gap是只有行锁；还有一种 “locks gap before rec”，就是只有间隙锁；

8.就是一个比较好的栗子，总结不出来问题点了。

问题：select * from t where c>=15 and c<=20 order by c desc in share mode;
最右边加的是 (20, 25)的间隙锁，
而这个例子select * from t where id>10 and id<=15 for update中，最右边加的是(15,20]的next-key锁，
这两个查询为何最后边一个加的gap锁，一个加的next-key锁，他们都是<=的等值范围查询，区别在哪里？

答：select * from t where c>=15 and c<=20 order by c desc in share mode;
这个语句是根据 c=20 来查数据的，所以加锁(20,25]的时候，可以使用优化2；
select * from t where id>10 and id<=15 for update；
这里的id=20，是用“向右遍历”的方式得到的，没有优化，按照“以next-key lock”为加锁单位来执行