10个行锁、死锁案例⭐️24张加锁分析图🚀彻底搞懂Innodb行锁加锁规则！

10个行锁、死锁案例⭐️24张加锁分析图🚀彻底搞懂Innodb行锁加锁规则！

上篇文章 我们描述原子性与隔离性的实现，其中描述读操作解决隔离性问题的方案时还遗留了一个问题：写操作是如何解决不同的隔离性问题？

本篇文章将会解决这个问题并描述MySQL中的锁、总结Innodb中行锁加锁规则、列举行锁、死锁案例分析等

再阅读本篇文章前，至少要理解查询使用索引的流程、mvcc等知识（不理解的同学可以根据专栏顺序进行阅读）

MySQL锁的分类

从锁的作用域上划分:全局锁、表锁、页锁、行锁

从功能上划分：意向锁、插入意向锁、自增长锁、悲观锁、乐观锁...

这些锁有些是MySQL提供的，有些是存储引擎提供的，比如Innodb支持的行锁粒度小，并发性能高，不易冲突

但在某些场景下行锁还是会发生冲突（阻塞），因此我们需要深入掌握行锁的加锁规则才能在遇到这种场景时分析出问题

Innodb的行锁加锁规则

前面说到行锁分为独占锁 X锁和共享锁 S锁，行锁除了会使用这种模型外，还会使用到一些其他的模型

锁模型

record：记录锁，用于锁定某条记录 模型使用S/X （也就是独占锁、共享锁）

gap：间隙锁，用于锁定某两条记录之间，禁止插入，用于防止幻读 模型使用GAP

next key：临键锁，相当于记录锁 + 间隙锁 模型使用X/S，GAP

在lock mode中常用X或S代表独占/共享的record，GAP则不分X或S

如果是独占的临键锁则是X、GAP，共享的临键锁则是S、GAP

（这个lock mode后面案例时会用到，表示当前SQL被哪种锁模型阻塞）

不同隔离级别下的加锁

加锁的目的就是为了能够满足事务隔离性从而达到数据的一致性

在不同隔离级别、使用不同类型SQL（增删改查）、走不同索引（主键和非主键、唯一和非唯一）、查询条件（等值查询、范围查询）、MySQL版本 等诸多因素都会导致加锁的细节发生变化

因此只需要大致掌握加锁规则，并结合发生阻塞的记录排查出发生阻塞的问题，再进行优化、解决即可（本文基于5.7.X）

在RU（Read Uncommitted）下，读不加锁，写使用X record，由于写使用X record 则不会产生脏写，会产生脏读、不可重复读、幻读，

在RC（Read Committed）下，读使用mvcc（每次读生成read view），写使用X record，不会产生脏写、脏读，但会有不可重复读和幻读

在RR（Repeatable Read）下，读使用mvcc（第一次读生成read view），写使用X next key，不会产生脏写、脏读、不可重复读和大部分幻读，极端场景的幻读会产生

在S（Serializable）下，自动提交情况下读使用mvcc，手动提交下读使用S next key，写使用X next key，不会产生脏写、脏读、不可重复读、幻读

在常用的隔离级别RC、RR中，读都是使用mvcc机制（不加锁）来提高并发性能的

锁定读的加锁

在S串行化下，读会加S锁，那select如何加锁呢?

S锁：select ... lock in share mode

X锁：select ... for update

通常把加锁的select称为锁定读，而在普通的update和delete时，需要先进行读（找到记录）再操作，在这种情况下加锁规则也可以归为锁定读

update与delete是写操作，肯定是加X锁的

（以下锁定读和新增的加锁规则是总结，搭配案例查看，一开始看不懂不要紧~）

锁定读加锁规则

新增的加锁

前面说到update、delete这种先查再写的操作可以看成加X锁的锁定读，而select的锁定读分为S、X，还剩insert的规则没有说明

新增加锁规则

新增加锁规则分为三种情况：正常情况、遇到重复冲突的情况、外键情况

新增时加的锁叫插入意向锁，它是隐式锁

当别的事务想要获取该记录的X/S锁时，查看该记录的事务id是不是活跃事务，如果活跃（事务未提交）则会帮新增记录的事务生成锁结构，此时插入意向锁变成显示锁（可以看成X锁）

正常情况下加锁：

重复冲突加锁：

外键加锁：一般不做物理外键，略...

行锁案例分析

搭建环境

先建立一张测试表，其中id为主键，以s_name建立索引

