Mysql原理-锁

4. Mysql 锁

本篇学习总结参考自小林的mysql文档。
在 MySQL 里，根据加锁的范围，可以分为全局锁、表级锁和行锁三类。

4.1 全局锁

1、全局锁应用场景是什么？

• 全局锁主要应用于做全库逻辑备份，这样在备份数据库期间，不会因为数据或表结构的更新，而出现备份文件的数据与预期的不一样。就是说，如果想做全库备份，那么就先加上全局锁。

2、全局锁是怎么用的？

• 要使用全局锁，则要执行这条命令：

  flush tables with read lock
  

  执行后，整个数据库就处于只读状态了，这时其他线程执行以下操作，都会被阻塞：

  • 对数据的增删改操作，比如 insert、delete、update等语句；
  • 对表结构的更改操作，比如 alter table、drop table 等语句。

  如果要释放全局锁，则要执行这条命令：

  unlock tables
  

  当然，当会话断开了，全局锁会被自动释放。

3、加全局锁又会带来什么缺点呢？

• 加上全局锁，意味着整个数据库都是只读状态。那么如果数据库里有很多数据，备份就会花费很多的时间，关键是备份期间，业务只能读数据，而不能更新数据，这样会造成业务停滞。

4、既然备份数据库数据的时候，使用全局锁会影响业务，那有什么其他方式可以避免？

• 有的，如果数据库的引擎支持的事务支持可重复读的隔离级别，那么在备份数据库之前先开启事务，会先创建 Read View，然后整个事务执行期间都在用这个 Read View，而且由于 MVCC 的支持，备份期间业务依然可以对数据进行更新操作。

4.2 表级锁

MySQL 表级锁有哪些？具体怎么用的。

MySQL 里面表级别的锁有这几种：

• 表锁；
• 元数据锁（MDL）;
• 意向锁；
• AUTO-INC 锁；

表锁

先来说说表锁。如果我们想对学生表（t_student）加表锁，可以使用下面的命令：

//表级别的共享锁，也就是读锁
lock tables t_student read;

//表级别的独占锁，也就是写锁；
lock tables t_stuent write;

要释放表锁，可以使用下面这条命令，会释放当前会话的所有表锁，另外，当会话退出后，也会释放所有表锁。：

unlock tables

加了表共享锁后，执行写操作就会被阻塞。

不过尽量避免在使用 InnoDB 引擎的表使用表锁，因为表锁的颗粒度太大，会影响并发性能，InnoDB 牛逼的地方在于实现了颗粒度更细的行级锁。

元数据锁

再来说说元数据锁（MDL）。MDL 是为了保证当用户对表执行 CRUD 操作时，防止其他线程对这个表结构做变更。

我们不需要显示的使用 MDL，因为当我们对数据库表进行操作时，会自动给这个表加上 MDL：

• 对一张表进行 CRUD 操作时，加的是 MDL 读锁；
• 对一张表做结构变更操作的时候，加的是 MDL 写锁；

当有线程在执行 select 语句（ 加 MDL 读锁）的期间，如果有其他线程要更改该表的结构（ 申请 MDL 写锁），那么将会被阻塞，直到执行完 select 语句（ 释放 MDL 读锁）。反之同理。

1、MDL 不需要显示调用，那它是在什么时候释放的?

• MDL 是在事务提交后才会释放，这意味着事务执行期间，MDL 是一直持有的。

2、线程 A 先启用了事务（但是一直不提交），然后执行一条 select 语句，此时就先对该表加上 MDL 读锁。然后，线程 B 也执行了同样的 select 语句，此时并不会阻塞，因为「读读」并不冲突；

3、DML写锁获取优先级高于读锁，所以当获取 DML写锁的线程被阻塞时（当前已经有一个 DML 读锁时），后续select语句（会加DML读锁）就会被阻塞。如果后续 select 线程太多，这时数据库的线程很快就会爆满了。

意向锁

接着，说说意向锁。

• 在使用 InnoDB 引擎的表里，对某些记录加上「共享锁」之前，需要先在表级别加上一个「意向共享锁」；
• 在使用 InnoDB 引擎的表里，对某些纪录加上「独占锁」之前，需要先在表级别加上一个「意向独占锁」；

也就是，当执行插入、更新、删除操作，需要先对表加上「意向独占锁」，然后对该记录加独占锁。

意向共享锁和意向独占锁是表级锁。表锁和行锁是满足读读共享、读写互斥、写写互斥的。意向锁之间不会发生冲突，意向锁只会与共享表锁和独占表锁发生冲突。

