gif5网站下载不了做的动图,小广告设计,广州做网站的公,华为云速建站教程加锁规则的一些问题
Hi#xff0c;我是阿昌#xff0c;今天学习记录的是关于加锁规则的一些问题的内容。
加锁规则#xff0c;这个规则中#xff0c;包含了两个“原则”、两个“优化”和一个“bug”#xff1a;
原则 1#xff1a;加锁的基本单位是 next-key lock。nex…加锁规则的一些问题
Hi我是阿昌今天学习记录的是关于加锁规则的一些问题的内容。
加锁规则这个规则中包含了两个“原则”、两个“优化”和一个“bug”
原则 1加锁的基本单位是 next-key lock。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)
) ENGINEInnoDB;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 id9 and id12
order by id desc for update;利用上面的加锁规则这个语句的加锁范围是主键索引上的 (0,5]、(5,10]和 (10, 15)。也就是说id15 这一行并没有被加上行锁。
为什么呢说加锁单位是 next-key lock都是前开后闭区间但是这里用到了优化 2即索引上的等值查询向右遍历的时候 id15 不满足条件所以 next-key lock 退化为了间隙锁 (10, 15)。但是查询语句中 where 条件是大于号和小于号这里的“等值查询”又是从哪里来的呢
要知道加锁动作是发生在语句执行过程中的所以在分析加锁行为的时候要从索引上的数据结构开始。这里再把这个过程拆解一下。
如图 1 所示是这个表的索引 id 的示意图。 首先这个查询语句的语义是 order by id desc要拿到满足条件的所有行优化器必须先找到“第一个 id12 的值”。这个过程是通过索引树的搜索过程得到的在引擎内部其实是要找到 id12 的这个值只是最终没找到但找到了 (10,15) 这个间隙。然后向左遍历在遍历过程中就不是等值查询了会扫描到 id5 这一行所以会加一个 next-key lock (0,5]。
也就是说在执行过程中通过树搜索的方式定位记录的时候用的是“等值查询”的方法。 二、等值查询的过程
下面这个语句的加锁范围是什么 begin;
select id from t where c in(5,20,10) lock in share mode;这条查询语句里用的是 in先来看这条语句的 explain 结果。 可以看到这条 in 语句使用了索引 c 并且 rows3说明这三个值都是通过 B 树搜索定位的。
在查找 c5 的时候先锁住了 (0,5]。但是因为 c 不是唯一索引为了确认还有没有别的记录 c5就要向右遍历找到 c10 才确认没有了这个过程满足优化 2所以加了间隙锁 (5,10)。
同样的执行 c10 这个逻辑的时候加锁的范围是 (5,10] 和 (10,15)执行 c20 这个逻辑的时候加锁的范围是 (15,20] 和 (20,25)。
通过这个分析可以知道这条语句在索引 c 上加的三个记录锁的顺序是先加 c5 的记录锁再加 c10 的记录锁最后加 c20 的记录锁。这个加锁范围不就是从 (5,25) 中去掉 c15 的行锁吗为什么这么麻烦地分段说呢
因为要跟你强调这个过程这些锁是“在执行过程中一个一个加的”而不是一次性加上去的。
理解了这个加锁过程之后就可以来分析下面例子中的死锁问题了。
如果同时有另外一个语句是这么写的 select id from t where c in(5,20,10) order by c desc for update;此时的加锁范围又是什么呢
现在都知道间隙锁是不互锁的但是这两条语句都会在索引 c 上的 c5、10、20 这三行记录上加记录锁。
这里需要注意一下由于语句里面是 order by c desc 这三个记录锁的加锁顺序是先锁 c20然后 c10最后是 c5。也就是说这两条语句要加锁相同的资源但是加锁顺序相反。当这两条语句并发执行的时候就可能出现死锁。
关于死锁的信息MySQL 只保留了最后一个死锁的现场但这个现场还是不完备的。 三、怎么看死锁
图 3 是在出现死锁后执行 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 和主键字段 id0: len 4; hex 0000000a; asc ;; 是第一个字段也就是 c。值是十六进制 a也就是 101: len 4; hex 0000000a; asc ;; 是第二个字段也就是主键 id值也是 10这两行里面的 asc 表示的是接下来要打印出值里面的“可打印字符”但 10 不是可打印字符因此就显示空格。第一个事务信息就只显示出了等锁的状态在等待 (c10,id10) 这一行的锁。当然你是知道的既然出现死锁了就表示这个事务也占有别的锁但是没有显示出来。别着急从第二个事务的信息中推导出来。 第二个事务显示的信息要多一些 “ HOLDS THE LOCK(S)”用来显示这个事务持有哪些锁index c of table test.t 表示锁是在表 t 的索引 c 上hex 0000000a 和 hex 00000014 表示这个事务持有 c10 和 c20 这两个记录锁WAITING FOR THIS LOCK TO BE GRANTED表示在等 (c5,id5) 这个记录锁。
从上面这些信息中就知道
“lock in share mode”的这条语句持有 c5 的记录锁在等 c10 的锁“for update”这个语句持有 c20 和 c10 的记录锁在等 c5 的记录锁。
因此导致了死锁。这里可以得到两个结论
由于锁是一个个加的要避免死锁对同一组资源要按照尽量相同的顺序访问在发生死锁的时刻for update 这条语句占有的资源更多回滚成本更大所以 InnoDB 选择了回滚成本更小的 lock in share mode 语句来回滚。 四、怎么看锁等待
看一个锁等待的例子。 可以看到由于 session A 并没有锁住 c10 这个记录所以 session B 删除 id10 这一行是可以的。但是之后session B 再想 insert id10 这一行回去就不行了。
现在一起看一下此时 show engine innodb status 的结果看看能不能给我们一些提示。
锁信息是在这个命令输出结果的 TRANSACTIONS 这一节。
可以在文稿中看到这张图片 来看几个关键信息。
index PRIMARY of table test.t 表示这个语句被锁住是因为表 t 主键上的某个锁。lock_mode X locks gap before rec insert intention waiting 这里有几个信息 insert intention 表示当前线程准备插入一个记录这是一个插入意向锁。可以认为它就是这个插入动作本身。gap before rec 表示这是一个间隙锁而不是记录锁。 那么这个 gap 是在哪个记录之前的呢接下来的 0~4 这 5 行的内容就是这个记录的信息。n_fields 5 也表示了这一个记录有 5 列 0: len 4; hex 0000000f; asc ;; 第一列是主键 id 字段十六进制 f 就是 id15。所以这时我们就知道了这个间隙就是 id15 之前的因为 id10 已经不存在了它表示的就是 (5,15)。1: len 6; hex 000000000513; asc ;; 第二列是长度为 6 字节的事务 id表示最后修改这一行的是 trx id 为 1299 的事务。2: len 7; hex b0000001250134; asc % 4;; 第三列长度为 7 字节的回滚段信息。可以看到这里的 acs 后面有显示内容 (% 和 4)这是因为刚好这个字节是可打印字符。后面两列是 c 和 d 的值都是 15。
因此由于 delete 操作把 id10 这一行删掉了原来的两个间隙 (5,10)、(10,15变成了一个 (5,15)。
说到这里可以联合起来再思考一下这两个现象之间的关联
session A 执行完 select 语句后什么都没做但它加锁的范围突然“变大”了第 21 篇文章的课后思考题当我们执行 select * from t where c15 and c20 order by c desc lock in share mode; 向左扫描到 c10 的时候要把 (5, 10]锁起来。
也就是说所谓“间隙”其实根本就是由“这个间隙右边的那个记录”定义的。 五、update 的例子
一个 update 语句的案例。
session A 的加锁范围是索引 c 上的 (5,10]、(10,15]、(15,20]、(20,25]和 (25,supremum]。 注意根据 c5 查到的第一个记录是 c10因此不会加 (0,5]这个 next-key lock。 之后 session B 的第一个 update 语句要把 c5 改成 c1可以理解为两步
插入 (c1, id5) 这个记录删除 (c5, id5) 这个记录。
索引 c 上 (5,10) 间隙是由这个间隙右边的记录也就是 c10 定义的。
所以通过这个操作session A 的加锁范围变成了图 7 所示的样子 好接下来 session B 要执行 update t set c 5 where c 1 这个语句了一样地可以拆成两步
插入 (c5, id5) 这个记录删除 (c1, id5) 这个记录。
第一步试图在已经加了间隙锁的 (1,10) 中插入数据所以就被堵住了。 六、问题 一个空表有间隙吗这个间隙是由谁定义的怎么验证这个结论呢 有间隙间隙应该是负无穷~正无穷。验证如下在空表select * for update,另一个线程insert 会发现被阻塞。
有间隙应该是由最小值(infimum)和最大值(supermum)来定的。
