关于|关于 InnoDB 锁的超全总结

几个月之前,开始深入学习 MySQL 。说起数据库,并发控制是其中很重要的一部分。于是,就这样开起了 MySQL 锁的学习,随着学习的深入,发现想要更好的理解锁,需要了解 MySQL 事务,数据底层的存储方式,MySQL 的执行流程,特别是索引的选择等。
在学习期间,查找了不少资料,现根据个人的理解总结下来,方便日后复习。
InnoDB 锁一览 先从 MySQL 官网的锁介绍开始,来逐一认识下这些让我们夜不能寐的小王八蛋:
关于|关于 InnoDB 锁的超全总结
文章图片

Shared and Exclusive Locks
这二位正式称呼呢,就是共享锁和排他锁,其实就是我们常说的读锁和写锁。它们之间的互斥规则,想必都清楚,就不赘述了。但有一点需要注意,共享锁和排他锁是标准的实现行级别的锁。举例来说,当给 select 语句应用 lock in share mode 或者 for update,或者更新某条记录时,加的都是行级别的锁。
与行级别的共享锁和排他锁类似的,还有表级别的共享锁和排他锁。如 LOCK TABLES ... WRITE/READ 等命令,实现的就是表级锁。
Intention Locks
在 InnoDB 中是支持多粒度的锁共存的,比如表锁和行锁。而 Intention Locks - 意向锁,就是表级锁。和行级锁一样,意向锁分为 intention shared lock (IS) 和 intention exclusive lock (IX) . 但有趣的是,IS 和 IX 之间并不互斥,也就是说可以同时给不同的事务加上 IS 和 IX. 兼容性如下:
关于|关于 InnoDB 锁的超全总结
文章图片

这时就产生疑问了,那它俩存在的意义是什么?作用就是,和共享锁和排他锁互斥。注意下,这里指的是表级别的共享锁和排他锁,和行级别没有关系!
官网中给了这样一段解释:

The main purpose of intention locks is to show that someone is locking a row, or going to lock a row in the table.

意向锁的目的就是表明有事务正在或者将要锁住某个表中的行。想象这样一个场景,我们使用 select * from t where id=0 for update; 将 id=0 这行加上了写锁。假设同时,一个新的事务想要发起 LOCK TABLES ... WRITE 锁表的操作,这时如果没有意向锁的话,就需要去一行行检测是否所在表中的某行是否存在写锁,从而引发冲突,效率太低。相反有意向锁的话,在发起 lock in share mode 或者 for update 前就会自动加上意向锁,这样检测起来就方便多了。
在实际中,手动锁表的情况并不常见,所以意向锁并不常用。特别是之后 MySQL 引入了 MDL 锁,解决了 DML 和 DDL 冲突的问题,意向锁就更不被提起来了。
Record Locks
record lock ,就是常说的行锁。InnoDB 中,表都以索引的形式存在,每一个索引对应一颗 B+ 树,这里的行锁锁的就是 B+ 中的索引记录。之前提到的共享锁和排他锁,就是将锁加在这里。
Gap Locks
Gap Locks, 间隙锁锁住的是索引记录间的空隙,是为了解决幻读问题被引入的。有一点需要注意,间隙锁和间隙锁本身之间并不冲突,仅仅和插入这个操作发生冲突。
Next-Key lock
next-key lock 是行锁(Record)和间隙锁的并集。在 RR 级别下,InnoDB 使用 next-key 锁进行树搜索和索引扫描。记住这句话,加锁的基本单位是 next-key lock.
加锁规则 该加锁原则由林晓斌老师刷代码后总结,符合的版本如下:
  1. MySQL 版本:5.x - 5.7.24, 8.0 - 8.0.13. 我是 5.7.27 也未发现问题。
规则包括:两个“原则”、两个“优化”和一个“bug”。
  1. 原则1:加锁的基本单位是 next-key lock。next-key lock 是前开后闭区间。
  2. 原则2:查找过程中访问到的对象才会加锁。
  3. 优化1:索引上的等值查询,给唯一索引加锁的时候,next-key lock 退化为行锁。
  4. 优化2:索引上的等值查询,向右遍历时且最后一个值不满足等值条件的时候,next-key lock 退化为间隙锁。
  5. 一个 bug:唯一索引上的范围查询会访问到不满足条件的第一个值为止。