意向锁的目的是为了快速判断表里是否有记录被加锁。如果没有「意向锁」，那么加「独占表锁」时，就需要遍历表里所有记录，查看是否有记录存在独占锁，这样效率会很慢。那么有了「意向锁」，由于在对记录加独占锁前，先会加上表级别的意向独占锁，那么在加「独占表锁」时，直接查该表是否有意向独占锁，如果有就意味着表里已经有记录被加了独占锁，这样就不用去遍历表里的记录。

普通的 select 是不会加行级锁的，普通的 select 语句是利用 MVCC 实现一致性读，是无锁的。

//先在表上加上意向共享锁，然后对读取的记录加共享锁
select ... lock in share mode;

//先表上加上意向独占锁，然后对读取的记录加独占锁
select ... for update;

AUTO-INC 锁

表里的主键通常都会设置成自增的，这是通过对主键字段声明 AUTO_INCREMENT 属性实现的。之后可以在插入数据时，可以不指定主键的值，数据库会自动给主键赋值递增的值，这主要是通过 AUTO-INC 锁实现的。

在插入数据时，会加一个表级别的 AUTO-INC 锁，然后为被 AUTO_INCREMENT 修饰的字段赋值递增的值，等插入语句执行完成后，才会把 AUTO-INC 锁释放掉。那么，一个事务在持有 AUTO-INC 锁的过程中，其他事务的如果要向该表插入语句都会被阻塞，从而保证插入数据时，被 AUTO_INCREMENT 修饰的字段的值是连续递增的。但是， AUTO-INC 锁在对大量数据进行插入的时候，会影响插入性能，因为另一个事务中的插入会被阻塞。

AUTO-INC锁不是在事务提交后才释放，而是执行完插入语句后就释放。

因此， 在 MySQL 5.1.22 版本开始，InnoDB 存储引擎提供了一种轻量级的锁来实现自增。一样也是在插入数据的时候，会为被 AUTO_INCREMENT 修饰的字段加上轻量级锁，然后给该字段赋值一个自增的值，就把这个轻量级锁释放了，而不需要等待整个插入语句执行完后才释放锁。

4.3 行级锁

InnoDB 引擎是支持行级锁的，而 MyISAM 引擎并不支持行级锁。

前面也提到，普通的 select 语句是不会对记录加锁的，因为它属于快照读。如果要在查询时对记录加行锁，可以使用下面这两个方式，这种查询会加锁的语句称为锁定读。

begin;
//对读取的记录加共享锁
select ... lock in share mode;
commit; //锁释放

begin;
//对读取的记录加排他锁
select ... for update;
commit; //锁释放

上面这两条语句必须在一个事务中，所以在使用这两条语句的时候，要加上 begin / start transaction 或者 set autocommit = 0。当事务提交了，锁就会被释放。

共享锁（S锁）满足读读共享，读写互斥。独占锁（X锁）满足写写互斥、读写互斥。

行级锁的类型主要有三类：

• Record Lock，记录锁，也就是仅仅把一条记录锁上；
• Gap Lock，间隙锁，锁定一个范围，但是不包含记录本身；
• Next-Key Lock：Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。

1 Record Lock

Record Lock 称为 记录锁，锁住的是一条记录。而且记录锁是有 S 锁和 X 锁之分的：

• 当一个事务对一条记录加了 S 型记录锁后，其他事务也可以继续对该记录加 S 型记录锁（S 型与 S 锁兼容），但是不可以对该记录加 X 型记录锁（S 型与 X 锁不兼容）;
• 当一个事务对一条记录加了 X 型记录锁后，其他事务既不可以对该记录加 S 型记录锁（S 型与 X 锁不兼容），也不可以对该记录加 X 型记录锁（X 型与 X 锁不兼容）。

举个例子，当一个事务执行了下面这条语句：

mysql > begin;
mysql > select * from t_test where id = 1 for update;

就是对 t_test 表中主键 id 为 1 的这条记录加上 X 型的记录锁，这样其他事务就无法对这条记录进行修改了。

当事务执行 commit 后，事务过程中生成的锁都会被释放。

2 Gap Lock

Gap Lock 称为 间隙锁，只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象。

假设，表中有一个范围 id 为（3，5）间隙锁，那么其他事务就无法插入 id = 4 这条记录了，这样就有效的防止幻读现象的发生。

间隙锁虽然存在 X 型间隙锁和 S 型间隙锁，但是并没有什么区别，间隙锁之间是兼容的，即两个事务可以同时持有包含共同间隙范围的间隙锁，并不存在互斥关系，因为间隙锁的目的是防止插入幻影记录而提出的。

