【MYSQL】MYSQL 的学习教程（十）之 InnoDB 锁

1. 为什么需要加锁呢？

数据库为什么需要加锁呢？

如果有多个并发请求存取数据，在数据就可能会产生多个事务同时操作同一行数据。如果并发操作不加控制，不加锁的话，就可能写入了不正确的数据，或者导致读取了不正确的数据，破坏了数据的一致性。因此需要考虑加锁。

1.1 事务并发存在的问题

• 脏读：一个事务 A 读取到事务 B 未提交的数据
• 不可重复读：事务 A 被事务 B 干扰到了！在事务 A 范围内，两个相同的查询，读取同一条记录，却返回了不同的数据（事务 B 修改了数据）
• 幻读：事务 A 查询一个范围的结果集，另一个并发事务 B 往这个范围中插入/删除了数据，并静悄悄地提交，然后事务 A 再次查询相同的范围，两次读取得到的结果集不一样了

1.2 一个加锁和不加锁对比的例子

MySQL数据库有四大隔离级别：

1. 读未提交（脏读）：该隔离级别的事务可以看到其他事务中未提交的数据。该隔离级别因为可以读取到其他事务中未提交的数据，而未提交的数据可能会发生回滚，因此我们把该级别读取到的数据称之为脏数据，把这个问题称之为脏读
2. 读已提交（不可重复读）：该隔离级别的事务能读取到已经提交事务的数据，因此它不会有脏读问题。但由于在事务的执行中可以读取到其他事务提交的结果，所以在不同时间的相同 SQL 查询中，可能会得到不同的结果，这种现象叫做不可重复读
3. 可重复读（MySQL 的默认事务隔离级别：幻读）：它能确保同一事务多次查询的结果一致。但也会有新的问题，比如此级别的事务正在执行时，另一个事务成功的插入了某条数据，两次查询结果记录条数不一样
4. 序列化：事务最高隔离级别，它会强制事务排序，使之不会发生冲突，从而解决了脏读、不可重复读和幻读问题，但因为执行效率低，所以真正使用的场景并不多

在 READ-UNCOMMITTED（读未提交） 隔离级别下，假设现在有两个事务 A、B：

• 假设现在 Jay 的余额是 100，事务 A 正在准备查询Jay的余额
• 这时候，事务 B 先扣减 Jay 的余额，扣了 10
• 最后 A 读到的是扣减后的余额

流程如下：

由上图可以发现，事务 A、B 交替执行，事务 A 被事务 B 干扰到了，因为事务 A 读取到事务 B 未提交的数据，这就是脏读。

为什么存在脏读问题呢？这是因为在读未提交的隔离级别下执行写操作，并没有对 SQL 加锁，因此产生了脏读这个问题

再来看下，在串行化隔离级别下，同样的 SQL 执行流程，又是怎样的呢？

为啥会阻塞等待超时呢？这是因为串行化隔离级别下，对写的 SQL 加锁啦。

我们可以再看下加了什么锁，命令如下：

SET GLOBAL innodb_status_output=ON; – 开启输出
SET GLOBAL innodb_status_output_locks=ON; – 开启锁信息输出
SHOW ENGINE INNODB STATUS

锁相关的输出内容如下：

我们可以看到了这么一把锁：lock_mode X locks rec but not gap，它到底是一种什么锁呢。继续往下看

2. InnoDB 的锁介绍

2.1 共享/排他锁

InnoDB 实现了两种标准的行级锁：共享锁（简称 S 锁）、排他锁（简称 X 锁）

• 共享锁：简称为 S 锁，在事务要读取一条记录时，需要先获取该记录的 S 锁
• 排他锁：简称 X 锁，在事务需要改动一条记录时，需要先获取该记录的 X 锁

如果事务 T1 持有行 R 的 S 锁，那么另一个事务 T2 请求访问这条记录时，会做如下处理：

1. T2 请求 S 锁立即被允许，结果 T1和T2都持有R行的S锁
2. T2 请求 X 锁不能被立即允许，此操作会阻塞

