炸裂：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锁的分类

表级锁：

• 开销小，加锁快
• 不会出现死锁
• 锁定粒度大，发生锁冲突的概率最高，并发度最低

行级锁：

• 开销大，加锁慢
• 会出现死锁
• 锁定粒度小，发生锁冲突的概率最低，并发度最高

页级锁：

• 开销和加锁时间介于表锁和行锁之间
• 会出现死锁
• 锁定粒度介于表锁和行锁之间，并发度一般

3 从操作的类型进行的mysql锁的分类

读锁（S锁）

写锁（X锁）

意向锁

• IS: 意向共享锁，表级锁，已加S锁的表，肯定会有IS锁，反过来，有IS锁的表，不一定会有S锁
• IX: 意向排它锁，表级锁，已加X锁的表，肯定会有IX锁，反过来，有IX锁的表，不一定会有X锁

4 从操作的性能进行的mysql锁的分类

• 乐观锁：一般的实现方式是对记录数据版本进行比对，在数据更新提交的时候才会进行冲突检测，如果发现冲突了，则提示错误信息。
• 悲观锁：在对一条数据修改的时候，为了避免同时被其他人修改，在修改数据之前先锁定，再修改的控制方式。共享锁和排他锁是悲观锁的不同实现，但都属于悲观锁范畴。

5 InnoDB存储引擎三种行锁模式

• RecordLock锁(行锁)：锁定单个行记录的锁。（RecordLock锁 是记录锁，RC、RR隔离级别都支持）
• GapLock锁：间隙锁，锁定索引记录间隙(不包括记录本身)，确保索引记录的间隙不变。（GapLock是范围锁，RR隔离级别支持。RC隔离级别不支持）
• Next-key Lock 锁(临键锁)：记录锁和间隙锁组合，同时锁住数据，并且锁住数据前后范围。（记录锁+范围锁，RR隔离级别支持。RC隔离级别不支持）

5.1 记录锁(Record Locks)

5.2 间隙锁(Gap Locks)

5.3 行锁：临键锁(Next-Key Locks)

具体的讲解，请参见《尼恩Java面试宝典》配套视频。

6 事务隔离级别和锁的关系

6.1 数据库事务的隔离级别

• 未提交读（READ UNCOMMITTED）：所有事务都可以看到其他事务未提交的修改。一般很少使用；
• 读已提交（READ COMMITTED）：Oracle默认隔离级别，事务之间只能看到彼此已提交的变更修改；
• 可重复读（REPEATABLE READ）：MySQL默认隔离级别，同一事务中的多次查询会看到相同的数据行；可以解决不可重复读，但可能出现幻读；
• 可串行化（SERIALIZABLE）：最高的隔离级别，事务串行的执行，前一个事务执行完，后面的事务会执行。读取每条数据都会加锁，会导致大量的超时和锁争用问题；

6.2 事务隔离级别和锁的关系

1. 事务隔离级别是SQL92定制的标准，相当于事务并发控制的整体解决方案，本质上是对锁和MVCC使用的封装，隐藏了底层细节。
2. 锁是数据库实现并发控制的基础，事务隔离性是采用锁来实现，对相应操作加不同的锁，就可以防止其他事务同时对数据进行读写操作。
3. 对用户来讲，首先选择使用隔离级别，当选用的隔离级别不能解决并发问题或需求时，才有必要在开发中手动的设置锁。
   MySQL 默认隔离级别：可重复读， 一般建议改为 RC 读已提交
   Oracle、SQLServer默认隔离级别：读已提交

7 死锁产生原因和解决方案

• 支持事务
• 采用行锁

7.1 查看Innodb行锁争用情况

• 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 详细介绍 死锁的概念

• 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）解决方案：

7.4 行级锁死锁

a) 产生原因1：

b) 产生原因2：

c) 产生原因3：每个事务只有一个SQL,但是有些情况还是会发生死锁.

create table t1(
    id int(32) not null,
    name varchar(50) not null,
    reg_time int(32) not null,
    city varchar(50) ,
    primary key (`name`),
    index index_name(`name`),
    index index_reg_time(`reg_time`)
);

update t1 set city = "香港"  where name="aaa"

Select * from t1  where reg_time>=1000 for update

具体的讲解，请参见《尼恩Java面试宝典》配套视频。

8：InnoDB预防死锁策略

• 根据给定的查询条件，找到对应的记录所在页面；
• 对页面加上X锁(RWLock)，然后在页面内寻找满足条件的记录；
• 在持有页面锁的情况下，对满足条件的记录加事务锁(行锁：根据记录是否满足查询条件，记录是否已经被删除，分别对应于上面提到的3种加锁策略之一)；