接下来，来验证「 MySQL 记录锁+间隙锁可以防止删除操作而导致的幻读问题」的结论。实验环境：MySQL 8.0 版本，可重复读隔离级。

现在有一张用户表（t_user），表里只有一个主键索引，表里有以下行数据：

现在有一个 A 事务执行了一条查询语句，查询到年龄大于 20 岁的用户共有 6 条行记录。

然后， B 事务执行了一条删除 id = 2 的语句：

此时，B 事务的删除语句就陷入了等待状态，说明是无法进行删除的。

因此，MySQL 记录锁+间隙锁可以防止删除操作而导致的幻读问题。

注意：select * from t_user where age > 20 for update 因为 where 后面没有用到索引列（本表只有主键 id 有一个主键索引，也叫聚簇索引，并没有给 age 创建二级索引，所以查询语句没有使用索引），这相当于把整个表给锁住了，其他事务在对该表进行增、删、改操作的时候都会被阻塞。

1、为什么上述只是查询年龄 20 岁以上行记录，而把整个表给锁住了呢？

• 这是因为事务 A 的这条查询语句是全表扫描，锁是在遍历索引的时候加上的，并不是针对输出的结果加锁。

  

  因此，在线上在执行 update、delete、select … for update 等具有加锁性质的语句，一定要检查语句是否走了索引，如果是全表扫描的话，会对每一个索引加 next-key 锁，相当于把整个表锁住了，这是挺严重的问题。

2、如果对 age 建立索引，事务 A 这条查询会加什么锁呢？

• 解析见MySQL 记录锁+间隙锁可以防止删除操作而导致的幻读吗？ | 小林coding (xiaolincoding.com)，最后一部分，一定要看

3 Next-Key Lock

Next-Key Lock 称为 临键锁，是 Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。

假设，表中有一个范围 id 为（3，5] 的 next-key lock，那么其他事务即不能插入 id = 4 记录，也不能修改 id = 5 这条记录。

所以，next-key lock 即能保护该记录，又能阻止其他事务将新纪录插入到被保护记录前面的间隙中。

next-key lock 是包含间隙锁+记录锁的（因为记录锁有 S 型和 X 型之分，所以 next-key lock 也有 S 型和 X 型），如果一个事务获取了 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，是会被阻塞的。

Innodb 引擎为了解决「可重复读」隔离级别下的幻读问题，就引出了 next-key 锁，它是记录锁和间隙锁的组合。

Innodb 可重复读隔离级别解决幻读的两种方式：

• 针对快照读（普通 select）：MVCC 机制（Read View + 记录隐藏列 trx_id 和 roll_pointer）
• 针对当前读（select … for update）：加 next-key lock。

4 插入意向锁

一个事务在插入一条记录的时候，需要判断插入位置是否已被其他事务加了间隙锁（next-key lock 也包含间隙锁）。如果有的话，插入操作就会发生阻塞，直到拥有间隙锁的那个事务提交为止（释放间隙锁的时刻），在此期间会生成一个插入意向锁，表明有事务想在某个区间插入新记录，但是现在锁处于等待状态（事务并没有获取到锁），现象就是 Insert 语句会被阻塞。。

举个例子，假设事务 A 已经对表加了一个范围 id 为（3，5）间隙锁。

当事务 A 还没提交的时候，事务 B 准备向该表插入一条 id = 4 的新记录，这时会判断到插入的位置已经被事务 A 加了间隙锁，于是事物 B 会生成一个插入意向锁，然后将锁的状态设置为等待状态（PS：MySQL 加锁时，是先生成锁结构，然后设置锁的状态，如果锁状态是等待状态，并不是意味着事务成功获取到了锁，只有当锁状态为正常状态时，才代表事务成功获取到了锁），此时事务 B 就会发生阻塞，直到事务 A 提交了事务。

插入意向锁名字虽然有意向锁，但是它并不是意向锁，它是一种特殊的间隙锁，属于行级别锁。如果说间隙锁锁住的是一个区间，那么「插入意向锁」锁住的就是一个点。因而从这个角度来说，插入意向锁确实是一种特殊的间隙锁。

插入意向锁与间隙锁的另一个非常重要的差别是：尽管「插入意向锁」也属于间隙锁，但两个事务却不能在同一时间内，一个拥有间隙锁，另一个拥有该间隙区间内的插入意向锁（当然，插入意向锁如果不在间隙锁区间内则是可以的）。

4.4 Mysql 是怎么加锁的？

1 什么 SQL 语句会加【行级锁】？（首先要明白的）

