MySQL之如何排查死锁

写在前面

死锁的条件就是形成了循环等待，即你等我，我等你，如下图：

在MySQL中也是如此，接下来我们就一起看下，针对MySQL的死锁如何排查。

1：数据准备

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);
1
2
3
4
5
6
7
8
9
10

2：正戏

• 准备3个会话A，B，C
• 在会话A启动事务，并查看生成的事务ID

mysql> start transaction with consistent snapshot;
Query OK, 0 rows affected (0.04 sec)
mysql> select trx_id from information_schema.innodb_trx order by trx_started desc limit 1;
+--------+
| trx_id |
+--------+
| B3915  |
+--------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9

可以看到会话A对应的事务ID是B3915，记录这个值，后面分析需要用到。

• 在会话A执行操作，并查看此时事务

mysql> update t set c=c+1 where id=5;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> show engine innodb status\G
...可通过搜索B3915快速定位日志
------------
TRANSACTIONS
------------
Trx id counter B3916
Purge done for trx's n:o < B3915 undo n:o < 0
History list length 1926
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION B3915, ACTIVE 391 sec
2 lock struct(s), heap size 320, 1 row lock(s), undo log entries 1
MySQL thread id 7, OS thread handle 0xcf54, query id 110 localhost 127.0.0.1 root
show engine innodb status
Trx read view will not see trx with id >= B3916, sees < B3916
...
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

信息分析如下：

Trx id counter B3916：
    下一个可分配的事务ID
---TRANSACTION B3915, ACTIVE 391 sec：
    当前事务ID是B3915，活跃时长是391秒
1 row lock(s), undo log entries 1：
    拥有1个锁，本事务产生的undo log的长度是1
show engine innodb status：
    当前事务执行的最后一个sql语句
Trx read view will not see trx with id >= B3916, sees < B3916：
    一致性事务看不到事务ID大于等于B3916的事务产生的修改，看的到事务ID小于B3916的修改
1
2
3
4
5
6
7
8
9
10

• 在会话B启动事务，并查看生成的事务ID

mysql> start transaction with consistent snapshot;
Query OK, 0 rows affected (0.00 sec)

mysql> select trx_id from information_schema.innodb_trx order by trx_started desc limit 1;
+--------+
| trx_id |
+--------+
| B3916  |
+--------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10

可以看到会话A对应的事务ID是B3916，记录这个值，后面分析需要用到。

• 在会话B执行操作

mysql> update t set c=c+1 where id=10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
1
2
3

• 在会话C查看事务状态

---TRANSACTION B3916, ACTIVE 707 sec
2 lock struct(s), heap size 320, 1 row lock(s), undo log entries 1
MySQL thread id 8, OS thread handle 0xd124, query id 114 localhost 127.0.0.1 root
Trx read view will not see trx with id >= B3917, sees < B3915
1
2
3
4

• 在会话A执行操作产生锁等待

mysql> update t set c=c+1 where id=10;
等待
1
2

此时等待被会话B占有的id=10行X锁。

• 在会话C查看事务状态

...
---TRANSACTION B3915, ACTIVE 2097 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 320, 2 row lock(s), undo log entries 1
MySQL thread id 7, OS thread handle 0xcf54, query id 116 localhost 127.0.0.1 root Updating
update t set c=c+1 where id=10
Trx read view will not see trx with id >= B3916, sees < B3916
------- TRX HAS BEEN WAITING 19 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 15154 n bits 80 index `PRIMARY` of table `test`.`t` trx id B3915 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 0000000b3916; asc     9 ;;
 2: len 7; hex 12000001421f01; asc     B  ;;
 3: len 4; hex 8000000b; asc     ;;
 4: len 4; hex 8000000a; asc     ;;
...
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

信息如下：

localhost 127.0.0.1 root Updating：
    表示被阻塞等待行锁
update t set c=c+1 where id=10：
    被阻塞的语句
------- TRX HAS BEEN WAITING 19 SEC FOR THIS LOCK TO BE GRANTED:
    已经等待锁等待了19秒