解释下容易理解错误的地方:
  1. 对优化 2 的说明:
    从等值查询的值开始,向右遍历到第一个不满足等值条件记录结束,然后将不满足条件记录的 next-key 退化为间隙锁。
  2. 等值查询和遍历有什么关系?
    在分析加锁行为时,一定要从索引的数据结构开始。通过树搜索的方式定位索引记录时,用的是"等值查询",而遍历对应的是在记录上向前或向后扫描的过程。
应用场景 【关于|关于 InnoDB 锁的超全总结】表结构如下:
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);

场景1:主键索引等值间歇锁
Session A Session B Session C
begin;
update t set d=d+1 where id=7;
insert into t values(8,8,8);
被阻塞 update t set d=d+1 where id=10;
查询正常
其中 id 列为主键索引,并且 id=7 的行并不存在。
对于 Session A 来说:
  1. 根据原则1,加锁的单位是 next-key, 因为 id=7 在 5 - 10 间,next-key 默认是左开右闭。所以范围是 (5,10].
  2. 根据优化2,但因为 id=7 是等值查询,到 id=10 结束。next-key 退化成间隙锁 (5,10).
对于 Session B 来说:
  • 插入操作与间隙锁冲突,所以失败。
对于 Session C 来说:
  1. 根据原则1,next-key 加锁 (5,10].
  2. 根据优化1:给唯一索引加锁时,退化成行锁。范围变为:id=10 的行锁
  3. Session C 和 Session A (5,10) 并不起冲突,所以成功。
这里可以看出,行锁和间隙锁都是有 next-key 锁满足一定后条件后转换的,加锁的默认单位是 next-key.
场景2:非唯一索引等值锁
Session A Session B Session C
begin;
select id from t where c=5 lock in share mode;
update t set d=d+1 where id=5;
查询正常 insert into t values(7,7,7);
被阻塞
关注几点:c为非唯一索引,查询的字段仅有 id,lock in share mode 给满足条件的行加上读锁。
Session A:
  1. c=5,等值查询且值存在。先加 next-key 锁,范围为 (0,5].
  2. 由于 c 是普通索引,因此仅访问 c=5 这一条记录不会停止,会继续向右遍历,到 10 结束。根据原则2,这时会给 id=10 加 next-key (5,10].
  3. 但 id=10 同时满足优化2,退化成间隙锁 (5,10).
  4. 根据原则2,该查询使用覆盖索引,可以直接得到 id 的值,主键索引未被访问到,不加锁。
Session B:
  1. 根据原则1 和优化1,给 id=10 的主键索引加行锁,并不冲突,修改成功。
Session C:
  1. 由于 Session A 已经对索引 c 中 (5,10) 的间隙加锁,与插入 c=7 冲突, 所以被阻塞。