如果 T1 持有行 R 的 X 锁，那么 T2 请求 R 的 X、S 锁都不能被立即允许，T2 必须等待 T1 释放 X 锁才可以，因为 X 锁与任何的锁都不兼容

S 锁和 X 锁的兼容关系如下图表格：

X 锁和 S 锁是对于行记录来说的话，可以称它们为行级锁或者行锁。我们认为行锁的粒度就比较细，其实一个事务也可以在表级别下加锁，对应的，我们称之为表锁。给表加的锁，也是可以分为 X 锁和 S 锁的

如果一个事务给表已经加了 S 锁，则：

• 别的事务可以继续获得该表的 S 锁，也可以获得该表中某些记录的 S 锁
• 别的事务不可以继续获得该表的 X 锁，也不可以获得该表中某些记录的 X 锁

如果一个事务给表加了 X 锁，那么：

• 别的事务不可以获得该表的 S 锁，也不可以获得该表某些记录的 S 锁。
• 别的事务不可以获得该表的 X 锁，也不可以继续获得该表某些记录的 X 锁。

2.2 意向锁

意向锁是一种不与行级锁冲突的表级锁（本质上就是空间换时间）。未来的某个时刻，事务可能要加共享或者排它锁时，先提前声明一个意向

为什么要加共享锁或排他锁时的时候，需要提前声明个意向锁呢呢？

因为 InnoDB 是支持表锁和行锁共存的。如果一个事务 A 获取到某一行的排他锁，并未提交，这时候事务 B 请求获取同一个表的表共享锁。由于共享锁和排他锁是互斥的，因此事务 B 想对这个表加共享锁时，需要保证没有其他事务持有这个表的表排他锁，同时还要保证没有其他事务持有表中任意一行的排他锁
然后问题来了：你要保证没有其他事务持有表中任意一行的排他锁的话，去遍历每一行？这样显然是一个效率很差的做法。为了解决这个问题，InnoDB 的设计者提出了意向锁

意向锁是如何解决这个问题的呢？来看下。

意向锁分为两类：

• 意向共享锁：简称 IS 锁，当事务准备在某些记录上加 S 锁时，需要现在表级别加一个 IS 锁
• 意向排他锁：简称 IX 锁，当事务准备在某条记录上加上 X 锁时，需要现在表级别加一个 IX 锁

比如：

• select ... lock in share mode：要给表设置 IS 锁
• select ... for update：要给表设置 IX 锁

意向锁又是如何解决这个效率低的问题呢？

如果一个事务 A 获取到某一行的排他锁，并未提交，这时候表上就有意向排他锁和这一行的排他锁。这时候事务 B 想要获取这个表的共享锁，此时因为检测到事务 A 持有了表的意向排他锁，因此事务 A 必然持有某些行的排他锁，也就是说事务B对表的加锁请求需要阻塞等待，不再需要去检测表的每一行数据是否存在排他锁啦。这样效率就高很多啦。

意向锁仅仅表明意向的锁，意向锁之间并不会互斥，是可以并行的，整体兼容性如下图所示：

2.3 记录锁（Record Lock）

记录锁是最简单的行锁，仅仅锁住一行

如：

SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE

如果 c1 字段是主键或者是唯一索引的话，这个 SQL 会加一个记录锁（Record Lock）

记录锁永远都是加在索引上的，即使一个表没有索引，InnoDB 也会隐式的创建一个索引，并使用这个索引实施记录锁。它会阻塞其他事务对这行记录的插入、更新、删除。