在说 MySQL 是怎么加行级锁的时候，其实是在说 InnoDB 引擎是怎么加行级锁的。

普通的 select 语句是不会对记录加锁的（除了串行化隔离级别），因为它属于快照读，是通过 MVCC（多版本并发控制）实现的。如果要在查询时对记录加行级锁，可以使用下面这两个方式，这两种查询会加锁的语句称为锁定读。

//对读取的记录加共享锁(S型锁)
select ... lock in share mode;

//对读取的记录加独占锁(X型锁)
select ... for update;

//上面这两条语句必须在事务中，所以在使用这两条语句的时候，要加上 begin 或者 start transaction 开启事务的语句。**当事务提交了，锁就会被释放**。

update 和 delete 操作都会加行级锁，且锁的类型都是独占锁(X型锁)。

//对操作的记录加独占锁(X型锁)
update table .... where id = 1;

//对操作的记录加独占锁(X型锁)
delete from table where id = 1;

2 行级锁的种类

见 4.3

3 MySQL 是怎么加行级锁的？（内容较多先理解）

见原文，这部分内容比较多。如果嫌弃内容多，可以先直接看原文最下面的总结。MySQL 是怎么加锁的？ | 小林coding (xiaolincoding.com)

加锁的对象是索引，加锁的基本单位是 next-key lock，它是由记录锁和间隙锁组合而成的，next-key lock 是前开后闭区间，而间隙锁是前开后开区间。

但是，next-key lock 在一些场景下会退化成记录锁或间隙锁。那到底是什么场景呢？总结一句，在使用记录锁或者间隙锁就能避免幻读现象的场景下， next-key lock 就会退化成退化成记录锁或间隙锁。

唯一索引等值查询

当我们用唯一索引进行等值查询的时候（比如 select * from user where id = 1 for update），查询的记录存不存在，加锁的规则也会不同：

• 当查询的记录是「存在」的，在索引树上定位到这一条记录后，将该记录的索引中的 next-key lock 会退化成「记录锁」。
• 当查询的记录是「不存在」的，在索引树找到第一条大于该查询记录的记录后，将该记录的索引中的 next-key lock 会退化成「间隙锁」。

1、主键索引也是一种唯一索引。

2、主键索引作为唯一索引，那么加锁只加在主键索引上。如果是**用二级索引进行锁定读查询**（不管是不是非唯一索引，还是唯一索引）的时候，除了会对二级索引项加行级锁，而且还会对查询到的记录的主键索引项上加「记录锁」。

3、为什么唯一索引等值查询并且查询记录存在的场景下，该记录的索引中的 next-key lock 会退化成记录锁？

• 原因就是在唯一索引等值查询并且查询记录存在的场景下，仅靠记录锁也能避免幻读的问题。
• 由于主键/唯一键具有唯一性，所以其他事务插入 id = 1 的时候，会因为主键冲突，导致无法插入 id = 1 的新记录。这样事务 A 在多次查询 id = 1 的记录的时候，不会出现前后两次查询的结果集不同，也就避免了幻读的问题。
• 由于对 id = 1 加了记录锁，其他事务无法删除该记录，这样事务 A 在多次查询 id = 1 的记录的时候，不会出现前后两次查询的结果集不同，也就避免了幻读的问题。

4、为什么唯一索引等值查询并且查询记录「不存在」的场景下，在索引树找到第一条大于该查询记录的记录后，要将该记录的索引中的 next-key lock 会退化成「间隙锁」？

• 原因就是在唯一索引等值查询并且查询记录不存在的场景下，仅靠间隙锁就能避免幻读的问题。

唯一索引范围查询

表是上面的样子，分析下面几种情况 next-key lock 会如何退化：

1、大于or大于等于情况：

• 针对「大于」的范围查询的情况。

  mysql> begin;
  mysql> select * from user where id > 15 for update;
  结果显示id为 20 的记录
  

  两个next-key lock。

  

• 针对「大于等于」的范围查询的情况。

  mysql> begin;
  mysql> select * from user where id >= 15 for update;
  结果显示id为 15 和 20 的记录
  

  一个X记录锁，两个X next-key lock。

  

2、小于or小于等于的情况：

• 针对「小于」或「小于等于」的范围查询时，查询条件值的记录「不存在」表中的情况。

  mysql> begin;
  mysql> select * from user where id < 6 for update;
  结果显示id为 1 和 5 的记录
  

  两个 next-key lock，一个间隙锁（注意 间隙锁的范围不是（5,6），而是（5,10））

  

