Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

30 | 答疑文章(二):用动态的观点看加锁 #41

Open
git-zjx opened this issue Oct 9, 2019 · 0 comments

Comments

@git-zjx
Copy link
Owner

@git-zjx git-zjx commented Oct 9, 2019

加锁规则
加锁规则中,包含了两个“原则”、两个“优化”和一个“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`)
) ENGINE=InnoDB;

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 id>9 and id<12 order by id desc for update;

利用上面的加锁规则,我们知道这个语句的加锁范围是主键索引上的 (0,5]、(5,10] 和 (10, 15)。也就是说,id=15 这一行,并没有被加上行锁。为什么呢?

我们说加锁单位是 next-key lock,都是前开后闭区间,但是这里用到了优化 2,即索引上的等值查询,向右遍历的时候 id=15 不满足条件,所以 next-key lock 退化为了间隙锁 (10, 15)

但是,我们的查询语句中 where 条件是大于号和小于号,这里的“等值查询”又是从哪里来的呢?

要知道,加锁动作是发生在语句执行过程中的,所以你在分析加锁行为的时候,要从索引上的数据结构开始。这里,我再把这个过程拆解一下。

如图所示,是这个表的索引 id 的示意图:
ac1aa07860c565b907b32c5f75c4f2bb

  1. 首先这个查询语句的语义是 order by id desc,要拿到满足条件的所有行,优化器必须先找到“第一个 id<12 的值”。
  2. 这个过程是通过索引树的搜索过程得到的,在引擎内部,其实是要找到 id=12 的这个值,只是最终没找到,但找到了 (10,15) 这个间隙。
  3. 然后向左遍历,在遍历过程中,就不是等值查询了,会扫描到 id=5 这一行,所以会加一个 next-key lock (0,5]。

也就是说,在执行过程中,通过树搜索的方式定位记录的时候,用的是“等值查询”的方法

等值查询的过程

与上面这个例子对应的,是下面这个语句的加锁范围是什么?

begin;
select id from t where c in(5,20,10) lock in share mode;

这条查询语句里用的是 in,我们先来看这条语句的 explain 结果:
8a089159c82c1458b26e2756583347b3

可以看到,这条 in 语句使用了索引 c 并且 rows=3,说明这三个值都是通过 B+ 树搜索定位的。
在查找 c=5 的时候,先锁住了 (0,5]。但是因为 c 不是唯一索引,为了确认还有没有别的记录 c=5,就要向右遍历,找到 c=10 才确认没有了,这个过程满足优化 2,所以加了间隙锁 (5,10)。
同样的,执行 c=10 这个逻辑的时候,加锁的范围是 (5,10] 和 (10,15);执行 c=20 这个逻辑的时候,加锁的范围是 (15,20] 和 (20,25)。
通过这个分析,我们可以知道,这条语句在索引 c 上加的三个记录锁的顺序是:先加 c=5 的记录锁,再加 c=10 的记录锁,最后加 c=20 的记录锁。
你可能会说,这个加锁范围,不就是从 (5,25) 中去掉 c=15 的行锁吗?为什么这么麻烦地分段说呢?
因为我要跟你强调这个过程:这些锁是“在执行过程中一个一个加的”,而不是一次性加上去的

理解了这个加锁过程之后,我们就可以来分析下面例子中的死锁问题了。如果同时有另外一个语句,是这么写的:

select id from t where c in(5,20,10) order by c desc for update;

此时的加锁范围,又是什么呢?
我们现在都知道间隙锁是不互锁的,但是这两条语句都会在索引 c 上的 c=5、10、20 这三行记录上加记录锁。
这里需要注意,由于语句里面是 order by c desc, 这三个记录锁的加锁顺序,是先锁 c=20,然后 c=10,最后是 c=5。
也就是说,这两条语句要加锁相同的资源,但是加锁顺序相反。当这两条语句并发执行的时候,就可能出现死锁。

怎么看死锁?
关于死锁的信息,MySQL 只保留了最后一个死锁的现场,但这个现场还是不完备的。现在,我就来简单分析一下上面这个例子的死锁现场。

下图是在出现死锁后,执行 show engine innodb status 命令得到的部分输出。这个命令会输出很多信息,有一节 LATESTDETECTED DEADLOCK,就是记录的最后一次死锁信息
a7dccb91bc17d12746703eb194775cf6

这个结果分成三部分:

  • (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 语句,来回滚。

怎么看锁等待?

看完死锁,我们再来看一个锁等待的例子:
af3602b81aeb49e33577ba372d220a75

可以看到,由于 session A 并没有锁住 c=10 这个记录,所以 session B 删除 id=10 这一行是可以的。但是之后,session B 再想 insert id=10 这一行回去就不行了

我们可以看下 show engine innodb status 的结果,信息是在这个命令输出结果的 TRANSACTIONS 这一节:
c3744fb7b61df2a5b45b8eb1f2a853a6
有几个关键信息:

  1. index PRIMARY of table test.t ,表示这个语句被锁住是因为表 t 主键上的某个锁。
  2. lock_mode X locks gap before rec insert intention waiting 这里有几个信息:
    • insert intention 表示当前线程准备插入一个记录,这是一个插入意向锁。为了便于理解,你可以认为它就是这个插入动作本身
    • gap before rec 表示这是一个间隙锁,而不是记录锁
  3. 那么这个 gap 是在哪个记录之前的呢?接下来的 0~4 这 5 行的内容就是这个记录的信息。
  4. n_fields 5 也表示了,这一个记录有 5 列:
    • 0: len 4; hex 0000000f; asc ;; 第一列是主键 id 字段,十六进制 f 就是 id=15。所以,这时我们就知道了,这个间隙就是 id=15 之前的,因为 id=10 已经不存在了,它表示的就是 (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 操作把 id=10 这一行删掉了,原来的两个间隙 (5,10)、(10,15)变成了一个 (5,15)

说到这里,你可以联合起来再思考一下这两个现象之间的关联:

  1. session A 执行完 select 语句后,什么都没做,但它加锁的范围突然“变大”了;
  2. 当我们执行 select * from t where c>=15 and c<=20 order by c desc lock in share mode; 向左扫描到 c=10 的时候,要把 (5, 10] 锁起来

也就是说,所谓“间隙”,其实根本就是由“这个间隙右边的那个记录”定义的

update 的例子

61c1ceea7b59201649c2514c9db864a7

session A 的加锁范围是索引 c 上的 (5,10]、(10,15]、(15,20]、(20,25] 和 (25,supremum]。

注意:根据 c>5 查到的第一个记录是 c=10,因此不会加 (0,5] 这个 next-key lock。

之后 session B 的第一个 update 语句,要把 c=5 改成 c=1,可以理解为两步:

  1. 插入 (c=1, id=5) 这个记录;
  2. 删除 (c=5, id=5) 这个记录。

按照我们上一节说的,索引 c 上 (5,10) 间隙是由这个间隙右边的记录,也就是 c=10 定义的。所以通过这个操作,session A 的加锁范围变成了下图所示的样子:

d2f6a0c46dd8d12f6a90dacc466d53e9

接下来 session B 要执行 update t set c = 5 where c = 1 这个语句了,一样地可以拆成两步:

  1. 插入 (c=5, id=5) 这个记录;
  2. 删除 (c=1, id=5) 这个记录。

第一步试图在已经加了间隙锁的 (1,10) 中插入数据,所以就被堵住了

@git-zjx git-zjx added this to MySQL实战45讲 in MySQL Oct 9, 2019
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
MySQL
MySQL实战45讲
1 participant
You can’t perform that action at this time.