InnoDB预防死锁策略

• 持有事务锁(行锁、表锁)，可以等待获取页面锁；
• 但反之，持有页面锁，不能等待持有事务锁。

• 当一个事务请求某个数据页的锁时，锁管理器会检查当前锁的状态以及其他事务是否持有或等待相同的锁。
• 如果存在潜在的死锁风险，系统会通过死锁检测算法来检测并解决死锁。其中，常用的死锁检测算法包括等待图（Wait-for graph）算法和超时算法。

9 ：死锁案例分析

9.1 案例一：拆借款

• A用户金额随机分为2份，分给借款人小明，小亮，
• B用户金额随机分为2份，分给借款人小亮，小明

mysql> select * from t3 where id in (8,9) for update;
+----+--------+------+---------------------+
| id | course | name | ctime               |
+----+--------+------+---------------------+
|  8 | WA     | f    | 2016-03-02 11:36:30 |
|  9 | JX     | f    | 2016-03-01 11:36:30 |
+----+--------+------+---------------------+
rows in set (0.04 sec)

select * from t3 where id in (10,8,6) for update;
锁等待中……

mysql> select * from t3 where id=6 for update;
锁等待中

mysql> select * from t3 where id=10 for update;
+----+--------+------+---------------------+
| id | course | name | ctime               |
+----+--------+------+---------------------+
| 10 | JB     | g    | 2016-03-10 11:45:05 |
+----+--------+------+---------------------+
row in set (0.00 sec)

9.2 案例二：有则插入无则更新

• 根据字段值查询（有索引），如果不存在，则插入；
• 否则更新。

以id为主键为例，目前还没有id=22的行

Session1:
select * from t3 where id=22 for update;
Empty set (0.00 sec)

session2:
select * from t3 where id=23  for update;
Empty set (0.00 sec)

Session1:
insert into t3 values(22,'ac','a',now());
锁等待中……

Session2:
insert into t3 values(23,'bc','b',now());
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

• 当对存在的行 进行锁的时候(主键)，mysql就只有行锁。
• 当对未存在的行进行锁的时候(即使条件为主键)，mysql是会锁住一段范围（有gap锁），也就是间隙锁(Gap Locks)

• 如果表中目前有已有的id为（11 ， 12），那么就锁住（12，无穷大）
• 如果表中目前已有的id为（11 ， 30），那么就锁住（11，30）

9.3 案例三 死锁日志分析

--创建表 t2
create table t2(
  id int primary key,
  name varchar(50),
  age int
);
--插入数据
insert into t2 values(1,'lisi',11),(2,'zhangsan',22),(3,'wangwu',33);

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+

-- information_schema.innodb_trx: 当前出现的锁
select * from information_schema.innodb_locks;
-- information_schema.innodb_trx: 当前运行的所有事务
select * from information_schema.innodb_trx;
-- information_schema.innodb_lock_waits: 锁等待的对应关系
select * from information_schema.innodb_lock_waits;

show engine innodb status;

场景1:

1. 事务1, 执行begin开始事务执行一条SQL,查询 id=1 的数据

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t2 where id = 1 for update;
+----+------+------+
| id | name | age |
+----+------+------+
|  1 | lisi |  11 |
+----+------+------+
1 row in set (0.00 sec)

1. 事务1进行首先申请IX锁 (意向排它锁,因为是for update)
2. 然后申请X锁进行查询是否存在 id = 1 的记录
3. 存在该记录,因为id字段是唯一索引,所以添加的是 Record Lock
4. 查看 information_schema.innodb_trx表,发现存在事务1 的信息

select trx_id '事务id',trx_state '事务状态',
       trx_started '事务开始时间',trx_weight '事务权重',
       trx_mysql_thread_id '事务线程ID',
       trx_tables_locked '事务拥有多少个锁',
       trx_lock_memory_bytes '事务锁住的内存大小',
       trx_rows_locked '事务锁住的行数',
       trx_rows_modified '事务更改的行数'
  from information_schema.innodb_trx;

3. 执行事务2的 delete语句, 删除成功,因为id=3的数据并没有被加锁

mysql> delete from t2 where id = 3; -- 删除成功

4. 事务1对 id=3 的记录进行修改操作,发生阻塞. 因为id=3的数据的X锁已经被事务2拿到,其他事务的操 作只能被阻塞.

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t2 where id = 1 for update;
+----+------+------+
| id | name | age |
+----+------+------+
|  1 | lisi |  11 |
+----+------+------+
1 row in set (0.00 sec)
mysql> update t2 set name = 'aaa' where id = 3;
-- 阻塞