• 针对「小于等于」的范围查询时，查询条件值的记录「存在」表中的情况。

  mysql> begin;
  mysql> select * from user where id <= 5 for update;
  结果显示id为 1 和 5 的记录
  

  两个x型next-key lock。

• 针对「小于」的范围查询时，查询条件值的记录「存在」表中的情况。

  select * from user where id < 5 for update;
  

  一个next-key lock，一个间隙锁。

  

非唯一索引等值查询

表如下，age是非唯一索引，非唯一索引会存在等值的情况，即多个记录的 age 相同

当我们用非唯一索引进行等值查询的时候，因为存在两个索引，一个是主键索引，一个是非唯一索引（二级索引），所以在加锁时，同时会对这两个索引都加锁，但是对主键索引加锁的时候，只有满足查询条件的记录才会对它们的主键索引加锁。

1、查询记录不存在：

• 针对非唯一索引等值查询时，查询的值不存在的情况。

  mysql> begin;
  mysql> select * from user where age = 25 for update;
  输出为空
  

  插入一个间隙锁

  

  问题：当有一个事务持有二级索引的间隙锁 (22, 39) 时，什么情况下，可以让其他事务的插入 age = 22 或者 age = 39 记录的语句成功？又是什么情况下，插入 age = 22 或者 age = 39 记录时的语句会被阻塞？

2、记录存在的情况：

• 针对非唯一索引等值查询时，查询的值存在的情况。

  mysql> begin;
  mysql> select * from user where age = 22 for update;
  显示id为 10 的记录
  

  加三把锁，x型next-key lock、x记录锁（加在主键索引上）、x间隙锁：

这里要好好理解。mysql 的间隙锁和next-key lock都是为了在可重复读隔离级别下解决幻读现象。(21,22]这个next-key lock是为了防止插入 id<=10 但是 age = 22 的数据；(22,39)的间隙锁是为了防止插入 id>=10 但是 age = 22 的数据，但是间隙锁只能加在两条记录之间，所以是 22 到 39 之间。

非唯一索引范围查询

非唯一索引和主键索引的范围查询的加锁也有所不同，不同之处在于非唯一索引范围查询，索引的 next-key lock 不会有退化为间隙锁和记录锁的情况，也就是非唯一索引进行范围查询时，对二级索引记录加锁都是加 next-key 锁。

表如下：

mysql> begin;
mysql> select * from user where age >= 22  for update;
显示id为 10和20 的两条记录

非唯一索引加3把next-key锁（非唯一索引范围查询只能加next-key lock），主键索引加2把记录锁：

没有加索引的查询（很严重）

如果锁定读查询语句（快照读不会），没有使用索引列作为查询条件，或者查询语句没有走索引查询，导致扫描是全表扫描。那么，每一条记录的索引上都会加 next-key 锁，这样就相当于锁住的全表，这时如果其他事务对该表进行增、删、改操作的时候，都会被阻塞。

不只是锁定读查询语句不加索引才会导致这种情况，update 和 delete 语句如果查询条件不加索引，那么由于扫描的方式是全表扫描，于是就会对每一条记录的索引上都会加 next-key 锁，这样就相当于锁住的全表。

因此，在线上在执行 update、delete、select … for update 等具有加锁性质的语句，一定要检查语句是否走了索引，如果是全表扫描的话，会对每一个索引加 next-key 锁，相当于把整个表锁住了，这是挺严重的问题。

4.5 update 没加索引会锁全表？

update 表名 set 字段=xxx where 索引名=yyy

如果上述语句 where 后跟的不是索引列，而是一个普通列并且没有创建普通索引，会出现什么情况？会出现相当于给全表加锁的情况（但是不等价于给全表加锁）。具体见原文

4.6 MySQL 死锁了，怎么办？

1 死锁的发生

本次案例使用存储引擎 Innodb，隔离级别为可重复读（RR）。接下来，我用实战的方式来带大家看看死锁是怎么发生的。我建了一张订单表，其中 id 字段为主键索引，order_no 字段普通索引，也就是非唯一索引：