一般我们看死锁日志时，都是找关键词，比如 lock_mode X locks rec but not gap，就表示一个 X 型的记录锁。记录锁的关键词就是 rec but not gap。以下就是一个记录锁的日志：

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t` 
trx id 10078 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;;

2.4 间隙锁（Gap Lock）

为了解决幻读问题，InnoDB 引入了间隙锁（Gap Lock）。间隙锁是一种加在两个索引之间的锁，或者加在第一个索引之前，或最后一个索引之后的间隙。它锁住的是一个区间，而不仅仅是这个区间中的每一条数据

假如 user 表中只有 101 条记录， 其userid 的值分别是 1,2,…,100,101， 下面的 SQL： select * from user where userid > 100 for update;是一个范围条件的检索，InnoDB 不仅会对符合条件的 userid 值为 101 的记录加锁，也会对userid 大 于 101（但是这些记录并不存在）的"间隙"加锁，防止其它事务在表的末尾增加数据（其它事务无法插入 userid 大于 100 的任何记录）

比如 lock_mode X locks gap before rec 表示 X 型 gap 锁。以下就是一个间隙锁的日志：

RECORD LOCKS space id 177 page no 4 n bits 80 index idx_name of table `test2`.`account` 
trx id 38049 lock_mode X locks gap before rec
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 3; hex 576569; asc Wei;;
 1: len 4; hex 80000002; asc     ;;

2.5 临键锁（Next-Key Lock）

Next-key 锁是记录锁和间隙锁的组合，它指的是加在某条记录以及这条记录前面间隙上的锁。说得更具体一点就是：临键锁会封锁索引记录本身，以及索引记录之前的区间，即它的锁区间是前开后闭，比如 (5,10]

如果一个会话占有了索引记录 R 的共享/排他锁，其他会话不能立刻在 R 之前的区间插入新的索引记录

2.6 插入意向锁

插入意向锁是插入一行记录操作之前设置的一种间隙锁。这个锁释放了一种插入方式的信号。它解决的问题是：多个事务，在同一个索引，同一个范围区间插入记录时，如果插入的位置不冲突，就不会阻塞彼此

假设有索引值 4、7，几个不同的事务准备插入 5、6，每个事务都在获得插入行的独占锁之前用插入意向锁各自锁住了 4、7 之间的间隙，但是不阻塞对方因为插入行不冲突

以下就是一个插入意向锁的日志：

RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child`
trx id 8731 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000066; asc    f;;
 1: len 6; hex 000000002215; asc     " ;;
 2: len 7; hex 9000000172011c; asc     r  ;;...

锁模式兼容矩阵（横向是已持有锁，纵向是正在请求的锁）如下：

2.7 自增锁

自增锁是一种特殊的表级别锁。 它是专门针对 AUTO_INCREMENT 类型的列，对于这种列，如果表中新增数据时，InnoDB 就会去持有此表得自增锁，添加完数据后，自增锁会自动释放。简言之，如果一个事务正在往表中插入记录，所有其他事务的插入必须等待，以便第一个事务插入的行，是连续的主键值

假设有表结构以及自增模式是 1，如下：

mysql> create table t0 (id int NOT NULL AUTO_INCREMENT,name varchar(16),primary key ( id));

mysql> show variables like '%innodb_autoinc_lock_mode%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 1     |
+--------------------------+-------+
1 row in set, 1 warning (0.01 sec)

设置事务A和B交替执行流程如下：

通过上图我们可以看到，当我们在事务 A 中进行自增列的插入操作时，另外会话事务 B 也进行插入操作，这种情况下会发生2个奇怪的现象：

• 事务A会话中的自增列好像直接增加了2个值。（如上图中步骤7、8）
• 事务B会话中的自增列直接从2开始增加的。（如上图步骤5、6）

自增锁是一个表级别锁，那为什么会话A事务还没结束，事务会话B可以执行插入成功呢？不是应该锁表嘛？

这是因为在参数 innodb_autoinc_lock_mode 上，这个参数设置为 1 的时候，相当于将这种 auto_inc lock 弱化为了一个更轻量级的互斥自增长机制去实现，官方称之为 mutex。

innodb_autoinc_lock_mode 还可以设置为 0 或者 2：

• 0：传统锁模式，使用表级AUTO_INC锁。一个事务的 INSERT-LIKE语句在语句执行结束后释放 AUTO_INC 表级锁，而不是在事务结束后释放
• 1：连续锁模式，连续锁模式对于 Simple inserts 不会使用表级锁，而是使用一个轻量级锁来生成自增值，因为 InnoDB 可以提前直到插入多少行数据。自增值生成阶段使用轻量级互斥锁来生成所有的值，而不是一直加锁直到插入完成。对于 bulk inserts 类语句使用 AUTO_INC 表级锁直到语句完成
• 2：交错锁模式，所有的INSERT-LIKE语句都不使用表级锁，而是使用轻量级互斥