5. 查看当前锁信息

-- 查看当前锁信息
select
lock_id '锁ID',
 lock_trx_id '拥有锁的事务ID',
 lock_mode '锁模式',
 lock_type '锁类型' ,
 lock_table '被锁的索引',
 lock_space '被锁的表空间号',
 lock_page '被锁的页号',
 lock_rec '被锁的记录号',
 lock_data '被锁的数据'
from information_schema.innodb_locks;

-- 查看锁等待的对应关系
select requesting_trx_id '请求锁的事务ID',
       requested_lock_id '请求锁的锁ID',
       blocking_trx_id '当前拥有锁的事务ID',
       blocking_lock_id '当前拥有锁的锁ID'
   from information_schema.innodb_lock_waits;

6. 事务2 执行删除操作,删除 id = 1的数据成功.

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from t2 where id = 3;
Query OK, 1 row affected (0.00 sec)

mysql> delete from t2 where id = 1;
Query OK, 1 row affected (0.00 sec)

7. 但是事务1已经检测到了死锁的发生

mysql> update t2 set name = 'aaa' where id = 3;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

--事务1 commit,更新操作失败
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_dead;
-- ERROR 1146 (42S02): Table 'test_lock.test_dead' doesn't exist

mysql> select * from t2;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | lisi     |   11 |
|  2 | zhangsan |   22 |
+----+----------+------+
2 rows in set (0.00 sec)

-- 事务2 commit ,删除操作成功
mysql> commit;

mysql> select * from t2;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | lisi     |   11 |
|  2 | zhangsan |   22 |
+----+----------+------+
2 rows in set (0.00 sec)

8. 查看死锁日志

• 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锁的个数

  LATEST DETECTED DEADLOCK
  ------------------------
  2022-04-04 06:22:01 0x7fa66b39d700
  *** (1) TRANSACTION: 事务1
  TRANSACTION 16472, ACTIVE 309 sec starting index read
  -- 事务编号 16472,活跃秒数 309,starting index read 表示事务状态为根据索引读取数据.
  
  mysql tables in use 1, locked 1  
  -- 表示有一张表被使用了 ,locked 1 表示表上有一个表锁,对于DML语句为LOCK_IX
  
  LOCK WAIT 3 lock struct(s), heap size 1136, 3 row lock(s)
  MySQL thread id 20, OS thread handle 140352739985152, query id 837 localhost root updating
  
  update t2 set name = 'aaa' where id = 3 
  --当前正在等待锁的SQL语句.
  
  *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
  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
  Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
   0: len 4; hex 80000003; asc     ;;
   1: len 6; hex 000000004059; asc     @Y;;
   2: len 7; hex 4100000193256b; asc A    %k;;
   3: len 6; hex 77616e677775; asc wangwu;;
   4: len 4; hex 80000021; asc    !;;
  
  *** (2) TRANSACTION:
  TRANSACTION 16473, ACTIVE 300 sec starting index read
  mysql tables in use 1, locked 1
  3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
  MySQL thread id 19, OS thread handle 140352740251392, query id 838 localhost root updating
  delete from t2 where id = 1
  *** (2) HOLDS THE LOCK(S):
  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
  Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
   0: len 4; hex 80000003; asc     ;;
   1: len 6; hex 000000004059; asc     @Y;;
   2: len 7; hex 4100000193256b; asc A    %k;;
   3: len 6; hex 77616e677775; asc wangwu;;
   4: len 4; hex 80000021; asc    !;;
  
  *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
  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
  Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
   0: len 4; hex 80000001; asc     ;;
   1: len 6; hex 00000000403d; asc     @=;;
   2: len 7; hex b0000001240110; asc     $  ;;
   3: len 4; hex 6c697369; asc lisi;;
   4: len 4; hex 8000000b; asc     ;
  

  具体的演示过程，请参见《尼恩Java面试宝典》配套视频。

10 ：死锁产生的前提和建议

• 互斥：不能共享
• 持有并等待：当前事务保持至少一个资源，同时在等待获取其他资源。
• **不可剥夺 **：已获得的资源不能被强制释放，只能由获取该资源的事务主动释放。
• **循环等待：**系统中若干事务之间形成了一个循环等待资源的链。
  死锁的关键在于：两个(或以上)的Session加锁的顺序不一致。
  那么对应的解决死锁问题的关键就是：让不同的session加锁有次序

