MySQL死锁问题详解及解决方案 -- 知识铺
尼恩说在前面
在40岁老架构师 尼恩的读者交流群(50+)中，最近有小伙伴拿到了一线企业如 字节、得物、阿里、滴滴、极兔、有赞、希音、百度、网易、美团、蚂蚁、得物的面试资格，遇到很多很重要的面试题：
MySQL死锁什么时候发生，如何解决？
如何解决MySQL中的死锁问题？
最近有小伙伴在面试字节，都到了这个的面试题。
小伙伴没回答好，支支吾吾的说了几句，面试官不满意，面试挂了。
所以，尼恩给大家做一下系统化、体系化的梳理，帮大家展示一下大家雄厚的 “技术肌肉”，让面试官爱到 “不能自已、口水直流”，然后实现”offer直提”。
当然，这道面试题，以及参考答案，也会收入咱们的 《尼恩Java面试宝典PDF》V175版本，供后面的小伙伴参考，提升大家的 3高 架构、设计、开发水平。
《尼恩 架构笔记》《尼恩高并发三部曲》《尼恩Java面试宝典》的PDF，请到文末公号【技术自由圈】获取
本文目录
- 尼恩说在前面
- 1 什么是mysql死锁？
- 2 从操作的粒度进行的mysql锁的分类
- 表级锁：
- 行级锁：
- 页级锁：
- 3 从操作的类型进行的mysql锁的分类
- 读锁（S锁）
- 写锁（X锁）
- 意向锁
- 4 从操作的性能进行的mysql锁的分类
- 5 InnoDB存储引擎三种行锁模式
- 5.1 记录锁(Record Locks)
- 5.2 间隙锁(Gap Locks)
- 5.3 行锁：临键锁(Next-Key Locks)
- 6 事务隔离级别和锁的关系
- 6.1 数据库事务的隔离级别
- 6.2 事务隔离级别和锁的关系
- 7 死锁产生原因和解决方案
- 7.1 查看Innodb行锁争用情况
- 7.2 详细介绍 死锁的概念
- 7.3 表级锁死锁
-a）产生原因:
-b）解决方案：
- 7.4 行级锁死锁
-a) 产生原因1：
-b) 产生原因2：
-c) 产生原因3：每个事务只有一个SQL,但是有些情况还是会发生死锁.
- 8：InnoDB预防死锁策略
- 9 ：死锁案例分析
- 9.1 案例一：拆借款
- 9.2 案例二：有则插入无则更新
- 9.3 案例三 死锁日志分析
-场景1:
- 10 ：死锁产生的前提和建议
- 11：线上发生了死锁，应该如何具体操作？
- 11.1 监控死锁
- 11.2 终止死锁事务
- 11.3 重试事务
- 11.4 防止死锁再次发生
- 说在最后：有问题找老架构取经
1 什么是mysql死锁？
死锁是指两个或多个事务在执行过程中，因争夺锁资源而造成的相互等待的现象，若无外力干涉它们都将无法继续执行。
通俗来说，就是两个或多个事务在等待对方释放锁，从而造成僵持不下，使得整个系统陷入停滞状态。
2 从操作的粒度进行的mysql锁的分类
从操作的粒度可分为表级锁、行级锁和页级锁。
表级锁：
每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。
应用在MyISAM、InnoDB、BDB 等存储引擎中。
表锁的特点：
-
开销小，加锁快
-
不会出现死锁
-
锁定粒度大，发生锁冲突的概率最高，并发度最低
行级锁：
每次操作锁住一行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。
应用在InnoDB 存储引擎中。
行锁的特点：
-
开销大，加锁慢
-
会出现死锁
-
锁定粒度小，发生锁冲突的概率最低，并发度最高
页级锁：
每次锁定相邻的一组记录，锁定粒度界于表锁和行锁之间，开销和加锁时间界于表锁和行锁之间，并发度一般。
页锁的特点：
-
开销和加锁时间介于表锁和行锁之间
-
会出现死锁
-
锁定粒度介于表锁和行锁之间，并发度一般
3 从操作的类型进行的mysql锁的分类
从操作的类型可分为读锁和写锁。
读锁（S锁）
读锁（S锁）：共享锁,针对同一份数据,多个读操作可以同时进行而不会互相影响。
S锁：事务A对记录添加了S锁，可以对记录进行读操作，不能做修改，其他事务可以对该记录追加S锁，但是不能追加X锁，要追加X锁，需要等记录的S锁全部释放。
写锁（X锁）
写锁（X锁）：排他锁,当前写操作没有完成前,它会阻断其他写锁和读锁
X锁：事务A对记录添加了X锁，可以对记录进行读和修改操作，其他事务不能对记录做读和修改操作。
意向锁
-
IS: 意向共享锁，表级锁，已加S锁的表，肯定会有IS锁，反过来，有IS锁的表，不一定会有S锁
-
IX: 意向排它锁，表级锁，已加X锁的表，肯定会有IX锁，反过来，有IX锁的表，不一定会有X锁
4 从操作的性能进行的mysql锁的分类
从操作的性能可分为乐观锁和悲观锁。
-
乐观锁：一般的实现方式是对记录数据版本进行比对，在数据更新提交的时候才会进行冲突检测，如果发现冲突了，则提示错误信息。
-
悲观锁：在对一条数据修改的时候，为了避免同时被其他人修改，在修改数据之前先锁定，再修改的控制方式。共享锁和排他锁是悲观锁的不同实现，但都属于悲观锁范畴。
5 InnoDB存储引擎三种行锁模式
InnoDB引擎行锁是通过对索引数据页上的记录加锁实现的，
主要实现算法有 3 种：Record Lock、Gap Lock 和 Next-key Lock,
也就是InnoDB的三种行锁模式。
-
RecordLock锁(行锁)：锁定单个行记录的锁。（RecordLock锁 是记录锁，RC、RR隔离级别都支持）
-
GapLock锁：间隙锁，锁定索引记录间隙(不包括记录本身)，确保索引记录的间隙不变。（GapLock是范围锁，RR隔离级别支持。RC隔离级别不支持）
-
Next-key Lock 锁(临键锁)：记录锁和间隙锁组合，同时锁住数据，并且锁住数据前后范围。（记录锁+范围锁，RR隔离级别支持。RC隔离级别不支持）
5.1 记录锁(Record Locks)
（1）记录锁, 仅仅锁住索引记录的一行，在单条索引记录上加锁。
（2）record lock锁住的永远是索引，而非记录本身，即使该表上没有任何索引，那么innodb会在后台创建一个隐藏的聚集主键索引，那么锁住的就是这个隐藏的聚集主键索引。
所以说当一条sql没有走任何索引时，那么将会在每一条聚合索引后面加X锁，这个类似于表锁，但原理上和表锁应该是完全不同的。
5.2 间隙锁(Gap Locks)
（1）区间锁, 仅仅锁住一个索引区间（开区间，不包括双端端点）。
（2）在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括该索引记录本身。
（3）间隙锁可用于防止幻读，保证索引间的不会被插入数据
比如在 100、10000中，间隙锁的可能值有 (∞, 100)，(100, 10000)，(10000, ∞)，
5.3 行锁：临键锁(Next-Key Locks)
（1）record lock + gap lock, 左开右闭区间。
（2）默认情况下，innodb使用next-key locks来锁定记录。select … for update
（3）但当查询的索引含有唯一属性的时候，Next-Key Lock 会进行优化，将其降级为Record Lock，即仅锁住索引本身，不是范围。
（4）Next-Key Lock在不同的场景中会退化:
比如在 100、10000中，临键锁(Next-Key Locks)的可能有 (∞, 100]，(100, 10000] , 40岁老架构师尼恩提示，这里的关键是左开右闭
具体的讲解，请参见《尼恩Java面试宝典》配套视频。
6 事务隔离级别和锁的关系
6.1 数据库事务的隔离级别
先来回顾一下，数据库事务的隔离级别，目前数据库事务的隔离级别一共有 4 种，由低到高分别为：
事务的四个隔离级别：
-
未提交读（READ UNCOMMITTED）：所有事务都可以看到其他事务未提交的修改。一般很少使用；
-
读已提交（READ COMMITTED）：Oracle默认隔离级别，事务之间只能看到彼此已提交的变更修改；
-
可重复读（REPEATABLE READ）：MySQL默认隔离级别，同一事务中的多次查询会看到相同的数据行；可以解决不可重复读，但可能出现幻读；
-
可串行化（SERIALIZABLE）：最高的隔离级别，事务串行的执行，前一个事务执行完，后面的事务会执行。读取每条数据都会加锁，会导致大量的超时和锁争用问题；
数据库一般默认的隔离级别为 读已提交 RC ，比如 Oracle，
也有一些数据的默认隔离级别为 可重复读 RR，比如 Mysql。
“可重复读”（Repeatable Read）这个级别确保了对同一字段的多次读取结果是一致的，除非数据是被本身事务自己所修改。
RR它能够防止脏读、不可重复读，但可能会遇到幻读的情况。
参考 文章：
MySQL默认的Repeatable Read隔离级别，被改成了RC , 具体请参考 《尼恩Java 面试宝典》 MYSQL 专题：
一般而言，数据库的读已提交（READ COMMITTED）能够满足业务绝大部分场景了。
6.2 事务隔离级别和锁的关系
-
事务隔离级别是SQL92定制的标准，相当于事务并发控制的整体解决方案，本质上是对锁和MVCC使用的封装，隐藏了底层细节。
-
锁是数据库实现并发控制的基础，事务隔离性是采用锁来实现，对相应操作加不同的锁，就可以防止其他事务同时对数据进行读写操作。
-
对用户来讲，首先选择使用隔离级别，当选用的隔离级别不能解决并发问题或需求时，才有必要在开发中手动的设置锁。
MySQL 默认隔离级别：可重复读， 一般建议改为 RC 读已提交
Oracle、SQLServer默认隔离级别：读已提交
7 死锁产生原因和解决方案
InnoDB与MyISAM的最大不同有两点
-
支持事务
-
采用行锁
行级锁和表级锁本来就有许多不同之处，另外，事务的引入也带来了一些问题 ，比如 死锁。
7.1 查看Innodb行锁争用情况
通过show status like 'innodb_row_lock_%'; 命令可以查询MySQL整体的锁状态，如下：
-
Innodb_row_lock_current_waits：当前正在阻塞等待锁的事务数量。 -
Innodb_row_lock_time：MySQL启动到现在，所有事务总共阻塞等待的总时长。 -
Innodb_row_lock_time_avg：平均每次事务阻塞等待锁时，其平均阻塞时长。 -
Innodb_row_lock_time_max：MySQL启动至今，最长的一次阻塞时间。 -
Innodb_row_lock_waits：MySQL启动到现在，所有事务总共阻塞等待的总次数。
7.2 详细介绍 死锁的概念
什么是死锁DeadLock？：
是指两个或两个以上的进程在执行过程中, 因争夺资源而造成的一种互相等待的现象,
若无外力作用，它们都将无法推进下去.
此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。
一个的形象举例：
假设有两个事务 A 和 B，它们同时试图获取对方持有的资源，但又都在等待对方释放资源，导致了僵持不下的局面。
举个例子，假设有两个人 A 和 B，他们同时想要通过一扇门进入一个房间，但这扇门只能由一人单独打开。
现在，尴尬了 ：
-
A 想要进入房间1，但门被 B 挡住了，所以 A 无法进入，于是 A 抓住了 B 的右手，不让 B 打开房间2的门。
-
B 想要进入房间2，但门被 A 挡住了，所以 B 无法进入，于是 B 抓住了 A 的左手，不让 A 打开房间1的门。
现在的情况是：
-
A 等待着 B 放开 B 的左手，以便自己能打开门进入房间，
B 等待着 A 放开A的右手，以便自己能够进入房间。
这就形成了死锁，因为两个人都在 对方放开资源，而对方又不愿意放开自己所持有的资源，导致了相互等待，最终无法继续执行下去。
7.3 表级锁死锁
a）产生原因:
-
用户A先访问表1（锁住了表1），然后再访问表2；
-
用户B先访问表2（锁住了表2），然后再企图访问表1；
用户A和用户B加锁的顺序如下：
-
用户A–》表1（表锁）–》表2（表锁）
-
用户B–》表2（表锁）–》表1（表锁）
这时， 出现了二者的相互争抢：
-
用户A由于用户B已经锁住表2，它必须等待用户B释放表2才能继续，
-
同样用户B要等用户A释放表1才能继续
这就死锁就产生了。如下图所示：
b）解决方案：
这种死锁比较常见，是由于程序的BUG产生的，除了调整的程序的逻辑没有其它的办法。
仔细分析程序的逻辑，对于数据库的多表操作时，尽量按照相同的顺序进行处理，
尽量避免同时锁定两个资源，如操作A和B两张表时，总是按先A后B的顺序处理， 必须同时锁定两个资源时，要保证在任何时刻都应该按照相同的顺序来锁定资源。
7.4 行级锁死锁
a) 产生原因1：
如果在事务中执行了一条没有索引条件的查询，引发全表扫描，行锁 膨胀 为表锁（ 或者等价于 表级锁），
多个这样的 锁表事务 执行后，就很容易产生死锁和阻塞，最终应用系统会越来越慢，发生阻塞或 死锁。
解决方案1：
SQL语句中不要使用太复杂的关联多表的查询；
使用explain“执行计划"对SQL语句进行分析，对于有全表扫描和全表锁定的SQL语句，建立相应的索引进行优化。
b) 产生原因2：
两个事务分别想拿到对方持有的锁，互相等待，于是产生死锁。
c) 产生原因3：每个事务只有一个SQL,但是有些情况还是会发生死锁.
假设有下面的一个表 t1
<section><span>create table t1(<br> id int(32) not null,<br> name varchar(50) not null,<br> reg_time int(32) not null,<br> city varchar(50) ,<br> primary key (`name`),<br> index index_name(`name`),<br> index index_reg_time(`reg_time`)<br>);<br></span></section>
事务1, 假设有下面的一个 session1 会话
<section><span>update t1 set city = "香港" where name="aaa"<br></span></section>
首先， session1 从name 非聚族索引索引出发 , 读到的 [aaa, 1], [aaa, 4] , 会加name索引上的记录[aaa, 1], [aaa, 4] 两个 记录的X锁,
然后，session1 会加聚簇索引上的记录X锁, 聚簇索引上 加锁顺序为先[1] 记录, 后[4] 记录
事务2 假设有下面的一个 session2 会话
<section><span>Select * from t1 where reg_time>=1000 for update<br></span></section>
session2 从reg_time 非聚族索引索引出发 , 读到的 [1000, 4], [1100, 3] , [1200,1], [1300, 2] ,
首先，session2 会加reg_time索引上的记录 [1000, 4], [1100, 3] , [1200,1], [1300, 2] 四个 记录的X锁,
然后，session2 而且会加聚簇索引上的记录X锁, 聚簇索引上 加锁顺序为 [4] 、[3] 、[2] 、[1] ，其中 先[4] 记录, 后[1] 记录
session2 加聚簇索引上的记录X锁时，发现跟session1的加锁顺序正好相反，
两个Session恰好都持有了第一把锁，请求加第二 把锁，死锁就发生了。
解决方案: 如上面的原因2和原因3, 对索引加锁顺序的不一致很可能会导致死锁，所以如果可以，
尽量以相同的顺序来访问索引记录和表。
在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能；
通过统一的锁定顺序，可以有效地避免不同事务之间的锁定顺序不一致导致的死锁问题。
具体的讲解，请参见《尼恩Java面试宝典》配套视频。
8：InnoDB预防死锁策略
InnoDB引擎内部(或者说是所有的数据库内部)，有多种锁类型：事务锁(行锁、表锁)，Mutex(保护内部的共享变量操作)、RWLock(又称之为Latch，保护内部的页面读取与修改)。
InnoDB每个页面为16K，读取一个页面时，需要对页面加S锁(共享锁)，更新一个页面时，需要对页面加上X锁(排他锁)。
任何情况下，操作一个页面，都会对页面加锁，页面锁加上之后，页面内存储的索引记录才不会被并发修改。
因此，为了修改一条记录，InnoDB内部如何处理：
-
根据给定的查询条件，找到对应的记录所在页面；
-
对页面加上X锁(RWLock)，然后在页面内寻找满足条件的记录；
-
在持有页面锁的情况下，对满足条件的记录加事务锁(行锁：根据记录是否满足查询条件，记录是否已经被删除，分别对应于上面提到的3种加锁策略之一)；
相对于事务锁，页面锁是一个短期持有的锁，而事务锁(行锁、表锁)是长期持有的锁。
InnoDB预防死锁策略
因此，为了防止页面锁与事务锁之间产生死锁，InnoDB做了死锁预防的策略：
-
持有事务锁(行锁、表锁)，可以等待获取页面锁；
-
但反之，持有页面锁，不能等待持有事务锁。
根据死锁预防策略，在持有页面锁，加行锁的时候，如果行锁需要等待，则释放页面锁，然后等待行锁。
此时，行锁获取没有任何锁保护，因此加上行锁之后，记录可能已经被并发修改。因此，此时要重新加回页面锁，重新判断记录的状态，重新在页面锁的保护下，对记录加锁。
如果此时记录未被并发修改，那么第二次加锁能够很快完成，因为已经持有了相同模式的锁。但是，如果记录已经被并发修改，那么，就有可能导致死锁问题。
在数据库系统中，死锁的检测和解决通常是通过**锁管理器（Lock Manager）**来实现的。
-
当一个事务请求某个数据页的锁时，锁管理器会检查当前锁的状态以及其他事务是否持有或等待相同的锁。
-
如果存在潜在的死锁风险，系统会通过死锁检测算法来检测并解决死锁。其中，常用的死锁检测算法包括等待图（Wait-for graph）算法和超时算法。
在数据库系统的实现中，锁管理器会维护一个锁表（Lock Table），用于记录当前数据页的锁状态以及事务之间的关系。
当一个事务请求锁时，锁管理器会根据锁定顺序来判断是否存在死锁风险，并根据具体情况采取相应的措施，比如阻塞等待或者回滚事务。
在数据库系统的源代码级别，锁管理器通常是数据库引擎的一部分，具体实现方式会根据不同的数据库系统而有所不同。例如，MySQL、PostgreSQL、Oracle等数据库系统都有自己的锁管理器实现，通常会涉及到并发控制、事务管理等核心模块的代码。
总之，在MySQL 5.5.5及以上版本中，MySQL的默认存储引擎是InnoDB。该存储引擎使用的是行级锁，在某种情况下会产生死锁问题，所以InnoDB存储引擎采用了一种叫作等待图(wait-for graph)的方法来自动检测死锁，如果发现死锁，就会自动回滚一个事务
9 ：死锁案例分析
为了帮助大家了解mysql的死锁，下面有三个和死锁有关的案例：
9.1 案例一：拆借款
需求：投资人将投资的钱，拆成几份随机分配给借款人。
第一个版本的业务逻辑：投资人投资后，将金额随机分为几份，然后随机从借款人表里面选几个，然后通过一条条select for update 去更新借款人表里面的余额等。
例如两个用户同时投资，
-
A用户金额随机分为2份，分给借款人小明，小亮，
-
B用户金额随机分为2份，分给借款人小亮，小明
由于加锁的顺序不一样，死锁当然很快就出现了。
如果改进呢？对于这个问题的改进很简单，直接把所有分配到的借款人直接一次锁住就行了。
Select * from xxx where id in (xx,xx,xx) for update
在in里面的列表值mysql是会自动从小到大排序，加锁也是一条条从小到大加的锁。
例如（以下会话id为主键）：
Session1:
<section><br><span>mysql> select * from t3 where id in (8,9) for update;<br>+----+--------+------+---------------------+<br>| id | course | name | ctime |<br>+----+--------+------+---------------------+<br>| 8 | WA | f | 2016-03-02 11:36:30 |<br>| 9 | JX | f | 2016-03-01 11:36:30 |<br>+----+--------+------+---------------------+<br>rows in set (0.04 sec)<br><br></span></section>
Session2:
<section><br><span>select * from t3 where id in (10,8,6) for update;<br>锁等待中……<br><br></span></section>
其实这个时候id=10这条记录没有被锁住的，但id=6的记录已经被锁住了，锁的等待在id=8（被Session1 锁住）的这里
Session3:
<section><span>mysql> select * from t3 where id=6 for update;<br>锁等待中<br><br><br></span></section>
可以看到id=6被Session2 锁住，锁等待中
Session4:
<section><span>mysql> select * from t3 where id=10 for update;<br>+----+--------+------+---------------------+<br>| id | course | name | ctime |<br>+----+--------+------+---------------------+<br>| 10 | JB | g | 2016-03-10 11:45:05 |<br>+----+--------+------+---------------------+<br>row in set (0.00 sec)<br></span></section>
在其它session中id=6是加不了锁的，但是id=10是可以加上锁的， 说明id=10这条记录没有被Session2锁住 。
9.2 案例二：有则插入无则更新
在开发中，经常会做这类的判断需求：
-
根据字段值查询（有索引），如果不存在，则插入；
-
否则更新。
<section><span>以id为主键为例，目前还没有id=22的行<br><br>Session1:<br>select * from t3 where id=22 for update;<br>Empty set (0.00 sec)<br><br>session2:<br>select * from t3 where id=23 for update;<br>Empty set (0.00 sec)<br><br>Session1:<br>insert into t3 values(22,'ac','a',now());<br>锁等待中……<br><br>Session2:<br>insert into t3 values(23,'bc','b',now());<br>ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction<br></span></section>
-
当对存在的行 进行锁的时候(主键)，mysql就只有行锁。
-
当对未存在的行进行锁的时候(即使条件为主键)，mysql是会锁住一段范围（有gap锁），也就是间隙锁(Gap Locks)
间隙锁(Gap Locks) 锁住的范围为：(无穷小或小于表中锁住id的最大值，无穷大或大于表中锁住id的最小值)
-
如果表中目前有已有的id为（11 ， 12），那么就锁住（12，无穷大）
-
如果表中目前已有的id为（11 ， 30），那么就锁住（11，30）
对于这种死锁的解决办法是：
insert into t3(xx,xx) on duplicate key update xx=‘XX’;
用mysql特有的语法来解决此问题。因为insert语句对于主键来说，插入的行不管有没有存在，都会只有行锁
9.3 案例三 死锁日志分析
数据准备
<section><span>--创建表 t2<br>create table t2(<br> id int primary key,<br> name varchar(50),<br> age int<br>);<br>--插入数据<br>insert into t2 values(1,'lisi',11),(2,'zhangsan',22),(3,'wangwu',33);<br></span></section>
数据库隔离级别查看
<section><span>mysql> select @@tx_isolation;<br>+-----------------+<br>| @@tx_isolation |<br>+-----------------+<br>| REPEATABLE-READ |<br>+-----------------+<br></span></section>
查看加锁信息
<section><span>-- information_schema.innodb_trx: 当前出现的锁<br>select * from information_schema.innodb_locks;<br>-- information_schema.innodb_trx: 当前运行的所有事务<br>select * from information_schema.innodb_trx;<br>-- information_schema.innodb_lock_waits: 锁等待的对应关系<br>select * from information_schema.innodb_lock_waits;<br></span></section>
查看InnoDB状态 ( 包含最近的死锁日志信息 )
<section><span>show engine innodb status;<br></span></section>
案例分析
这里我们进行细致的分析，两个事物每执行一条SQL，
可以查看下innodb锁状态及锁等待信息以及当前innodb事务列表信息，
最后可以通过 show engine innodb status 查看最近的死锁日志信息.
场景1:
- 事务1, 执行begin开始事务执行一条SQL,查询 id=1 的数据
<section><span>mysql> begin;<br>Query OK, 0 rows affected (0.00 sec)<br>mysql> select * from t2 where id = 1 for update;<br>+----+------+------+<br>| id | name | age |<br>+----+------+------+<br>| 1 | lisi | 11 |<br>+----+------+------+<br>1 row in set (0.00 sec)<br></span></section>
分析加锁过程:
-
事务1进行首先申请IX锁 (意向排它锁,因为是for update)
-
然后申请X锁进行查询是否存在 id = 1 的记录
-
存在该记录,因为id字段是唯一索引,所以添加的是 Record Lock
-
查看 information_schema.innodb_trx表,发现存在事务1 的信息
<section><span>select trx_id '事务id',trx_state '事务状态',<br> trx_started '事务开始时间',trx_weight '事务权重',<br> trx_mysql_thread_id '事务线程ID',<br> trx_tables_locked '事务拥有多少个锁',<br> trx_lock_memory_bytes '事务锁住的内存大小',<br> trx_rows_locked '事务锁住的行数',<br> trx_rows_modified '事务更改的行数'<br> from information_schema.innodb_trx;<br></span></section>
- 执行事务2的 delete语句, 删除成功,因为id=3的数据并没有被加锁
<section><span>mysql> delete from t2 where id = 3; -- 删除成功<br></span></section>
- 事务1对 id=3 的记录进行修改操作,发生阻塞. 因为id=3的数据的X锁已经被事务2拿到,其他事务的操 作只能被阻塞.
<section><span>mysql> begin;<br>Query OK, 0 rows affected (0.00 sec)<br>mysql> select * from t2 where id = 1 for update;<br>+----+------+------+<br>| id | name | age |<br>+----+------+------+<br>| 1 | lisi | 11 |<br>+----+------+------+<br>1 row in set (0.00 sec)<br>mysql> update t2 set name = 'aaa' where id = 3;<br>-- 阻塞<br></span></section>
- 查看当前锁信息
<section><span>-- 查看当前锁信息<br>select<br>lock_id '锁ID',<br> lock_trx_id '拥有锁的事务ID',<br> lock_mode '锁模式',<br> lock_type '锁类型' ,<br> lock_table '被锁的索引',<br> lock_space '被锁的表空间号',<br> lock_page '被锁的页号',<br> lock_rec '被锁的记录号',<br> lock_data '被锁的数据'<br>from information_schema.innodb_locks;<br></span></section>
lock_rec=4 表示是对唯一索引进行的加锁. lock_mode= X 表示这里加的是X锁.
<section><span>-- 查看锁等待的对应关系<br>select requesting_trx_id '请求锁的事务ID',<br> requested_lock_id '请求锁的锁ID',<br> blocking_trx_id '当前拥有锁的事务ID',<br> blocking_lock_id '当前拥有锁的锁ID'<br> from information_schema.innodb_lock_waits;<br></span></section>
- 事务2 执行删除操作,删除 id = 1的数据成功.
<section><span>mysql> begin;<br>Query OK, 0 rows affected (0.00 sec)<br><br>mysql> delete from t2 where id = 3;<br>Query OK, 1 row affected (0.00 sec)<br><br>mysql> delete from t2 where id = 1;<br>Query OK, 1 row affected (0.00 sec)<br></span></section>
- 但是事务1已经检测到了死锁的发生
<section><span>mysql> update t2 set name = 'aaa' where id = 3;<br>ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction<br><br>--事务1 commit,更新操作失败<br>mysql> commit;<br>Query OK, 0 rows affected (0.00 sec)<br><br>mysql> select * from test_dead;<br>-- ERROR 1146 (42S02): Table 'test_lock.test_dead' doesn't exist<br><br>mysql> select * from t2;<br>+----+----------+------+<br>| id | name | age |<br>+----+----------+------+<br>| 1 | lisi | 11 |<br>| 2 | zhangsan | 22 |<br>+----+----------+------+<br>2 rows in set (0.00 sec)<br><br>-- 事务2 commit ,删除操作成功<br>mysql> commit;<br><br>mysql> select * from t2;<br>+----+----------+------+<br>| id | name | age |<br>+----+----------+------+<br>| 1 | lisi | 11 |<br>| 2 | zhangsan | 22 |<br>+----+----------+------+<br>2 rows in set (0.00 sec)<br></span></section>
- 查看死锁日志
-
ACTIVE 309秒 sec : 表示事务活动时间
-
starting index read : 表示读取索引
-
tables in use 1: 表示有一张表被使用了
-
LOCK WAIT 3 lock struct(s): 表示该事务的锁链表的长度为3,每个链表节点代表该事务持有的一个锁结构，包括表锁，记录锁以及 autoinc 锁等.
-
heap size 1136 : 为事务分配的锁堆内存大小
-
3 row lock(s): 表示当前事务持有的行锁个数/gap锁的个数
<section><span>LATEST DETECTED DEADLOCK<br>------------------------<br>2022-04-04 06:22:01 0x7fa66b39d700<br>*** (1) TRANSACTION: 事务1<br>TRANSACTION 16472, ACTIVE 309 sec starting index read<br>-- 事务编号 16472,活跃秒数 309,starting index read 表示事务状态为根据索引读取数据.<br><br>mysql tables in use 1, locked 1 <br>-- 表示有一张表被使用了 ,locked 1 表示表上有一个表锁,对于DML语句为LOCK_IX<br><br>LOCK WAIT 3 lock struct(s), heap size 1136, 3 row lock(s)<br>MySQL thread id 20, OS thread handle 140352739985152, query id 837 localhost root updating<br><br>update t2 set name = 'aaa' where id = 3 <br>--当前正在等待锁的SQL语句.<br><br>*** (1) WAITING FOR THIS LOCK TO BE GRANTED:<br>RECORD LOCKS space id 248 page no 3 n bits 72 index PRIMARY of table `test_lock`.`t2` trx id 16472 lock_mode X locks rec but not gap waiting<br>Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 32<br> 0: len 4; hex 80000003; asc ;;<br> 1: len 6; hex 000000004059; asc @Y;;<br> 2: len 7; hex 4100000193256b; asc A %k;;<br> 3: len 6; hex 77616e677775; asc wangwu;;<br> 4: len 4; hex 80000021; asc !;;<br><br>*** (2) TRANSACTION:<br>TRANSACTION 16473, ACTIVE 300 sec starting index read<br>mysql tables in use 1, locked 1<br>3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1<br>MySQL thread id 19, OS thread handle 140352740251392, query id 838 localhost root updating<br>delete from t2 where id = 1<br>*** (2) HOLDS THE LOCK(S):<br>RECORD LOCKS space id 248 page no 3 n bits 72 index PRIMARY of table `test_lock`.`t2` trx id 16473 lock_mode X locks rec but not gap<br>Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 32<br> 0: len 4; hex 80000003; asc ;;<br> 1: len 6; hex 000000004059; asc @Y;;<br> 2: len 7; hex 4100000193256b; asc A %k;;<br> 3: len 6; hex 77616e677775; asc wangwu;;<br> 4: len 4; hex 80000021; asc !;;<br><br>*** (2) WAITING FOR THIS LOCK TO BE GRANTED:<br>RECORD LOCKS space id 248 page no 3 n bits 72 index PRIMARY of table `test_lock`.`t2` trx id 16473 lock_mode X locks rec but not gap waiting<br>Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0<br> 0: len 4; hex 80000001; asc ;;<br> 1: len 6; hex 00000000403d; asc @=;;<br> 2: len 7; hex b0000001240110; asc $ ;;<br> 3: len 4; hex 6c697369; asc lisi;;<br> 4: len 4; hex 8000000b; asc ;<br></span></section>具体的演示过程，请参见《尼恩Java面试宝典》配套视频。
10 ：死锁产生的前提和建议
前提：
-
互斥：不能共享
-
持有并等待：当前事务保持至少一个资源，同时在等待获取其他资源。
-
**不可剥夺 **：已获得的资源不能被强制释放，只能由获取该资源的事务主动释放。
-
**循环等待：**系统中若干事务之间形成了一个循环等待资源的链。
死锁的关键在于：两个(或以上)的Session加锁的顺序不一致。
那么对应的解决死锁问题的关键就是：让不同的session加锁有次序
建议：
-
一致性排序：
对索引加锁顺序的不一致很可能会导致死锁, 所以如果可以, 尽量以相同的顺序来访问索引记录和表.
在程序以批量方式处理数据的时候, 如果事先对数据排序, 保证每个线程按固定的顺序来处理记录, 也可以大大降低出现死锁的可能.
-
间隙锁
往往是程序中导致死锁的真凶, 由于默认情况下 MySQL 的隔离级别是 RR(Repeatable Read，可重复读),所以如果能确定幻读和不可重复读对应用的影响不大, 可以考虑将隔离级别改成 RC, 可以避免 Gap 锁导致的死锁.
-
为表添加合理的索引, 如果不走索引将会为表的每一行记录加锁, 死锁的概率就会大大增大.
-
避免大事务, 尽量将大事务拆成多个小事务来处理.
因为大事务占用资源多, 耗时长, 与其他事务冲突的概率也会变高.
-
避免在同一时间点运行多个对同一表进行读写的脚本, 特别注意加锁且操作数据量比较大的语句.
-
超时和重试机制：设置锁等待超时参数，
innodb_lock_wait_timeout，在并发访问比较高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖跨数据库。
我们通过设置合适的锁等待超时阈值，可以避免这种情况发生。
11：线上发生了死锁，应该如何具体操作？
数据库的死锁是指不同的事务在获取资源时相互等待，导致无法继续执行的情况。
MySQL中可能发生死锁的情况包括事务同时更新多个表、事务嵌套、索引顺序不一致以及不同事务同时更新相同的索引等。
虽然数据库有死锁的预防策略，以及自动的处理措施。但是，在线上很多场景下， 数据的的死锁预防策略和回滚策略 ， 通常达不到预期的效果。
如果线上发生了死锁，我们应该采取以下步骤进行处理：
11.1 监控死锁
通过数据库的监控工具或命令查看是否存在死锁情况，了解死锁的具体情况，包括死锁的事务和死锁的资源。
step1：查看当前正在等待锁的事务
<section><span><span>SELECT</span> * <span>FROM</span> INFORMATION_SCHEMA.INNODB_LOCK_WAITS;<br></span></section>
运行以上SQL语句，可以查看当前正在等待锁的事务列表。
根据返回结果，可以分析哪些事务在等待哪些锁，以及等待锁的具体类型。
step2：查看当前持有的锁信息
<section><span><span>SELECT</span> * <span>FROM</span> INFORMATION_SCHEMA.INNODB_LOCKS;<br></span></section>
运行以上SQL语句，可以查看当前数据库中的锁信息。
通过分析返回结果，可以了解哪些锁正在被持有，以及锁的持有者和锁的类型。
step3：查看当前的死锁信息
<section><span><span>SHOW</span> <span>ENGINE</span> <span>INNODB</span> <span>STATUS</span>;<br></span></section>
运行以上SQL语句，可以显示当前的InnoDB存储引擎的状态信息。
其中包括死锁检测结果。如果存在死锁，可以通过分析该信息来解决死锁问题。
具体的演示过程，请参见《尼恩Java面试宝典》配套视频。
11.2 终止死锁事务
一旦发现死锁，需要找到造成死锁的事务，并选择其中一个事务终止。可以根据事务的执行时间、影响行数、优先级等因素进行终止决策。
可以采取以下方法来解决死锁问题：
-
回滚事务：
使用以下命令回滚某个事务以解除死锁：
<section><span>ROLLBACK;<br></span></section> -
杀死进程：
使用以下命令查找引起死锁的进程：
<section><span>SHOW PROCESSLIST;<br></span></section>找到引起死锁的进程ID后，使用以下命令杀死该进程：
<section><span>KILL <process_id>;<br></span></section>
具体的演示过程，请参见《尼恩Java面试宝典》配套视频。
11.3 重试事务
终止死锁事务后，需要重新执行被终止的事务。
重试事务 之前，需要调整事务顺序
这可能需要一些逻辑处理，例如对数据进行回滚或者重新执行一些操作。
11.4 防止死锁再次发生
通过数据库的日志和监控信息，分析死锁的原因。
可以根据死锁原因对数据库的设计和代码进行优化，以尽量减少死锁的发生。
根据分析结果，针对性地进行数据库结构调整、索引优化、事务隔离级别调整等措施，以降低死锁的概率。
说在最后：有问题找老架构取经
MySQL死锁是什么，如何解决？如果大家能对答如流，如数家珍，基本上 面试官会被你 震惊到、吸引到。
最终，让面试官爱到 “不能自已、口水直流”。offer， 也就来了。
在面试之前，建议大家系统化的刷一波 5000页《尼恩Java面试宝典PDF》，里边有大量的大厂真题、面试难题、架构难题。很多小伙伴刷完后， 吊打面试官， 大厂横着走。
在刷题过程中，如果有啥问题，大家可以来 找 40岁老架构师尼恩交流。
另外，如果没有面试机会，可以找尼恩来改简历、做帮扶。
遇到职业难题，找老架构取经， 可以省去太多的折腾，省去太多的弯路。
尼恩指导了大量的小伙伴上岸，前段时间，刚指导一个40岁+被裁小伙伴，拿到了一个年薪100W的offer。
狠狠卷，实现 “offer自由” 很容易的， 前段时间一个武汉的跟着尼恩卷了2年的35岁大龄小伙伴， 在极度严寒/痛苦被裁的环境下， offer拿到手软、手软、手软， 实现真正的 “offer自由” 。
部分历史案例
-
**绝地逢生：**9年经验自考小伙伴，跟着尼恩狠卷3月硬核技术，面试机会爆表，2周后收3个offer ，满血复活
- 原文作者：知识铺
- 原文链接：https://index.zshipu.com/geek001/post/20240424/MySQL%E6%AD%BB%E9%94%81%E9%97%AE%E9%A2%98%E8%AF%A6%E8%A7%A3%E5%8F%8A%E8%A7%A3%E5%86%B3%E6%96%B9%E6%A1%88--%E7%9F%A5%E8%AF%86%E9%93%BA/
- 版权声明：本作品采用知识共享署名-非商业性使用-禁止演绎 4.0 国际许可协议进行许可，非商业转载请注明出处（作者，原文链接），商业转载请联系作者获得授权。
- 免责声明：本页面内容均来源于站内编辑发布，部分信息来源互联网，并不意味着本站赞同其观点或者证实其内容的真实性，如涉及版权等问题，请立即联系客服进行更改或删除，保证您的合法权益。转载请注明来源，欢迎对文章中的引用来源进行考证，欢迎指出任何有错误或不够清晰的表达。也可以邮件至 sblig@126.com
