MySQL死锁分析

当业务并发比较高时，如果数据库访问设计得不合理，可能时不时就爆出一个死锁错误。业务上表现为一个偶现的失败。这种情况，有时候非常让人抓狂，感觉无从入手。这里就介绍一下对MySQL死锁的理解，并提出一个基于审计日志分析死锁的方法。

一、死锁场景

我们创建一个最简单的死锁场景

1. 创建表

CREATE TABLE `t1` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`c_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

`name` varchar(64) NOT NULL DEFAULT '',

PRIMARY KEY (`id`)

);

CREATE TABLE `t2` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`c_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

`name` varchar(32) DEFAULT '',

PRIMARY KEY (`id`)

);

2.插入数据

insert into `t1` (name) values ('mercury');

insert into `t2` (name) values ('venus');

3.执行SQL

到步骤6时MySQL检查到死锁，报错：ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction 会话2的事务被回滚掉了。

二、死锁日志解读

mysql> show engine innodb status\G

*************************** 1. row ***************************

Type: InnoDB

Name:

Status:

=====================================

2021-01-23 01:44:34 0x7ff01c16c700 INNODB MONITOR OUTPUT

...略

------------------------

LATEST DETECTED DEADLOCK

------------------------

2021-01-23 01:44:22 0x7ff01c0e8700

*** (1) TRANSACTION:

事务Id是48993，处于Active状态103秒

TRANSACTION 48993, ACTIVE 103 sec starting index read

此事务目前使用了一个表，对一个表加锁

mysql tables in use 1, locked 1

事务处于LOCK WAIT。有4个锁结构体，其中两个是行锁。有3条undo log

LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s),

undo log entries 3

线程Id是19，操作系统线程句柄是140669239052032。查询Id是683，是内部使用的一个Id。

MySQL thread id 19, OS thread handle 140669239052032, query id 683 127.0.0.1 root updating

这个比较关键。是当前等锁的语句。

update t2 set c_time = now() where id = 1

当前期望获得的锁信息

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:

等待的锁表空间为50，页号为3，n_bits是记录锁定行的位图的大小(通过[space, page_no, heap_no]可以唯一确定一行。为节省锁存储空间，因此通过位图表示锁哪些行被锁定)。是在表`dbaas`.`t2`的主键上的行锁。

RECORD LOCKS space id 50 page no 3 n bits 168 index PRIMARY of table `dbaas`.`t2` trx id 48993 lock_mode X locks rec but not gap waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0

主键列。这个80000001可以看出是主键为1

0: len 4; hex 80000001; asc ;;

1: len 6; hex 00000000bf62; asc b;;

2: len 7; hex 44000001ec131f; asc D ;;

3: len 4; hex 600b7f29; asc ` );;

4: len 5; hex 6e616d6530; asc name0;;

*** (2) TRANSACTION:

事务Id是48994，处于Active状态77秒

TRANSACTION 48994, ACTIVE 77 sec starting index read

此事务目前使用了一个表，对一个表加锁

mysql tables in use 1, locked 1

事务处于LOCK WAIT。有4个锁结构体，其中两个是行锁。Undo log长度为1

4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1

MySQL thread id 20, OS thread handle 140669239592704, query id 684 127.0.0.1 root updating

这个比较关键。是当前等锁的语句。

update t1 set c_time = now() where id = 1

*** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 50 page no 3 n bits 168 index PRIMARY of table `dbaas`.`t2` trx id 48994 lock_mode X locks rec but not gap

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 00000000bf62; asc b;;

2: len 7; hex 44000001ec131f; asc D ;;

3: len 4; hex 600b7f29; asc ` );;

4: len 5; hex 6e616d6530; asc name0;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 51 page no 3 n bits 72 index PRIMARY of table `dbaas`.`t1` trx id 48994 lock_mode X locks rec but not gap waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0

这个锁其实是被事务TRANSACTION 48993获取了。

0: len 4; hex 80000001; asc ;;

1: len 6; hex 00000000bf61; asc a;;

2: len 7; hex 43000001f709aa; asc C ;;

3: len 4; hex 600b7f67; asc ` g;;

4: len 5; hex 6e616d6530; asc name0;;

5: len 0; hex ; asc ;;

*** WE ROLL BACK TRANSACTION (2)

...略

三、MySQL死锁检测

MySQL的死锁检测是通过wait-for graph来实现的，它是一个有向图。图的节点表示一个事务，图的边由等待锁的事务，指向已经持有锁的事务。当图中存在环路，则存在死锁。环路检测采取深度优先搜索的算法。为避免开销过大，当搜索深度超过200时，也认为是有环。

比如上面死锁场景中的case，其wait-for graph可以表示下图。所以存在死锁。

死锁示意图

在MySQL中，当开启死锁检测时，即innodb_deadlock_detect设置为ON时，每个事务请求锁并发生锁等待的时候，都会进行死锁检测。当发生死锁时，会选择权重较低的事务进行回滚。通常情况下，如果一个事务修改了不支持事务的表，则其权重较高；如果修改的都是支持事务的表，则undo log数量+持有锁数量较多的事务权重较高。这里需要注意的是，在选择回滚事务的时候，并不是在整个事务环路中寻找，而仅仅是在遍历的第一个节点和最后一个节点中选择。比如对于下面这种case。已有事务t1等待t2，t2等待t3，当事务t3请求锁等待t1时，深度优先遍历将是t3-->t1-->t2-->t3的顺序，则回滚事务只会在t2和t3中间选择。

死锁检测顺序

锁等待的关系，可以通过表innodb_lock_waits观察。

innodb_lock_waits表信息

每个锁都有且仅有一个事务持有。一个事务获取一个锁时，被另外一个锁阻塞，等价于等待另外一个事务。

四、如何分析死锁

通常的分析，都是看死锁日志，在结合业务执行的逻辑进行分析。如果只是上文中的简单case，还是比较容易的。但复杂的，多个事务相互依赖的情况，通过死锁日志提供的信息，再加上当时分析业务逻辑，可能也不是很容易。

五、通过审计日志分析死锁

分析死锁的根本目标，是找到业务如何执行SQL，构成死锁的。它应该是几组SQL，每组SQL在一个会话中执行。这些SQL按照一定次序执行，便会构成死锁。

如果存在完整审计日志，就有当时执行的所有的SQL语句。

另外一部分信息是死锁的日志。它主要包含wait-for graph环路中最后的两个节点的信息。

一种简单暴力的方法是，将数据库还原到死锁发生的时刻，关闭innodb_deadlock_detect并且重放死锁时间点的日志。通过分析innodb_lock_waits+innodb_trx中的信息，可以得到造成死锁的SQL信息。

另外一种是解析法。完整复现当时的锁等待情况，需要以下的条件：

1.SQL执行流

2.表结构及数据

3.MySQL配置。比如autocommit、transaction_isolation

4.MySQL的版本

解析法需要通过以上信息还原出wait-for graph，这需要许多innodb锁子系统的相关知识。这部分后续再单独写下~

六、如何避免死锁

从通用的角度讲，还是有许多的措施可以采用，避免死锁：

1.以固定的顺序访问表和行，从而以固定的顺序加锁，避免了交叉等待锁的情形。因为死锁的根本原因是有两个或多个事务之间加锁顺序的不一致导致的

2.尽量不使用大事务，拆解大事务，将降低死锁的概率。并且，大事务还可能会造成锁拥塞、等锁超时、回滚时间长、主备延迟等问题，需要避免

3.在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率

4.合理添加表索引。如果不走索引的话，将会给表的每一条记录加锁，死锁的概率大大增大