可以看出,加锁其实是在索引上,并且只加在访问到的记录上,如果想要在 lock in share mode 下避免数据被更新,需要引入覆盖索引不能包含的字段。
假设将 Session A 的语句改成 select id from t where c=5 for update; , for update 表示可能当前事务要更新数据,所以也会给满足的条件的主键索引加锁。这时 Session B 就会被阻塞了。
场景3:非唯一索引等值锁-锁主键
Session A Session B
begin;
select id from t where c=5 lock in share mode;
insert into t values(9,10,7);
被阻塞
和场景 2 很相似,该例主要是为了更好的说明间隙的概念。
Session A 的加锁范围不变,给索引 C 加了 (0,5] 和 (5,10) 的行锁。需要知道的是,非唯一索引形成的 key,需要包含主键 id,用于保证唯一性,画个图如下。
关于|关于 InnoDB 锁的超全总结
文章图片
由图中可知,由于非唯一索引存在主键id,并且按照 B+ 树的排序规则,不光 c 的值在加锁范围内不能被更改和插入,对应 id 的范围也不能被更改。怎么理解这句话呢,上个例子不是说,主键索引不会被加锁,怎么这里的主键 id 又被锁了呢?
首先主键索引是另外一颗B+树确实没有被锁,但这里由于 C 是非唯一索引,形成的B+树需要将主键索引的 Id 包含进来,并在按照先 c 字段,后 id 字段进行排序。这样,在给 c 字段加行锁时,对应的 id 也同时加了行锁。
上面例子中,Session2 更新成功是因为修改的是 d 地段,并没有更新 id 的值,所以成功了。而这里想要插入的 (id=6, c=10), 虽然 c=10 没有锁,但 id=6 却在锁的范围内,所以这里就被阻塞了。同样插入 (id=100,c=6) 也会被阻塞。
也就说,对于非唯一索引,考虑加锁范围时要考虑到主键 Id 的情况。
场景4:主键索引范围锁
Session A Session B Session C
begin;
select * from t where id>=10 and id <11 for update;
insert into t values(8,8,8);
正常
insert into t values(13,13,13);
被阻塞
update t set d=d+1 where id=15;
被阻塞
Session A:
  1. 先找到 id=10 行,属于等值查询。根据原则1,优化1,范围是 id=10 这行。
  2. 向后遍历,属于范围查询,找到 id=15 这行,根据原则2,范围是 (10,15]
Session B:
  1. 插入 (8,8,8) 可以,(13,13,13) 就不行了。
Session C:
  1. id=15 同样在锁的范围内,所以也被阻塞。
场景5:非唯一索引范围锁
Session A Session B Session C
begin;
select * from t where c>=10 and c <11 for update;
insert into t values(8,8,8);
被阻塞
insert into t values(13,13,13);
被阻塞
update t set d=d+1 where c=15;
被阻塞
Session A:
  1. 由于 c 是非唯一索引,索引对于 c=10 等值查询来说,根据原则1,加锁范围为 (5,10].
  2. 向右遍历,范围查询,加锁范围为 (10,15].
Session B:
  1. (8,8,8) 和 (13,13,13) 都冲突,所以被阻塞。
Session C:
  1. c=5 也被锁住,也会被阻塞。
场景6:唯一索引范围锁 bug
Session A Session B Session C
begin;
select * from t where id>10 and id <=15 for update;
update t set d=d+1 where id=20;
被阻塞
insert into t values(16,16,16);
被阻塞
Session A:
  1. 由于开始找大于 10 的过程中是第一次是范围查询,所以没有优化原则。加 (10,15].
  2. 有一个特殊的地方,理论上说由于 id 是唯一主键,找到 id=15 就应该停下来了,但实际没有。根据 bug 原则,会继续扫描第一个不满足的值为止,接着找到 id=20,因为是范围查找,没有优化原则,继续加锁 (15,20].
这个 bug 在 8.0.18 后已经修复了
对于 Session B 和 Session C 均和加锁的范围冲突。
场景7:非唯一索引 delete
Session A Session B Session C
begin;
delete from t where c=10;
insert into t values(12,12,12)
被阻塞
update t set d=d+1 where c=15;
成功
delete 后加 where 语句和 select * for update 语句的加锁逻辑类似。
Session A:
  1. 根据原则1,加 (5,10] 的 next-key.
  2. 向右遍历,根据优化2,加 (10,15) 的间歇锁。
场景8:非唯一索引 limit
Session A Session B
begin;
delete from t where c=10 limit 1;
insert into t values(12,12,12)
正常
虽然 c=10 只有一条记录,但和场景7 的加锁范围不同。
Session A:
  1. 根据原则1,加 (5,10] 的 next-key.
  2. 因为加了 limit 1,所以找到一条就可以了,不需要继续遍历,也就是说不在加锁。