• INSERT-LIKE:指所有的插入语句，包括：INSERT、REPLACE、INSERT…SELECT、REPLACE…SELECT,LOAD DATA等
• Simple inserts:指在插入前就能确定插入行数的语句，包括：INSERT、REPLACE，不包含INSERT…ON DUPLICATE KEY UPDATE这类语句
• Bulk inserts: 指在插入钱不能确定行数的语句，包括：INSERT … SELECT/REPLACE … SELECT/LOAD DATA

3. 一条SQL是如何加锁的呢？

介绍完 InnoDB 的七种锁后，我们来看下一条 SQL 是如何加锁的哈，现在可以分 9 种情况进行：

• 查询条件是主键，RC 隔离级别
• 查询条件是唯一索引，RC 隔离级别
• 查询条件是普通索引，RC 隔离级别
• 查询条件上没有索引，RC 隔离级别
• 查询条件是主键，RR 隔离级别
• 查询条件是唯一索引，RR 隔离级别
• 查询条件是普通索引，RR 隔离级别
• 查询条件上没有索引，RR 隔离级别
• Serializable 隔离级别

3.1 查询条件是主键，RC 隔离级别

给定 SQL：id 是主键

delete from t1 where id = 6;

在RC（读已提交） 的隔离级别下，对查询条件是主键 id 的场景，会加一个排他锁（X锁），或者说加一个 X 型的记录锁

查询条件是唯一索引，RC 隔离级别

给定 SQL：id 是唯一索引，name 是主键

delete from t2 where id = 6;

在 RC 隔离级别下，该 SQL 需要加两个X锁：一个对应于 id 唯一索引上的 id = 6 的记录；另一把锁对应于聚簇索引上的 [name=’b’,id=6] 的记录

为什么主键索引上的记录也要加锁呢？

如果并发的一个 SQL，是通过主键索引来更新：update t2 set id = 666 where name = 'b'; 此时，如果 delete 语句没有将主键索引上的记录加锁，那么并发的 update 就会感知不到 delete 语句的存在，违背了同一记录上的更新/删除需要串行执行的约束

3.3 查询条件是普通索引，RC隔离级别

若 id 列是普通索引，那么对应的所有满足 SQL 查询条件的记录，都会加上锁。同时，这些记录对应主键索引，也会上锁

3.4 查询条件列无索引，RC隔离级别

若 id 列上没有索引，MySQL 会走聚簇索引进行全表扫描过滤。每条记录都会加上 X 锁。但是，为了效率考虑，MySQL 在这方面进行了改进，在扫描过程中，若记录不满足过滤条件，会进行解锁操作。同时优化违背了2PL原则

3.5 查询条件是主键，RR 隔离级别

给定SQL：id 是主键

delete from t1 where id = 6;

在 RR 隔离级别下，跟 RC 隔离级别加锁是一样的：在 id = 6 这条记录上加上 X 锁

3.6 查询条件是唯一索引，RR 隔离级别

给定SQL：id 是唯一索引

delete from t1 where id = 6;

同 RC 级别：加了两个 X 锁，id 唯一索引满足条件的记录上一个，对应的主键索引上的记录一个

3.7 查询条件是普通索引，RR隔离级别

给定 SQL：c 是普通索引

update t5 set d = d + 1 where c = 10

在RR（可重复读的隔离级别下），除了会加 X 锁，还会加间隙 Gap 锁。Gap 锁的提出，是为了解决幻读问题引入的，它是一种加在两个索引之间的锁

c=10 这个记录更新时，不仅会有两把 X 锁，还会把区间 （10,15） 加间隙锁，因此要插入 （12,12,12） 记录时，会阻塞

3.8 查询条件列无索引，RR 隔离级别