CREATE TABLE `s` (
  `id` int(11) NOT NULL,
  `s_name` varchar(255) DEFAULT NULL,
  `s_age` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name_idx` (`s_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

再插入一些记录

INSERT INTO `s` (`id`, `s_name`, `s_age`) VALUES (1, 'juejin', '1');
INSERT INTO `s` (`id`, `s_name`, `s_age`) VALUES (10, 'nb', '10');
INSERT INTO `s` (`id`, `s_name`, `s_age`) VALUES (20, 'caicai菜菜', '20');
INSERT INTO `s` (`id`, `s_name`, `s_age`) VALUES (25, 'ai', '25');

聚簇索引和s_name索引的存储图像简化成如下：

前面说过GAP需要加在记录之间，如果是第一条记录或者最后一条记录要防止插入，该如何加GAP锁呢？

Infimum和Supremum的出现就能够解决这种问题，它们用于标识每页的最小值和最大值

注意：由于RC、RR是常用的隔离级别，案例也是使用这两种隔离级别进行说明

分析方法

可以通过系统库查看行锁阻塞的相关信息

5.7 阻塞的锁、事务等信息在information_schema库中的innodb_locks、innodb_lock_waits、innodb_trx等表

8.0 的相关信息则是在performance_schema库中

lock记录信息简介

lock_id 锁id 由事务id、存储信息组成 会变动

lock_trx_id 事务ID 42388为先开启的事务 （事务ID全局自增）

lock_mode 阻塞的锁为X锁，还有其他模式：S、X、IS、IX、GAP、AUTO_INC等

lock_type 锁类型为行锁record ，还有表锁：table

lock_table 锁的表 ; lock_index 锁的索引 （二级索引）

lock_space 、page 、rec 锁的表空间id、页、堆号等存储信息

lock_data 表示锁的数据，一般是行记录 'caicai菜菜',20 (s_name,id)

还可以通过联表查询获取行锁阻塞的信息

SELECT
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
	rl.lock_mode waiting_lock_mode,
	rl.lock_type waiting_lock_type,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query,
	bl.lock_mode blocking_lock_mode,
	bl.lock_type blocking_lock_type
FROM       information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b
  ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r
  ON r.trx_id = w.requesting_trx_id
inner join information_schema.innodb_locks rl
  on 	r.trx_id = rl.lock_trx_id
inner join information_schema.innodb_locks bl 
  on b.trx_id = bl.lock_trx_id;

又或者通过 innodb的日志 （show engine innodb status）查看阻塞信息...

（后文分析再说）

案例：RC、RR下的加锁

T1事务在10，20之间会加GAP锁，因此T2新增时会被阻塞

设置为RC后不再阻塞，因为RC下不加GAP锁不防止插入

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT @@tx_isolation;

但如果是要获取记录锁则还是会被阻塞 （修改id为10的记录 update s set s_name = '666' where id = 10）

根据该案例可以说明规则一：RC及以下使用记录锁、RR及以上使用临键锁

案例：等值查询

等值查询：匹配不到满足条件的记录

通过阻塞记录可以看到T2,T3事务被主键索引上数据为20的临键锁（的GAP）阻塞

等值查询如果匹配不到值会在该区间加GAP锁

图中向下黑箭头为GAP锁

例如T1等值查询id=15，没有id=15的记录则会加锁在15这个区间加GAP锁

等值查询：匹配到满足条件的记录

因为唯一索引上相同的记录只有一条，当等值查询匹配时，临键锁会退化成记录锁，因此T2不被阻塞 T3被阻塞

图中为T3被数据为20上的X锁阻塞

唯一索引等值查询间隙锁退化为记录锁

(图中蓝色为记录锁)

非唯一索引等值查询

为了确保 select s_name,id from s where s_name='caicai菜菜' for update 使用s_name索引，我将查询列换成s_name上存在的列，避免回表（确保使用s_name）

最终加锁范围 = (ai,caicai菜菜] + (caicai菜菜，juejin) = (ai，juejin)

(注意：我这里的加锁范围是简化的，没有带上主键信息；完整信息如下图lock_data中的juejin,1)

然后再来分析T2,T3的插入语句，首先它们需要在聚簇索引和name_idx索引上新增数据，由于聚簇索引未加锁，因此不影响插入

但是name_idx索引上存在锁，T2事务 bilibili 会插入到ai和caicai菜菜记录之间，T3事务会插入到caicai菜菜和juejin这两条记录间，因此被GAP锁阻塞

通过阻塞记录也可以看出T2,T3均被临键锁阻塞

至此等值查询的案例分析完毕，小结如下：

• 等值查询找不到记录：该区间加GAP锁
• 等值查询找到记录
• 唯一索引：临键锁会退化为记录锁
• 非唯一索引：一直扫描到第一条不满足条件的记录并将临键锁退化为间隙锁

案例：范围查询

在上面等值查询 + 非唯一索引的场景下，由于无法判断该值数量，因此会一直扫描，可以把这种场景理解成范围查询

按照正常思路来说，我的查询条件在10-20，那么就不能往这个范围外再加锁了

但是新增该范围外的记录是会阻塞的（我明明查询条件在10~20，结果超过20你也给我加锁是吧？）

我们来分析下T1加锁过程： id>=10 and id<=20

最终加锁范围 [10] + (10,20] + (20,25) = [10,25)，因此插入主键为21时会被阻塞

思考：按照正常的思路，当在非唯一索引上时，这么扫描没问题，因为不知道满足结果的20有多少条，只能往后扫描找到第一条不满足条件的记录；而在唯一索引上找到最后一个满足条件的记录20后，还继续往后加锁是不是有点奇怪呢？

我在8.0的版本中重现这个操作，插入id=21不再被阻塞，应该是在唯一索引上扫描到最终满足条件的记录（id=20）就结束，加锁范围如下图（在5.7中这应该算bug）

范围查询时无论是否唯一索引都会扫描到第一条不满足条件的记录，然后临键锁退化为间隙锁 （8.0修复唯一索引范围查询时的bug）

案例：查找过程中怎么加锁

T1 事务在修改时先使用聚簇索引定位到id=20的记录，修改后通过主键id=20找到二级索引上的记录进行修改，因此聚簇索引、二级索引上都会获取锁

T2 事务锁定读二级索引时，由于查询条件满足二级索引的值，因此不需要回表，但由于T1事务锁住二级索引上的记录，因此发生阻塞

在该案例中说明：加锁时使用什么索引就要在那个索引上加锁，遍历到哪些记录就要在哪些记录上加锁

delete：与主键相关的二级索引肯定也要删除，因此二级索引上对应主键值的记录也会被加锁

update：如果在二级索引上修改，那么一定回去聚簇索引上修改，因此聚簇索引也会被加锁；如果在聚簇索引上修改，二级索引可能会需要被加锁（如上案例，如果修改的是s_age那么二级索引就不需要加锁）

select：使用什么索引就在什么索引上加锁，比如使用聚簇索引就要在聚簇索引上加锁，使用二级索引就在二级索引上加锁（如果要回表也要在聚簇索引上加锁）

案例：RC、RR什么时候释放锁

RC及以下，RR及以上在获取完锁后，释放锁的时机也不同

RR下

T3插入的记录满足 s_name > 'c' and s_age > 18 的记录被阻塞情有可原

那为啥T2 id=1不满足 s_name > 'c' and s_age > 18 也被阻塞了呢？

T1事务是一条修改语句，我使用force index 让它强制使用name_idx索引，查询条件为 s_name > 'c' and s_age > 18

由于name_idx上不存在s_age，需要判断s_age就要去聚簇索引，因此聚簇索引上也会被加锁

T1在name_idx上，根据查询条件s_name > 'c'进行加锁

根据1-3的步骤，会在索引上这样加锁

最终加锁状态：（name_id中的+∞则指的是supremum）

其中只有id=20的记录满足 s_name > 'c' and s_age > 18，即使这些记录不满足条件也不会释放锁

因此T2要获取聚簇索引id=1的记录时被阻塞，而T3则是被supremum阻塞

在RR下使用的索引遍历到哪就把锁加到哪，即使不满足查询条件也不会释放锁，直到事务提交才释放

RC

设置隔离级别

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT @@tx_isolation;

遍历流程与RR情况相似，不同的是RC只加记录锁，并且不满足条件的记录会立即释放锁，因此T2不被阻塞，满足条件的T3被阻塞

加锁如下图

遍历到哪条记录就先加锁，但是RC对于不满足查询条件的记录会释放锁

死锁案例分析

死锁案例分析的是insert加的锁，配合上面新增加锁规则查看

案例：新增死锁

先将name_idx改为唯一索引

T1插入bilibili，T2也插入bilibili，按照道理应该报错唯一键重复呀，T2怎么阻塞了呢？

T1后续再插入balibali竟然发生死锁了！啥情况呀？同学们可以先根据前面说到的insert加锁规则，大胆猜测喔~

查看最近的死锁日志

需要注意的是innodb lock表中锁相关信息记录只有正在发生时才存在，像这种发生死锁，回滚事务后是看不到的，因此我们来看看死锁日志

show engine innodb status 查看innodb状态，其中有一段最近检测到的死锁 latest detected deadlock

红色框表示事务和持有/等待的锁

绿色框表示锁的信息（都是同一把X锁）

如果日志还是看不太懂的话，来看看下面这段分析吧（主要说name_idx索引上的流程哈）

1、T1插入bilibili（隐式锁）

2、T2插入bilibili发生冲突，T2帮T1生成锁结构（隐式锁转化为显示锁，T1获得X record），T2要加S临键锁，先获取GAP锁（成功），再获取S锁（被T1的X record阻塞）

T1事务id为42861，T2事务id为42867：根据锁信息可以看到T2想加的S锁被T1的X锁阻塞

3、T1插入balibali，插入意向锁被T2的GAP锁阻塞（死锁成环：T1等待T2的GAP，T2等待T1的X）

图中蓝色与T1有关，黑色与T2有关

T1：持有[bilibili，bilibili] X锁，要插入balabala被T2的间隙锁（ai,bilibili）阻塞

T2：持有间隙锁（ai,bilibili），要插入[bilibili，bilibili]S锁被T1的[bilibili，bilibili]X锁阻塞

那么如何解决死锁呢？

先来看看死锁产生的四个条件：互斥、占有资源不放、占有资源继续申请资源、等待资源成环

MySQL通过回滚事务的方式解决死锁，也就是解决占有资源不放

但MySQL死锁检测是非常耗费CPU的，为了避免死锁检测，我们应该在业务层面防止死锁产生

首先互斥、占有资源不放两个条件是无法破坏的，因为加锁由MySQL来实现

而破坏占有资源继续申请资源的代价可能会很大，比如：让业务层加锁处理

性价比最高的应该是破坏等待资源成环，当发生死锁时，通过分析日志、加锁规则，调整业务代码获取资源的顺序避免发生死锁

案例：相同的新增发生死锁

T1、T2、T3新增相同的记录

T1新增后，T2、T3 会帮T1生成锁结构X锁从而被阻塞

当T1回滚时，T2，T3竟然发生死锁？

分析流程

图中T2、T3都对bili加S next key锁（橙色记录和前面的黑色间隙），当它们都想加插入意向X锁（蓝色记录），同时也被各自的GAP锁阻塞

查看死锁日志

总结

本篇文章通过大量案例、图例分析不同情况下的行锁加锁规则

update、delete 先查再改，可以看成锁定读，insert则是有单独一套加锁规则

锁定读加锁规则

在RC及以下隔离级别，锁定读使用record锁；在RR及以上隔离级别，锁定读使用next key锁

等值查询：如果找不到记录，该查询条件所在区间加GAP锁；如果找到记录，唯一索引临键锁退化为记录锁，非唯一索引需要扫描到第一条不满足条件的记录，最后临键锁退化为间隙锁（不在最后一条不满足条件的记录上加记录锁）

范围查询：非唯一索引需要扫描到第一条不满足条件的记录（5.7中唯一索引也会扫描第一条不满足条件的记录8.0修复，后文描述）

在查找的过程中，使用到什么索引就在那个索引上加锁，遍历到哪条记录就给哪条先加锁

在RC及以下隔离级别下，查找过程中如果记录不满足当前查询条件则会释放锁；在RR及以上无论是否满足查询条件，只要遍历过记录就会加锁，直到事务提交才释放

insert加锁规则

正常情况下加锁：

重复冲突加锁：

如果使用ON DUPLICATE KEY update那么S锁会换成X锁

外键加锁：一般不做物理外键，略...

最后（不要白嫖，一键三连求求拉~）

本篇文章被收入专栏 MySQL进阶之路，感兴趣的同学可以持续关注喔

本篇文章笔记以及案例被收入 gitee-StudyJava、 github-StudyJava 感兴趣的同学可以stat下持续关注喔~

有什么问题可以在评论区交流，如果觉得菜菜写的不错，可以点赞、关注、收藏支持一下~

关注菜菜，分享更多干货，公众号：菜菜的后端私房菜