• 一致性排序：
  对索引加锁顺序的不一致很可能会导致死锁, 所以如果可以, 尽量以相同的顺序来访问索引记录和表.
  在程序以批量方式处理数据的时候, 如果事先对数据排序, 保证每个线程按固定的顺序来处理记录, 也可以大大降低出现死锁的可能.
• 间隙锁
  往往是程序中导致死锁的真凶, 由于默认情况下 MySQL 的隔离级别是 RR(Repeatable Read，可重复读),所以如果能确定幻读和不可重复读对应用的影响不大, 可以考虑将隔离级别改成 RC, 可以避免 Gap 锁导致的死锁.
• 为表添加合理的索引, 如果不走索引将会为表的每一行记录加锁, 死锁的概率就会大大增大.
• 避免大事务, 尽量将大事务拆成多个小事务来处理.
  因为大事务占用资源多, 耗时长, 与其他事务冲突的概率也会变高.
• 避免在同一时间点运行多个对同一表进行读写的脚本, 特别注意加锁且操作数据量比较大的语句.
• 超时和重试机制：设置锁等待超时参数，
  innodb_lock_wait_timeout，在并发访问比较高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖跨数据库。
  我们通过设置合适的锁等待超时阈值，可以避免这种情况发生。

11：线上发生了死锁，应该如何具体操作？

11.1 监控死锁

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

SHOW ENGINE INNODB STATUS;

具体的演示过程，请参见《尼恩Java面试宝典》配套视频。

11.2 终止死锁事务

• 回滚事务：
  使用以下命令回滚某个事务以解除死锁：

  ROLLBACK;
  

• 杀死进程：
  使用以下命令查找引起死锁的进程：

  SHOW PROCESSLIST;
  

  找到引起死锁的进程ID后，使用以下命令杀死该进程：

  KILL <process_id>;
  

具体的演示过程，请参见《尼恩Java面试宝典》配套视频。

11.3 重试事务

11.4 防止死锁再次发生

说在最后：有问题找老架构取经

部分历史案例

• 绝地逢生：9年经验自考小伙伴，跟着尼恩狠卷3月硬核技术，面试机会爆表，2周后收3个offer ，满血复活

• 硬转架构：35岁被裁6个月 职业绝望，转架构急救上岸，DDD和3高项目太重要了
  

• 逆天改命：3年经验2本，卷3个月涨薪60%，进准大厂(得物、乐信、58集团等)，年薪36W

• 女35岁CRUD程序媛，12月被裁后，拿30个机会2个offer上岸，年底大裁员太不容易了

• 38还有救吗？有。42岁被裁2年天快塌了，急救1个月上岸，成开发经理offer，起死回生

• 被裁不慌，9年小伙1个月喜提年薪60W offer，做中间件架构，爽歪了

• 12年小伙转架构，1小时蜕变收年薪55万offer，秘诀：GO+Java双栖架构

• 年薪百万：40岁小伙被裁6个月，跟着尼恩猛卷3月硬核技术，100W年薪逆袭 ，上岸秘诀：首席架构/总架构

• 不怕裁员，8年小伙去中年危机，秘诀：换架构师赛道，上得厅堂，下得厨房，越早越好

• 被裁员后因祸得福，拿N+1后，逆涨30%，如何实现？

• 逆天啦：4年crud小伙收shein+银行两优质offer，狠卷1月收年薪43万大涨30%

• 原来，拿大厂offer 有捷径......

• 太劲爆.... 被裁4个月，38岁Android转Java，2个月提架构offer

• 起死回生：8年小伙高中毕业+频繁跳槽，狠卷2月，提Java高级offer

• 爽翻了：指导3轮，5年小伙收5大offer，涨50%，领路模式太牛

• 降维攻击，37年大龄老伙喜提60W年薪offer，1个月顺利上岸

• 架构速成：从一面杀到一次过，记7年女架构的速成经历

• 极速拿offer：阿里P6被裁后极速上岸，1个月内喜提2优质offer(含滴滴)

• 大逆袭：做化工12年，35岁转Java，被裁4个月后，1个月收架构Offer，大龄跨行，超级牛

• 被裁后炸爽：10年小伙12天火速上岸，反涨20%，爽暴了

• 裁就裁，6年小伙60W极速上岸，白拿20W还游一圈拉萨，真香

• 极速拿offer：被毕业3个月，11年经验小伙0.5个月极速拿offer，领路模式的巨大威力

• 惊天大逆袭：8年小伙20天时间提75W年薪offer，逆涨50%，秘诀在这