CREATE TABLE `t_order` (
  `id` int NOT NULL AUTO_INCREMENT,
  `order_no` int DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_order` (`order_no`) USING BTREE   # 普通索引
) ENGINE=InnoDB ;

然后，先 t_order 表里现在已经有了 6 条记录：

假设这时有两事务，一个事务要插入订单 1007 ，另外一个事务要插入订单 1008，因为需要对订单做幂等性校验，所以两个事务先要查询该订单是否存在，不存在才插入记录，过程如下：

个人分析加锁：事务A的第一个查询操作会对二级索引 order_no 加 间隙锁，范围（1006，+oo）。事务B的第一个查询操作会加 间隙锁，范围（1006，+oo）。详情见 4.4的4的非唯一索引等值查询，next-key lock 会退化为间隙锁。因为间隙锁可以兼容，所以两个事务都可以获取 （1006，+oo）的间隙锁，详见4.3的2。

⚠️上面分析是不对的，不是间隙锁，如果有最后一个记录order_no>1008上面才加的是间隙锁，如果查询的不存在的是在最后一条记录到+oo，如上情况，那么就是 next-key lock。见下一小节。（我认为是间隙锁的依据 见 4.4的3的非唯一索引等值查询）

⚠️ 至于为什么两个 next-key lock 是兼容的，就要见原文的这个问题：为什么间隙锁与间隙锁之间是兼容的？

当A要执行插入操作时，会发现B在这个间隙有间隙锁，所以阻塞。B插入时同理。

可以看到，两个事务都陷入了等待状态（前提没有打开死锁检测），也就是发生了死锁，因为都在相互等待对方释放锁。这里在查询记录是否存在的时候，使用了 select ... for update 语句，目的为了防止事务执行的过程中，有其他事务插入了记录，而出现幻读的问题。

如果没有使用 select ... for update 语句，而使用了单纯的 select 语句，如果是 两个订单号一样 的请求同时进来，就会出现两个重复的订单（订单号一样），有可能出现幻读。

注意与java并发中的线程死锁联系。

2 为什么会产生死锁？

可重复读隔离级别下，是存在幻读的问题。Innodb 引擎为了解决「可重复读」隔离级别下的幻读问题，就引出了 next-key 锁，它是记录锁和间隙锁的组合。

普通的 select 语句是不会对记录加锁的，因为它是通过 MVCC 的机制实现的快照读，如果要在查询时对记录加行锁，可以使用下面这两个方式：

begin;
//对读取的记录加共享锁
select ... lock in share mode;
commit; //锁释放

begin;
//对读取的记录加排他锁
select ... for update;
commit; //锁释放

行锁的释放时机是在事务提交（commit）后，锁就会被释放，并不是一条语句执行完就释放行锁。比如，下面事务 A 查询语句会锁住 (2, +∞] 范围的记录，然后期间如果有其他事务在这个锁住的范围插入数据就会被阻塞。

需要注意的是，如果 update 语句的 where 条件没有用到索引列，那么就会全表扫描，在一行行扫描的过程中，不仅给行记录加上了行锁，还给行记录两边的空隙也加上了间隙锁，相当于锁住整个表，然后直到事务结束才会释放锁。所以在线上千万不要执行没有带索引条件的 update 语句，不然会造成业务停滞

回到上一节死锁的例子。

事务 A 在执行下面这条语句的时候：

select id from t_order where order_no = 1007 for update;

可以通过 select * from performance_schema.data_locks\G; 这条语句，查看事务执行 SQL 过程中加了什么锁。从原文的分析可知，共加了两个锁，分别是：

• 表锁：X 类型的意向锁；
• 行锁：X 类型的间隙锁，行锁重点关注。并且是 next-key 锁（既是间隙锁，又是记录锁）

因此，此时事务 A 在二级索引（INDEX_NAME : index_order）上加的是 X 型的 next-key 锁，锁范围是(1006, +∞]。（范围分析见原文）

当事务 B 往事务 A next-key 锁的范围 (1006, +∞] 里插入 id = 1008 的记录就会被锁住：

Insert into t_order (order_no, create_date) values (1008, now());

因为当我们执行上述插入语句时，会在插入间隙上获取插入意向锁，而插入意向锁与间隙锁（事务A查询语句生成的）是冲突的，所以当其它事务持有该间隙的间隙锁时，需要等待其它事务释放间隙锁之后，才能获取到插入意向锁。而间隙锁与间隙锁之间是兼容的，所以所以两个事务中 select ... for update 语句并不会相互影响。

案例中的事务 A 和事务 B 在执行完后 select ... for update 语句后都持有范围为(1006,+∞]的next-key 锁，而接下来的插入操作为了获取到插入意向锁，都在等待对方事务的间隙锁释放，于是就造成了循环等待，导致死锁。

为什么间隙锁与间隙锁之间是兼容的？

• 间隙锁的意义只在于阻止区间被插入，因此是可以共存的。一个事务获取的间隙锁不会阻止另一个事务获取同一个间隙范围的间隙锁，共享和排他的间隙锁是没有区别的，他们相互不冲突，且功能相同，即两个事务可以同时持有包含共同间隙的间隙锁。
• 但是有一点要注意，next-key lock 是包含间隙锁+记录锁的，记录锁分为 S 和 X。如果一个事务获取了 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，是会被阻塞的。

为什么上面事务 A 和 B 都是 x 型 next-key lock，持有范围都是(1006,+∞]，但是查询语句却没有阻塞呢？

• 对于这种范围为 (1006, +∞] 的 next-key lock，两个事务是可以同时持有的，不会冲突。因为 +∞ 并不是一个真实的记录，自然就不需要考虑 X 型与 S 型关系。

3 Insert 语句是怎么加行级锁的？

Insert 语句在正常执行时是不会生成锁结构的，它是靠聚簇索引记录自带的 trx_id 隐藏列来作为隐式锁来保护记录的。

什么是隐式锁？当事务需要加锁的时，如果这个锁不可能发生冲突，InnoDB会跳过加锁环节，这种机制称为隐式锁。隐式锁是 InnoDB 实现的一种延迟加锁机制，其特点是只有在可能发生冲突时才加锁，从而减少了锁的数量，提高了系统整体性能。

隐式锁就是在 Insert 过程中不加锁，只有在特殊情况下，才会将隐式锁转换为显示锁，这里我们列举两个场景。

• 如果记录之间加有间隙锁，为了避免幻读，此时是不能插入记录的。如果事务A对区间生成了间隙锁，事务B准备在间隙之间插入数据的时候，插入操作就会生成一个插入意向锁（显示锁），锁的状态是等待状态，意味着事务 B 并没有成功获取到插入意向锁，因此事务 B 发生阻塞。
• 如果 Insert 的记录和已有记录存在 唯一键 冲突，此时也不能插入记录。即如果在插入新记录时，插入了一个与「已有的记录的主键或者唯一二级索引列值相同」的记录，此时插入就会失败，然后对于这条记录加上了 S 型的锁。
  • 如果主键索引重复，插入新记录的事务会给已存在的主键值重复的聚簇索引记录添加 S 型记录锁。
  • 如果唯一二级索引重复，插入新记录的事务都会给已存在的二级索引列值重复的二级索引记录添加 S 型 next-key 锁。

上面第二点不明白可以看原文MySQL 死锁了，怎么办？ | 小林coding (xiaolincoding.com)，有3个案例。

总结：Insert 语句在正常执行时是不会生成锁结构的，它是靠聚簇索引记录自带的 trx_id 隐藏列来作为隐式锁来保护记录的。在某些情况（发生冲突） insert 会将隐式锁转换为显示锁，转换成的显示锁可以是 插入意向锁、S型锁（记录锁、next-key lock0）。

分析一个场景：分析两个事务执行过程中，执行了相同的 insert 语句的场景。现在 t_order 表中，只有这些数据，order_no 为唯一二级索引。

在隔离级别可重复读的情况下，开启两个事务，前后执行相同的 Insert 语句，此时事务 B 的 Insert 语句会发生阻塞。

两个事务的加锁过程：

• 事务 A 先插入 order_no 为 1006 的记录，可以插入成功，此时对应的唯一二级索引记录被「隐式锁」保护，此时还没有实际的锁结构（执行完这里的时候，你可以看查 performance_schema.data_locks 信息，可以看到这条记录是没有加任何锁的）；
• 接着，事务 B 也插入 order_no 为 1006 的记录，由于事务 A 已经插入 order_no 值为 1006 的记录，所以事务 B 在插入二级索引记录时会遇到重复的唯一二级索引列值，此时事务 B 想获取一个 S 型 next-key 锁，但是事务 A 并未提交，事务 A 插入的 order_no 值为 1006 的记录上的「隐式锁」会变「显示锁」且锁类型为 X 型的记录锁（事务A的隐式锁变为显示锁），所以事务 B 想获取 S 型 next-key 锁（事务B加的）时会遇到锁冲突，事务 B 进入阻塞状态（锁状态为等待状态）。

如果 order_no 不是唯一二级索引，那么两个事务，前后执行相同的 Insert 语句，是不会发生阻塞的，就如前面的这个例子。

⚠️注意哈，上面两个事务是普通查询，是不会加锁的。在可重复读隔离级别下，通过MVCC来避免幻读。并且 因为是快照读（普通select），所以即使 where 后面没有索引，也不会锁整个表，如果是 select id from t_order where order_no = 1007 for update 并且 order_no 不是索引时，就会对每条记录加 next-key lock，相当于锁住全表（详见4.4最后）。

4 如何避免死锁？

死锁的四个必要条件：互斥、占有且等待、不可强占用、循环等待。只要系统发生死锁，这些条件必然成立，但是只要破坏任意一个条件就死锁就不会成立。

在数据库层面，有两种策略通过「打破循环等待条件」来解除死锁状态：

• 设置事务等待锁的超时时间。当一个事务的等待时间超过该值后，就对这个事务进行回滚，于是锁就释放了，另一个事务就可以继续执行了。在 InnoDB 中，参数 innodb_lock_wait_timeout 是用来设置超时时间的，默认值时 50 秒。

  当发生超时后，就出现下面这个提示：

• 开启主动死锁检测。主动死锁检测在发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑，默认就开启。

  当检测到死锁后，就会出现下面这个提示：

上面这个两种策略是「当有死锁发生时」的避免方式。

我们可以回归业务的角度来预防死锁，对订单做幂等性校验的目的是为了保证不会出现重复的订单，那我们可以直接将 order_no 字段设置为唯一索引列，利用它的唯一性来保证订单表不会出现重复的订单，不过有一点不好的地方就是在我们插入一个已经存在的订单记录时就会抛出异常。

4.7 字节面试：加了什么锁，导致死锁的？（重点）

如果对 MySQL 加锁机制比较熟悉的同学，应该一眼就能看出会发生死锁，但是具体加了什么锁而导致死锁，是需要我们具体分析的。

1 答案

本次案例中，事务 A 和事务 B 在执行完后 update 语句后都持有范围为(20, 30）的间隙锁，而接下来的插入操作为了获取到插入意向锁，都在等待对方事务的间隙锁释放，于是就造成了循环等待，满足了死锁的四个条件：互斥、占有且等待、不可强占用、循环等待，因此发生了死锁。

2 分析

先准备一个表，与上述问题表结构一样，只是 id 是主键。启动两个事务，按照题目的 SQL 执行顺序，过程如下表格：

可以看到，事务 A 和 事务 B 都在执行 insert 语句后，都陷入了等待状态（前提没有打开死锁检测），也就是发生了死锁，因为都在相互等待对方释放锁。

• Time 1 阶段：事务 A 在主键索引（INDEX_NAME : PRIMARY）上加的是 间隙锁（不是 next-key lock，会退化），锁范围是(20, 30)。

• Time 2 阶段：事务 B 在主键索引（INDEX_NAME : PRIMARY）上加的也是 间隙锁（不是 next-key lock，会退化），锁范围是(20, 30)。

  事务 A 和 事务 B 的间隙锁范围都是一样的，为什么不会冲突？

  • 间隙锁不冲突。间隙锁的意义只在于阻止区间被插入，因此是可以共存的。一个事务获取的间隙锁不会阻止另一个事务获取同一个间隙范围的间隙锁。但是 next-key lock 是会冲突的，详见 4.6 的 2（next-key lock也不是完全会冲突）。

• Time 3 阶段：事务 A 执行插入操作后，就陷入了等待状态（阻塞）。因为事务 A 向事务 B 生成的间隙锁（范围 (20, 30)）中插入了一条记录，所以事务 A 的插入操作生成了一个插入意向锁（锁状态为等待状态）。

  插入意向锁是什么？

  • 插入意向锁不是意向锁，它属于行级锁，是一种特殊的间隙锁。但不同于间隙锁的是，该锁只用于并发插入操作。两个事务却不能在同一时间内，一个拥有间隙锁，另一个拥有该间隙区间内的插入意向锁（当然，插入意向锁如果不在间隙锁区间内则是可以的）。所以，插入意向锁和间隙锁之间是冲突的，所以上面的事务 A 的插入操作会阻塞。

• Time 4 阶段：事务 B 执行插入操作后，就陷入了等待状态（阻塞）。因为事务 B 向事务 A 生成的间隙锁（范围 (20, 30)）中插入了一条记录，所以事务 B 的插入操作生成了一个插入意向锁（锁状态为等待状态）。

3 总结

两个事务即使生成的间隙锁的范围是一样的，也不会发生冲突，因为间隙锁目的是为了防止其他事务插入数据，因此间隙锁与间隙锁之间是相互兼容的。

在执行插入语句时，如果插入的记录在其他事务持有间隙锁范围内，插入语句就会被阻塞，因为插入语句在碰到间隙锁时，会生成一个插入意向锁，然后插入意向锁和间隙锁之间是互斥的关系。

如果两个事务分别向对方持有的间隙锁范围内插入一条记录，而插入操作为了获取到插入意向锁，都在等待对方事务的间隙锁释放，于是就造成了循环等待，满足了死锁的四个条件：互斥、占有且等待、不可强占用、循环等待，因此发生了死锁。