所以对于 session B 来说,就不在阻塞。
场景9:next-key 引发死锁
Session A Session B
begin;
select id from t where c=10 lock in share mode;
update t set d=d+1 where c=10;
被阻塞
insert into t values(8,8,8)
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
  1. Session A 第一句 加 (5,10] next-key. 和 (10,15) 的 间隙锁。
  2. Session B,和 Session A 想要的加锁范围相同,先加 (5,10] next-key 发现被阻塞,后面 (10,15) 没有被加上,暂时等待。
  3. Session A,加入 (8,8,8) 和 Session B 的加锁范围 (5,10] 冲突,被阻塞。形成 A,B 相互等待的情况。引发死锁检测,释放 Session B.
假如把 insert into t values(8,8,8) 改成 insert into t values(11,11,11) 是可以的,因为 Session B 的间歇锁 (10,15) 没有被加上。
分析下死锁:
mysql> show engine innodb status\G; ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2020-03-08 17:04:10 0x7f9be0057700 *** (1) TRANSACTION: TRANSACTION 836108, ACTIVE 16 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 1653, OS thread handle 140307320846080, query id 1564409 localhost cisco updating update t set d=d+1 where c=10 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 251 page no 4 n bits 80 index c of table `my_test`.`t` trx id 836108 lock_mode X waiting Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 8000000a; asc; ; 1: len 4; hex 8000000a; asc; ; *** (2) TRANSACTION: TRANSACTION 836109, ACTIVE 22 sec inserting mysql tables in use 1, locked 1 5 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1 MySQL thread id 1655, OS thread handle 140307455112960, query id 1564410 localhost cisco update insert into t values(8,8,8) *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 251 page no 4 n bits 80 index c of table `my_test`.`t` trx id 836109 lock mode S Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 8000000a; asc; ; 1: len 4; hex 8000000a; asc; ; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 251 page no 4 n bits 80 index c of table `my_test`.`t` trx id 836109 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 8000000a; asc; ; 1: len 4; hex 8000000a; asc; ; *** WE ROLL BACK TRANSACTION (1)

  1. (1) TRANSACTION 表明发生死锁的第一个事务信息。
  2. (2) TRANSACTION 表明发生死锁的第二个事务信息。
  3. WE ROLL BACK TRANSACTION (1) 表明死锁的处理方案。
针对 (1) TRANSACTION:
  1. (1) WAITING FOR THIS LOCK TO BE GRANTED 表示 update t set d=d+1 where c=10 要申请写锁,并处于锁等待的情况。
  2. 申请的对象是 n_fields 2hex 8000000a; hex 8000000a; , 也就是 id=10 和 c=10 的记录。
针对 (1) TRANSACTION:
  1. HOLDS THE LOCK(S): 表示当前事务2持有的锁是 :hex 8000000a; hex 8000000a; .
  2. WAITING FOR THIS LOCK TO BE GRANTED: 表示对于 insert into t values(8,8,8) 进行所等待。
  3. lock_mode X locks gap before rec insert intention waiting: 表明在插入意向锁时,等待一个间隙锁( gap before rec)。
所以最后选择,回滚事务 (1)。
场景10:非唯一索引 order by
Session A Session B
begin;
select * from t where c>=15 and c<=20 order by c desc lock in share mode;
insert into t values(6,6,6);
被阻塞
在分析具体的加锁过程时,先要分析语句的执行顺序。如 Session A 中使用了 ordery by c desc 按照降序排列的语句,这就意味着需要在索引树 C 上,找到第一个 20 的值,然后向左遍历。并且由于 C 是非唯一索引 20 的值应该是记录中最右边的值。
Session A 的加锁过程:
  1. 在找到第一个 c=20 的值后,加 next-key (15,20].
  2. 但不会停下,因为无法确定当前 c=20 是最右面的值,继续遍历到 c=25,发现不满足,根据优化2,加 (20,25) 的间隙锁。
  3. 然后从最左面的 c=20 向左遍历,找到 c=15,加锁 next-key (10,15].
  4. 和之前是一样,无法确定 c=15 是最左面的值,继续遍历到 c=10,根据优化2,加(5,10)的间隙锁 。
  5. 最后由于是 select * 对应主键索引 id=10,15,20 加行锁。
