关于 InnoDB 锁的超全总结

几个月之前，开始深入学习 MySQL 。说起数据库，并发控制是其中很重要的一部分。于是，就这样开起了 MySQL 锁的学习，随着学习的深入，发现想要更好的理解锁，需要了解 MySQL 事务，数据底层的存储方式，MySQL 的执行流程，特别是索引的选择等。

在学习期间，查找了不少资料，现根据个人的理解总结下来，方便日后复习。

InnoDB 锁一览

先从 MySQL 官网的锁介绍开始，来逐一认识下这些让我们夜不能寐的小王八蛋：

eyAf6j2.png!web

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. 兼容性如下：

eeIBZf7.png!web

这时就产生疑问了，那它俩存在的意义是什么？作用就是，和共享锁和排他锁互斥。注意下，这里指的是表级别的共享锁和排他锁，和行级别没有关系！

官网中给了这样一段解释：

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.

加锁规则

该加锁原则由林晓斌老师刷代码后总结，符合的版本如下：

MySQL 版本：5.x - 5.7.24， 8.0 - 8.0.13. 我是 5.7.27 也未发现问题。

规则包括：两个“原则”、两个“优化”和一个“bug”。

原则1：加锁的基本单位是 next-key lock。next-key lock 是前开后闭区间。
原则2：查找过程中访问到的对象才会加锁。
优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

解释下容易理解错误的地方：

对优化 2 的说明：

从等值查询的值开始，向右遍历到第一个不满足等值条件记录结束，然后将不满足条件记录的 next-key 退化为间隙锁。
等值查询和遍历有什么关系？

在分析加锁行为时，一定要从索引的数据结构开始。通过树搜索的方式定位索引记录时，用的是"等值查询"，而遍历对应的是在记录上向前或向后扫描的过程。

应用场景

表结构如下：

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，加锁的单位是 next-key, 因为 id=7 在 5 - 10 间，next-key 默认是左开右闭。所以范围是 (5,10].
根据优化2，但因为 id=7 是等值查询，到 id=10 结束。next-key 退化成间隙锁 (5,10).

对于 Session B 来说：

插入操作与间隙锁冲突，所以失败。

对于 Session C 来说：

根据原则1，next-key 加锁 (5,10].
根据优化1：给唯一索引加锁时，退化成行锁。范围变为：id=10 的行锁
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：

c=5，等值查询且值存在。先加 next-key 锁，范围为 (0,5].
由于 c 是普通索引，因此仅访问 c=5 这一条记录不会停止，会继续向右遍历，到 10 结束。根据原则2，这时会给 id=10 加 next-key (5,10].
但 id=10 同时满足优化2，退化成间隙锁 (5,10).
根据原则2，该查询使用覆盖索引，可以直接得到 id 的值，主键索引未被访问到，不加锁。

Session B：

根据原则1 和优化1，给 id=10 的主键索引加行锁，并不冲突，修改成功。

Session C：

由于 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，用于保证唯一性，画个图如下。

C:%5CUsers%5Cyuwzhang%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5Cimage-20200304140317000.png

由图中可知，由于非唯一索引存在主键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：

先找到 id=10 行，属于等值查询。根据原则1，优化1，范围是 id=10 这行。
向后遍历，属于范围查询，找到 id=15 这行，根据原则2，范围是 (10,15]

Session B：

插入 (8,8,8) 可以，(13,13,13) 就不行了。

Session C：

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：

由于 c 是非唯一索引，索引对于 c=10 等值查询来说，根据原则1，加锁范围为 (5,10].
向右遍历，范围查询，加锁范围为 (10,15].

Session B:

(8,8,8) 和 (13,13,13) 都冲突，所以被阻塞。

Session C:

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：

由于开始找大于 10 的过程中是第一次是范围查询，所以没有优化原则。加 (10,15].
有一个特殊的地方，理论上说由于 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，加 (5,10] 的 next-key.
向右遍历，根据优化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，加 (5,10] 的 next-key.
因为加了 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

Session A 第一句加 (5,10] next-key. 和 (10,15) 的间隙锁。
Session B，和 Session A 想要的加锁范围相同，先加 (5,10] next-key 发现被阻塞，后面 (10,15) 没有被加上，暂时等待。
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) TRANSACTION
(2) TRANSACTION
WE ROLL BACK TRANSACTION (1)

针对 (1) TRANSACTION :

(1) WAITING FOR THIS LOCK TO BE GRANTED 表示 update t set d=d+1 where c=10 要申请写锁,并处于锁等待的情况。
申请的对象是 n_fields 2 ， hex 8000000a; 和 hex 8000000a; , 也就是 id=10 和 c=10 的记录。

针对 (1) TRANSACTION :

HOLDS THE LOCK(S): 表示当前事务2持有的锁是 : hex 8000000a; 和 hex 8000000a; .
WAITING FOR THIS LOCK TO BE GRANTED: 表示对于 insert into t values(8,8,8) 进行所等待。
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 的加锁过程：

select *

场景 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;

这里由于是 order by 语句，优化器会先找到第一个比 12 小的值。在索引树搜索过程后，其实要找到 id=12 的值，但没有找到，向右遍历找到 id=15，所以加锁 (10,15].
但由于第一次查找是等值查找（在索引树上搜索），根据优化2，变为间隙锁 (10,15).
然后向左遍历，变为范围查询，找到 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+ 树搜索定位的。

先查找 c=5，锁住 (0,5]. 由于 c 不是唯一索引，向右遍历到 c=10，开始是等值查找，加 (5,10).
查找 c=15，锁住 (10,15], 再加 (15,20).
最后查找 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 可以拆成两步：

插入 (c=1,id=5).
删除 (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

插入 (c=5,id=5).
删除 (c=1,id=5).

第一步插入意向锁和间隙锁冲突。

参考

InnoDB-locking

加锁过程

explain rows

InnoDB 锁一览

Shared and Exclusive Locks

Intention Locks

Record Locks

Gap Locks

Next-Key lock

加锁规则

应用场景

场景1：主键索引等值间歇锁

场景2：非唯一索引等值锁

场景3：非唯一索引等值锁-锁主键

也就说，对于非唯一索引，考虑加锁范围时要考虑到主键 Id 的情况。

场景4：主键索引范围锁

场景5：非唯一索引范围锁

场景6：唯一索引范围锁 bug

场景7：非唯一索引 delete

场景8：非唯一索引 limit

场景9：next-key 引发死锁

场景10：非唯一索引 order by

场景 11：INSERT INTO .... SELECT ...

场景12：不等号条件里的等值查询

场景 13：等值查询 in

可见，在 MySQL 中，锁是一个个逐步加的。

场景14：GAP 动态锁

场景15：update Gap 动态锁

参考

Recommend

Build Great Native CLI Apps in Java with Graalvm and Picocli

Variables, Data Types, Immutability and User Input with Kotlin - Marcus Eisele -...

现在都流行用 Chrome 做 FQ 浏览器了？

argoat - goat-based command-line argument parser for C

iPhone 11全球供应紧张零售商和运营商不知何时到货

苹果上诉被驳回：侵权Caltech专利需赔偿8.38亿美元

垃圾佬想装一台 itx 黑果

jquery教程

ajax原理

FreeNAS 和 TrueNAS 合并

About Joyk