给定 SQL：c 是普通索引

update t5 set d = d + 1 where c = 10

如果查询条件列没有索引，主键索引的所有记录，都将加上 X 锁，每条记录间也都加上间隙 Gap 锁。大家可以想象一下，任何加锁并发的 SQL，都是不能执行的，全表都是锁死的状态。如果表的数据量大，那效率就更低

在这种情况下，MySQL 做了一些优化，即 semi-consistent read，对于不满足条件的记录，MySQL 提前释放锁，同时 Gap 锁也会释放。而 semi-consistent read 是如何触发的呢：要么在 Read Committed 隔离级别下；要么在 Repeatable Read 隔离级别下，设置了 innodb_locks_unsafe_for_binlog 参数。但是 semi-consistent read 本身也会带来其他的问题，不建议使用。

3.9 Serializable 串行化

在 Serializable 串行化的隔离级别下，对于写的语句，比如 update account set balance= balance-10 where name=‘Jay’;，跟RC和RR隔离级别是一样的。不一样的地方是，在查询语句，如 select balance from account where name = ‘Jay’;，在 RC 和 RR 是不会加锁的，但是在 Serializable 串行化的隔离级别，即会加锁

4. RR 隔离级别下，加锁规则到底是怎样的呢？

对于 RC 隔离级别，加的排他锁（X锁），是比较好理解的，哪里更新就锁哪里嘛。但是 RR 隔离级别，间隙锁是怎么加的呢？

锁规则一共包括：两个原则、两个优化和一个 bug。

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

假设有表结构和数据如下：

CREATE TABLE t5 ( 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 t5 values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

分7个案例去分析哈：

• 等值查询间隙锁
• 非唯一索引等值锁
• 主键索引范围锁
• 非唯一索引范围锁
• 唯一索引范围锁 bug
• 普通索引上存在"等值"的例子
• limit 语句减少加锁范围

4.1 案例一：等值查询间隙锁

我们同时开启 A、B、C三个会话事务，如下：

发现事务 B 会阻塞等待，而 C 可以执行成功。如下：

为什么事务 B 会阻塞呢？

1. 因为根据加锁原则1：加锁基本单位是 next-key lock，因此事务会话 A 的加锁范围是 （5,10]，这里为什么是区间（5,10]，这是因为更新的记录，所在的表已有数据的区间就是 5-10 哈，又因为 next-key lock 是左开右闭的，所以加锁范围是（5,10]
2. 同时根据优化 2，这是一个等值查询 (id=6)，而id=10不满足查询条件。所以 next-key lock 退化成间隙 Gap 锁，因此最终加锁的范围是 (5,10)
3. 然后事务 Session B 中，你要插入的是 9，9 在区间 (5,10) 内，而区间 (5,10) 都被锁了。因此事务 B 会阻塞等到

为什么事务 C 可以正常执行呢？

这是因为锁住的区间是 (5,10)，没有包括 10，所以事务 C 可以正常执行。

4.2 案例二：非唯一索引等值锁

按顺序执行事务会话A、B、C，如下：

发现事务B可以执行成功，而 C 阻塞等待。如下：

为什么事务会话B没有阻塞，事务会话C却阻塞了？

事务会话A执行时，会给索引树 c=5 的这一行加上读共享锁。

1. 根据加锁原则1，加锁单位是 next-key lock，因此会加上 next-key lock(0,5]
2. 因为 c 只是普通索引，所以仅访问c=5这一条记录时不会马上停下来，需要继续向右遍历，查到c=10才结束。根据加锁原则2，访问到的都要加锁，因此要给 (5,10] 加 next-key lock
3. 由加锁优化2：等值判断，向右遍历，最后一个值 10 不满足 c = 5 这个等值条件，因此退化成间隙锁 (5,10)
4. 根据加锁原则 2 ：只有访问到的对象才会加锁，事务A的这个查询使用了覆盖索引，没有回表，并不需要访问主键索引，因此主键索引上没有加任何锁，事务会话 B 是对主键 id 的更新，因此事务会话 B 的 update 语句不会阻塞
5. 但是事务会话 C，要插入一个（6,6,6) 的记录时，会被事务会话A的间隙锁 (5,10) 锁住，因此事务会话 C 阻塞了