场景 11:INSERT INTO .... SELECT ...
Session A Session B
begin; begin;
insert into t values(1,1,1);
insert into t (id,c,d) select 1,1,1 from t where id=1;
被阻塞
为了保证数据的一致性,对于 INSERT INTO .... SELECT ... 中 select 部分会加 next-key 的读锁。
对于 Session A,在插入数据后,有了 id=1 的行锁。而 Session B 中的 select 虽然是一致性读,但会加上 id=1 的读锁。与 Session A 冲突,所以被阻塞。
场景12:不等号条件里的等值查询
begin; select * from t where id>9 and id<12 order by id desc for update;

  1. 这里由于是 order by 语句,优化器会先找到第一个比 12 小的值。在索引树搜索过程后,其实要找到 id=12 的值,但没有找到,向右遍历找到 id=15,所以加锁 (10,15].
  2. 但由于第一次查找是等值查找(在索引树上搜索),根据优化2,变为间隙锁 (10,15).
  3. 然后向左遍历,变为范围查询,找到 id=5 这行,加 (0,5] 的 next-key.
场景 13:等值查询 in
begin; select id from t where c in(5,20,10) lock in share mode;

mysql> explain select id from t where c in (5,20,10) lock in share mode\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t partitions: NULL type: range possible_keys: c key: c key_len: 5 ref: NULL rows: 3 filtered: 100.00 Extra: Using where; Using index 1 row in set, 1 warning (0.00 sec)ERROR: No query specified

rows=3 并且使用索引 c,说明三个值都是通过 B+ 树搜索定位的。
  1. 先查找 c=5,锁住 (0,5]. 由于 c 不是唯一索引,向右遍历到 c=10,开始是等值查找,加 (5,10).
  2. 查找 c=15,锁住 (10,15], 再加 (15,20).
  3. 最后查找 c=20,锁住 (15,20]. 再加 (20,25).
可见,在 MySQL 中,锁是一个个逐步加的。
假设还有一个这样的语句:
select id from t where c in(5,20,10) order by c desc for update;

由于是 order by c desc,虽然这里的加锁范围没有变,但是加锁的顺序发生了改变,会按照 c=20,c=10,c=5. 的顺序加锁。虽然说间隙锁本身并不冲突,但记录锁却会。这样如果是两个语句并发的情况,就可能发生死锁,第一个语句拥有了 c5 的行锁,请求c=10 的行锁。当第二个语句,拥有了 c=10 的行锁,请求 c=5 的行锁。
场景14:GAP 动态锁
Session A Session B
begin;
select * from t where id>10 and id<=15 for update;
delete from t where id=10;
成功
insert into t values(10,10,10);
被阻塞。
这里 insert 被阻塞,就是因为间隙锁是个动态的概念,Session B 在删除 id=10 的记录后,Session A 持有的间隙变大。
对于 Session A 原来持有,(10,15] 和 (15,20] 的 next-key 锁。 Session B 删除 id=10 的记录后,(10,15] 变成了 (5,15] 的间隙。所以之后就插入不回去了。
场景15:update Gap 动态锁
Session A Session B
begin;
select * from t where id> 5 lock in share mode;
update t set c=1 where c = 5;
成功
update t set c=5 where c = 1;
被阻塞。
Session A 加锁:(5,10], (10,15], (15,20], (20,25], (25,supermum].
c>5 第一个找到的是 c=10,并且是范围查找,没有优化原则。
Session B 的 update 可以拆成两步:
  1. 插入 (c=1,id=5).
  2. 删除 (c=5,id=5).
或者理解成,加(0.5] next-key 和 (5,10) 的间隙锁,但间隙锁不冲突。
修改后 Session A 的锁变为;
(c=1, 10], (10,15], (15,20], (20,25], (25,supermum].
接下来:update t set c=1 where c = 1
  1. 插入 (c=5,id=5).
  2. 删除 (c=1,id=5).
第一步插入意向锁和间隙锁冲突。
参考 InnoDB-locking
加锁过程
explain rows

    推荐阅读