本文主要是介绍MySQL学习笔记:等值查询、范围查询、死锁、间隙锁的本质,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!
环境
MySQL:5.7.26-log
前言
答疑文章(二):用动态的观点看加锁
- 原则 1:加锁的基本单位是 next-key lock。希望你还记得,next-key lock 是前开后闭区间。
- 原则 2:查找过程中访问到的对象才会加锁。
- 优化 1:索引上的等值查询,给唯一索引加锁的时候,next-key lock 退化为行锁。
- 优化 2:索引上的等值查询,向右遍历时且最后一个值不满足等值条件的时候,next-key lock 退化为间隙锁。
- 一个 bug:唯一索引上的范围查询会访问到不满足条件的第一个值为止。(MySQL 8 已经修复)
不等号条件里的等值查询
begin;
select * from t where id>9 and id<12 order by id desc for update;
-
首先这个查询语句的语义是
order by id desc
,要拿到满足条件的所有行,优化器必须先找到“第一个 id<12 的值”; -
这个过程是通过索引树的搜索过程得到的,在引擎内部,其实是要找到 id=12 的这个值,只是最终没找到,但找到了 (10,15) 这个间隙。(这里就是等值查询,即:通过索引树搜索的就是等值查询)。
-
然后向左遍历,在遍历过程中,就不是等值查询了,会扫描到 id=5 这一行,所以会加一个 next-key lock (0,5]。
说明:
①“查询语句order by id desc
”,说明是按照ID进行降序,所以其实先查询id<12
的值,然后从右往左进行扫描。
②默认情况,一般都是升序,所以一般也就是从左往右扫描。
小结:也就是说,在执行过程中,通过树搜索的方式定位记录的时候,用的是“等值查询”的方法。
死锁
关于死锁的信息,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 语句,来回滚。
怎么看锁等待 与 间隙锁的本质
上图的意思:sessionA先查询加读锁,然后sessionB先删除id=10记录,然后再插入ID=10的记录,发现被阻塞了。
sessionA:因为id>10,且是自然顺序,也就是升序。第一条记录是15,所以间隙锁(10, 15)。
sessionB: delete id=10以后,间隙锁,变为:(5,15)
也就是说,session A 执行完 select 语句后,什么都没做,但它加锁的范围突然“变大”了;
又如:当我们执行 select * from t where c>=15 and c<=20 order by c desc lock in share mode;
向左扫描到 c=10 的时候,要把 (5, 10]锁起来。
- 间隙锁的本质:也就是说,所谓“间隙”,其根本就是由“这个间隙右边的那个记录”定义的。
- 锁,作用于索引上的。
在看一个例子:update
sessionA:自然顺序,所以是升序,c>5,那么第一条记录就是c=10,所以间隙锁(5,10]、(10,15]、(15,20]、(20,25]、(25,无穷大]。
sessionB:第一个update,可以看做:
- 删除 (c=5, id=5) 这个记录。
- 插入 (c=1, id=5) 这个记录;
按照上面说的,间隙锁的本质由右边的记录也就是c=10决定的。所以sessionA的间隙锁变为了:(1,10]…后面不变
sessionB的第二个update,可以看做:
- 删除 (c=1, id=5) 这个记录。
- 插入 (c=5, id=5) 这个记录;
因为c=1没有被锁住,所以可以删除,然后插入c=5,被间隙锁(1,10]锁住了,所以被阻塞了。
在原文中,是将update看做,先插入,再删除。我觉得不合理,因为先插入会导致主键冲突。
举例思考
例1 等值查询与遍历区别
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”为加锁单位来执行。
等值查询:通过树搜索的方式定位记录。
总结
- 等值查询:通过树搜索的方式定位记录。遍历就是遍历叶子节点。加锁应该是加在叶子节点上,在树搜索中遇到的,不会锁住。
- 间隙锁的本质:所谓“间隙”,其根本就是由“这个间隙右边的那个记录”定义的。
这篇关于MySQL学习笔记:等值查询、范围查询、死锁、间隙锁的本质的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!