4.3 案例三：主键索引范围锁

按顺序执行事务会话A、B、C，如下：

执行结果如下：

发现事务会话 B 中，插入 12，即 insert into t5 values(12,12,12); 时，阻塞了，而插入 6，insert into t5 values(6,6,6); 却可以顺利执行。同时事务 C 中，Update t5 set d=d+1 where id =15; 也会阻塞，为什么呢？

事务会话 A 执行时，要找到第一个 id =10 的行：

1. 根据加锁原则1：加锁单位是 next-key lock，因此会加上 next-key lock(5,10]
2. 又因为 id 是主键，也就是唯一值，因此根据优化1：索引上的等值查询，给唯一索引加锁时，next-key lock 退化为行锁（Record lock）。所以只加了 id=10 这个行锁
3. 范围查找就往后继续找，找到 id=15 这一行停下来，因此还需要加 next-key lock(10,15]

事务会话 A 执行完后，加的锁是 id=10 这个行锁，以及临键锁 next-key lock(10,15]。这就是为什么事务 B 插入 6 那个记录可以顺利执行，插入12 就不行啦。同理，事务 C 那个更新 id=15 的记录，也是会被阻塞的

4.4 案例四：非唯一索引范围锁

按顺序执行事务会话 A、B、C，如下：

执行结果如下：

发现事务会话 B 和事务会话 C 的执行 SQL 都被阻塞了。

这是因为，事务会话 A 执行时，要找到第一个 c = 10 的行：

1. 根据加锁原则1：加锁单位是 next-key lock，因此会加上 next-key lock(5,10]。又因为 c 不是唯一索引，所以它不会退化为行锁。因此加的锁还是next-key lock(5,10]
2. 范围查找就往后继续找，找到 id=15 这一行停下来，因此还需要加 next-key lock(10,15]

因此事务B和事务C插入的 insert into t5 values(6,6,6); 和 Update t5 set d=d+1 where c =15; 都会阻塞

4.5 案例五：唯一索引范围锁 bug

按顺序执行事务会话A、B、C，如下：

事务会话 A 执行时，要找到第一个 id = 15 的行：

1. 根据加锁原则1：加锁单位是 next-key lock，因此会加上next-key lock(10,15]
2. 因为 id 是主键，即唯一的，因此循环判断到 id = 15 这一行就应该停止了
3. 根据一个 bug：InnoDB 会往前扫描到第一个不满足条件的行为止，直到扫描到 id = 20。而且由于这是个范围扫描，因此索引 id 上的 (15,20] 这个 next-key lock 也会被锁上

所以，事务 B 要更新 id = 20 这一行时，会阻塞锁住。同样地事务会话 C 要插入 id = 1 6的一行，也会被锁住

4.6 案例六：普通索引上存在"等值"的例子

插入：

insert into t5 values(28,10,66);

则 c 索引树如下：

c 索引值有相等的，但是它们对应的主键是有间隙的。比如（c=10，id=10）和（c=10，id=28）之间。

我们来看个例子，按顺序执行事务会话A、B、C，如下：

执行结果如下：

为什么事务B插入语句会阻塞，事务C的更新语句不会呢？

1. 这是因为事务会话A在遍历的时候，先访问第一个c=10的记录。它根据原则 1，加一个(c=5,id=5) 到 (c=10,id=10)的next-key lock
2. 然后，事务会话A向右查找，直到碰到 (c=15,id=15) 这一行，循环才结束。根据优化 2，这是一个等值查询，向右查找到了不满足条件的行，所以会退化成(c=10,id=10) 到 (c=15,id=15)的间隙Gap锁。即事务会话A这个 select...for update 语句在索引 c 上的加锁范围，就是下图灰色阴影部分的：