RECORD LOCKS space id 0 page no 15154 n bits 80 index `PRIMARY` of table `test`.`t` trx id B3915 lock_mode X locks rec but not gap waiting：
    在表test`.`t`的索引`PRIMARY`上等待锁，等待的是X锁，但不是间隙锁
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 0000000b3916; asc     9 ;;
 2: len 7; hex 12000001421f01; asc     B  ;;
 3: len 4; hex 8000000b; asc     ;;
 4: len 4; hex 8000000a; asc     ;;
    这些我看不太懂，应该是表述的锁的范围吧！
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

• 在会话B执行操作产生死锁

mysql> update t set c=c+1 where id=5;
Query OK, 1 row affected (4.31 sec)
Rows matched: 1  Changed: 1  Warnings: 0
1
2
3

虽然成功了，但这是因为MySQL主动重启了事务A：

mysql> update t set c=c+1 where id=10;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
1
2

• 在会话C查看事务状态

------------------------
LATEST DETECTED DEADLOCK
------------------------
220819 11:18:10
*** (1) TRANSACTION:
TRANSACTION B3916, ACTIVE 1643 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 320, 2 row lock(s), undo log entries 1
MySQL thread id 8, OS thread handle 0xd124, query id 118 localhost 127.0.0.1 root Updating
update t set c=c+1 where id=5
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 15154 n bits 80 index `PRIMARY` of table `test`.`t` trx id B3916 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 0000000b3915; asc     9 ;;
 2: len 7; hex 110000058b122c; asc       ,;;
 3: len 4; hex 80000006; asc     ;;
 4: len 4; hex 80000005; asc     ;;

*** (2) TRANSACTION:
TRANSACTION B3915, ACTIVE 2840 sec starting index read, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
3 lock struct(s), heap size 320, 2 row lock(s), undo log entries 1
MySQL thread id 7, OS thread handle 0xcf54, query id 119 localhost 127.0.0.1 root Updating
update t set c=c+1 where id=10
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 15154 n bits 80 index `PRIMARY` of table `test`.`t` trx id B3915 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 0000000b3915; asc     9 ;;
 2: len 7; hex 110000058b122c; asc       ,;;
 3: len 4; hex 80000006; asc     ;;
 4: len 4; hex 80000005; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 15154 n bits 80 index `PRIMARY` of table `test`.`t` trx id B3915 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 0000000b3916; asc     9 ;;
 2: len 7; hex 12000001421f01; asc     B  ;;
 3: len 4; hex 8000000b; asc     ;;
 4: len 4; hex 8000000a; asc     ;;

*** WE ROLL BACK TRANSACTION (2)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

主要信息如下：

MySQL thread id 8, OS thread handle 0xd124, query id 118 localhost 127.0.0.1 root Updating：
    查询ID 118等待行锁
update t set c=c+1 where id=5：
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    等待行锁授权
RECORD LOCKS space id 0 page no 15154 n bits 80 index `PRIMARY` of table `test`.`t` trx id B3916 lock_mode X locks rec but not gap waiting：
    在表`test`.`t`的主键索引`PRIMARY`上，事务ID B3916等待X模式的行锁，但不是间隙锁
MySQL thread id 7, OS thread handle 0xcf54, query id 119 localhost 127.0.0.1 root Updating：
    查询ID 118等待行锁
*** (2) HOLDS THE LOCK(S)：
    持有的锁信息
RECORD LOCKS space id 0 page no 15154 n bits 80 index `PRIMARY` of table `test`.`t` trx id B3915 lock_mode X locks rec but not gap
    持有表`test`.`t`的索引`PRIMARY`的X模式的行锁，但是不是间隙锁
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
    等待锁授权
RECORD LOCKS space id 0 page no 15154 n bits 80 index `PRIMARY` of table `test`.`t` trx id B3915 lock_mode X locks rec but not gap waiting：
    等待表`test`.`t`的索引`PRIMARY`的X模式的行锁，但是不是间隙锁
*** WE ROLL BACK TRANSACTION (2)：
    死锁的结果是回滚了(2)，即事务ID B3915的事务
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

show engine innodb status的锁描述主要有以下几种：

lock_mode X waiting表示next-key lock；

lock_mode X locks rec but not gap是只有行锁；

还有一种 “locks gap before rec”，就是只有间隙锁；

3：如何避免

对一组相同的资源加锁，按照相同的顺序来加锁。

写在后面