如何解决MySQL死锁（看懂MySQL锁日志）

数据运维 2024-03-30 Escape 手机阅读

​

有时候系统在生产运行着，会突然爆出

[40001][1213] Deadlock found when trying to get lock; try restarting transaction

这个时候每个人都会很紧张，因为死锁会影响DB性能，严重时甚至拖垮整个系统。在实际的环境中，很多服务会共用一个数据库，一旦数据库挂了，基本就是P0事故。

那么，死锁发生时，我们如何定位到死锁发生的SQL？

死锁排查

实操前置准备

磨刀不误砍柴功，我们先准备下实验环境。

首先创建一张表：

create table users(
    id int comment "id",
    age int comment '年龄',
    id_no int comment '身份证号',
    UNIQUE KEY `uk_task_obj` (`id_no`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='用户表';

如何解决MySQL死锁（看懂MySQL锁日志）-每日运维

写入数据：

insert into users values (1,18,1);
insert into users values (2,18,2);
insert into users values (3,18,3);

如何解决MySQL死锁（看懂MySQL锁日志）-每日运维

接着，我们开两个事务：

事务一：

begin;
select * from users where id_no=1 for update;
select * from users where id_no=3 for update ;
commit;

如何解决MySQL死锁（看懂MySQL锁日志）-每日运维

事务二：

begin;
select * from users where id_no=3 for update ;
select * from users where id_no=1 for update ;
commit ;

如何解决MySQL死锁（看懂MySQL锁日志）-每日运维

image.png

可以看到，两个事务执行的语句都一样，只不过顺序不一样，我们按照以下时序去执行时，终端会提示Deadlock found when trying to get lock; try restarting transaction

查看日志

要定位死锁发生的原因，我们需要知道，是哪些事务持有了哪些锁，哪些事务又互相阻塞。

我们可以通过

SHOW ENGINE INNODB STATUS;

如何解决MySQL死锁（看懂MySQL锁日志）-每日运维

来查看死锁发生时的日志

日志分析

当执行SHOW ENGINE INNODB STATUS时，MySQL返回如下日志：

如何解决MySQL死锁（看懂MySQL锁日志）-每日运维​编辑

日志内容很多，我们主要关注：LATEST DETECTED DEADLOCK 这一部分。我们逐步讲解下这一部分的日志。

如何解决MySQL死锁（看懂MySQL锁日志）-每日运维​

日志解释
LATEST DETECTED DEADLOCK------------------------2024-03-18 21:07:00 0x16be8b000*** (1) TRANSACTION:TRANSACTION 1836, ACTIVE 10 sec starting index readTRANSACTION 1836：1836代表事务id；active 10 sec：表示活跃时间
LOCK WAIT 4 lock struct(s), heap size 1128, 3 row lock(s)MySQL thread id 17, OS thread handle 6121680896, query id 2044 localhost 127.0.0.1 root statistics不重要，忽略
/* ApplicationName=GoLand 2023.2.2 */ select * from users where id_no=3 for update这个事务执行的sql语句
*** (1) HOLDS THE LOCK(S):RECORD LOCKS space id 2 page no 5 n bits 72 index uk_task_obj of table test.users trx id 1836 lock_mode X locks rec but not gapRecord lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000001; asc     ;; 1: len 6; hex 000000000200; asc       ;;HOLDS THE LOCK：表示当前事务持有的锁。RECORD LOCKS：表明是记录锁space id 2 page no 5 n bits 72 ：这是mysql底层存储的位置，我们可以不理解。index uk_task_obj of table test.users trx id 1836 lock_mode X locks rec but not gap：表明是索引uk_task_obj上的锁。X锁代表互斥锁，rec but not gap表示是记录锁不是间隙锁。
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 2 page no 5 n bits 72 index uk_task_obj of table test.users trx id 1836 lock_mode X locks rec but not gap waitingRecord lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000003; asc     ;; 1: len 6; hex 000000000202; asc       ;;WAITING FOR THIS LOCK TO BE GRANTED：这句话就说了，这个事务在等待索引uk_task_obj上的一个记录锁，下面是等待的锁信息。
*** (2) TRANSACTION:TRANSACTION 1837, ACTIVE 7 sec starting index readmysql tables in use 1, locked 1LOCK WAIT 4 lock struct(s), heap size 1128, 3 row lock(s)MySQL thread id 18, OS thread handle 6120566784, query id 2058 localhost 127.0.0.1 root statistics/* ApplicationName=GoLand 2023.2.2 */ select * from users where id_no=1 for update第一个事务在等待锁，这个时候提到第二个事务了。事务id是1837，执行的语句是select * from users where id_no=1 for update
*** (2) HOLDS THE LOCK(S):RECORD LOCKS space id 2 page no 5 n bits 72 index uk_task_obj of table test.users trx id 1837 lock_mode X locks rec but not gapRecord lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000003; asc     ;; 1: len 6; hex 000000000202; asc       ;;1837事务持有的锁也是记录锁，也是在唯一索引uk_task_obj上。锁的信息如下：**0: len 4; hex 80000003; asc     ;;**1: len 6; hex 000000000202; asc       ;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 2 page no 5 n bits 72 index uk_task_obj of table test.users trx id 1837 lock_mode X locks rec but not gap waitingRecord lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000001; asc     ;; 1: len 6; hex 000000000200; asc       ;;WAITING FOR THIS LOCK TO BE GRANTED:等待锁Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000001; asc     ;; 1: len 6; hex 000000000200; asc等待一个记录锁，可以看到，事务1836和1837持有和等待的锁是相反的，因而发生死锁
*** WE ROLL BACK TRANSACTION (2)MySQL选择回滚第二个事务

常见的MySQL死锁场景

上面的例子，是最常见的由于select for update产生的死锁问题，以下还有几种发生死锁的场景。

批量update

例如：insert into users values(1,2,3),(2,2,3),(3,3,3)和insert into users values(3,2,3),(2,2,3),(1,3,3)同时执行时，会由于锁的冲突会导致死锁。表面是看insert into users values(1,2,3),(2,2,3),(3,3,3)是一条语句，实际上MySQL并不是一次性加完全部锁，它会按照SQL的书写顺序逐步加锁。解决方法是在批量插入之前，我们按一定规则排序，只要两条sql按相同的顺序加锁便不会有死锁问题

update退化为共享锁

在MySQL中，update语句加的是排它锁，也就是X锁。如果此时另外一个事务正在执行select语句，对同一个目标加了共享锁之后，执行update的事务会由于加X锁失败，转而变为共享锁。

此时会发生如下情况：

如何解决MySQL死锁（看懂MySQL锁日志）-每日运维​

事务1执行完第一步之后，已经持有A的共享锁；

事务2执行第二步，由于排它锁加锁失败，转为持有A的共享锁，同时等待事务1释放共享锁；

事务3执行第三步，要将共享锁升级为排它锁，等待事务2释放共享锁。

此时事务1和2发生了循环等待，导致死锁发生。

总结：

  • 通过SHOW ENGINE INNODB STATUS查看MySQL死锁日志
  • select for update是最常见的死锁场景
  • 批量update时注意加锁顺序、小心update的排它锁退化成共享锁导致死锁发生

    • 诚意满满系列每一篇都是精挑细选，从大众知识点到原理再到具体实现，争取把一个知识点从头到尾完整讲下来，足以应付面试与工作。让读者读完之后能够有一种：“这个知识我看这一篇就够了”的感觉是本系列最大愿望。

    对于本人而言，在之前的学习中也发现，八股文讲得细致但不系统，而系统的学习往往又宽泛不细致，所以也打算取长补短，互相结合一下，欢迎大家收藏关注，持续更新。

    ​