因为c=13是这个区间内的，所以事务B插入insert into t5 values(13,13,13);会阻塞。因为根据优化2，已经退化成 (c=10,id=10) 到 (c=15,id=15) 的间隙Gap锁,即不包括c=15，所以事务C，Update t5 set d=d+1 where c=15不会阻塞

4.7 案例七：limit 语句减少加锁范围

Select * from t5 where c=10 limit 2 for update;

事务A、B执行如下：

发现事务B并没有阻塞，而是可以顺利执行：

这是为什么呢？跟上个例子，怎么事务会话B的SQL却不会阻塞了，事务会话A的select只是加多了一个limit 2

这是因为明确加了limit 2的限制后，因此在遍历到 (c=10, id=30) 这一行之后，满足条件的语句已经有两条，循环就结束了。因此，索引 c上的加锁范围就变成了从（c=5,id=5) 到（c=10,id=30) 这个前开后闭区间，如下图所示：

索引平时我们写SQL的时候，比如查询select或者delete语句时，尽量加一下limit哈，你看着这个例子不就减少了锁范围了嘛

5. 如何查看事务加锁情况

怎么查看执行中的 SQL 加了什么锁呢？或者换个说法，如何查看事务的加锁情况呢？有这两种方法：

• 使用 infomation_schema 数据库中的表获取锁信息
• 使用 show engine innodb status 命令

5.1 使用 infomation_schema 数据库中的表获取锁信息

infomation_schema 数据库中，有几个表跟锁紧密关联的。

• INNODB_TRX：该表存储了InnoDB当前正在执行的事务信息，包括事务id、事务状态（比如事务是在运行还是在等待获取某个所）等
• INNODB_LOCKS：该表记录了一些锁信息，包括两个方面：1.如果一个事务想要获取某个锁，但未获取到，则记录该锁信息。2. 如果一个事务获取到了某个锁，但是这个锁阻塞了别的事务，则记录该锁信息
• INNODB_LOCK_WAITS：表明每个阻塞的事务是因为获取不到哪个事务持有的锁而阻塞

5.1.1 INNODB_TRX

我们在一个会话中执行加锁的语句，在另外一个会话窗口，即可查看 INNODB_TRX 的信息啦，如下：

表中可以看到一个事务id为1644837正在运行汇中，它的隔离级别为REPEATABLE READ。我们一般关注这几个参数：

• trx_tables_locked：该事务当前加了多少个表级锁。
• trx_rows_locked：表示当前加了多少个行级锁。
• trx_lock_structs：表示该事务生成了多少个内存中的锁结构。

5.1.2 INNODB_LOCKS

一般系统中，发生某个事务因为获取不到锁而被阻塞时，该表才会有记录

事务A、B执行如下：

使用 select * from information_schema.INNODB_LOCKS; 查看

可以看到两个事务Id 1644842和1644843都持有什么锁，就是看那个lock_mode和lock_type哈。但是并看不出是哪个锁在等待那个锁导致的阻塞，这时候就可以看 INNODB_LOCK_WAITS 表啦

5.1.3 INNODB_LOCK_WAITS

INNODB_LOCK_WAITS 表明每个事务是因为获取不到哪个事务持有的锁而阻塞

• requesting_trx_id：表示因为获取不到锁而被阻塞的事务的事务id
• blocking_trx_id：表示因为获取到别的事务需要的锁而导致其被阻塞的事务的事务Id。

即requesting_trx_id表示事务B的事务Id，blocking_trx_id表示事务A的事务Id

5.2 show engine innodb status

INNODB_LOCKS 和 INNODB_LOCK_WAITS 在 MySQL 8.0 已被移除，其实就是不鼓励我们用这两个表来获取表信息。而我们还可以用 show engine innodb status 获取当前系统各个事务的加锁信息

在看死锁日志的时候，我们一般先把这个变量 innodb_status_output_locks 打开哈，它是MySQL 5.6.16 引入的：

set global  innodb_status_output_locks =on;

在 RR 隔离级别下，我们交替执行事务 A 和 B：

show engine innodb status 查看日志，如下：

TRANSACTIONS
------------
Trx id counter 1644854
Purge done for trx's n:o < 1644847 undo n:o < 0 state: running but idle
History list length 32
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 283263895935640, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 1644853, ACTIVE 7 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 7, OS thread handle 11956, query id 563 localhost ::1 root update
insert into t5 values(6,6,6)
------- TRX HAS BEEN WAITING 7 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 267 page no 4 n bits 80 index c of table `test2`.`t5` trx id 1644853 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 4; hex 8000000a; asc     ;;

------------------
TABLE LOCK table `test2`.`t5` trx id 1644853 lock mode IX
RECORD LOCKS space id 267 page no 4 n bits 80 index c of table `test2`.`t5` trx id 1644853 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 4; hex 8000000a; asc     ;;

这结构锁的关键词需要记住一下哈：
- ock_mode X locks gap before rec 表示X型的gap锁

• lock_mode X locks rec but not gap 表示 X型的记录锁（Record Lock）
• lock mode X 一般表示 X型临键锁（next-key 锁）

以上的锁日志，我们一般关注点，是一下这几个地方：

• TRX HAS BEEN WAITING 7 SEC FOR THIS LOCK TO BE GRANTED 表示它在等这个锁

• RECORD LOCKS space id 267 page no 4 n bits 80 index c of table test2.t5 trx id 1644853 lock_mode X locks gap before rec insert intention waiting 表示一个锁结构，这个锁结构的Space ID是267，page number是4，n_bits属性为80，对应的索引是c，这个锁结构中存放的锁类型是X型的插入意向Gap锁。

• 0: len 4; hex 8000000a; asc ;; 对应加锁记录的详细信息，8000000a代表的值就是10，a的16进制是10。

• TABLE LOCK table test2.t5 trx id 1644853 lock mode IX 表示一个插入意向表锁

这个日志例子，其实理解起来，就是事务A持有了索引c的间隙锁（~，10），而事务B想获得这个gap锁，而获取不到，就一直在等待这个插入意向锁

6. 手把手死锁案例分析

如果发生死锁了，我们应该如何分析呢？一般分为四个步骤：

1. show engine innodb status，查看最近一次死锁日志。
2. 分析死锁日志，找到关键词 TRANSACTION
3. 分析死锁日志，查看正在执行的 SQL
4. 看 SQL 持有什么锁，又在等待什么锁。

6.1 一个死锁的简单例子

表结构和数据如下：

CREATE TABLE t6 ( 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 t6 values(5,5,5),(10,10,10);

我们开启A、B事务，执行流程如下：

6.2 分析死锁日志

①：show engine innodb status，查看最近一次死锁日志。如下：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-05-03 22:53:22 0x2eb4
*** (1) TRANSACTION:
TRANSACTION 1644867, ACTIVE 31 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 5, OS thread handle 7068, query id 607 localhost ::1 root statistics
Select * from t6 where id=10 for update
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 268 page no 3 n bits 72 index PRIMARY of table `test2`.`t6` trx id 1644867 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000019193c; asc      <;;
 2: len 7; hex dd00000191011d; asc        ;;
 3: len 4; hex 8000000a; asc     ;;
 4: len 4; hex 8000000a; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 1644868, ACTIVE 17 sec starting index read, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 7, OS thread handle 11956, query id 608 localhost ::1 root statistics
Select * from t6 where id=5 for update
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 268 page no 3 n bits 72 index PRIMARY of table `test2`.`t6` trx id 1644868 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000019193c; asc      <;;
 2: len 7; hex dd00000191011d; asc        ;;
 3: len 4; hex 8000000a; asc     ;;
 4: len 4; hex 8000000a; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 268 page no 3 n bits 72 index PRIMARY of table `test2`.`t6` trx id 1644868 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 00000019193c; asc      <;;
 2: len 7; hex dd000001910110; asc        ;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 80000005; asc     ;;

②：先找到关键词 TRANSACTION，可以发现两部分的事务日志，如下：

③：查看正在执行，产生死锁的对应的SQL，如下：

④：查看分开两部分的TRANSACTION，分别持有什么锁，和等待什么锁