有些研发同学对MySQL的死锁不是很清楚,常常把一些事务超时等现象归咎于可能存在死锁,带着这种误解排查问题,往往会徒劳无功,离真相越来越远。

记得有一位WP同事问:“经常遇到mysql死锁,这个怎么解决啊?”
我说:”你怎么知道是死锁?“
WP: “我就执行了一个update, 他就卡住在那里好半天,这不是死锁了吗?“
我忍不住笑了起来:”这可不是死锁,这是死等!“

死等和死锁可不是一回事,如果你遇到了死等,大可放心,肯定不是死锁;如果发生了死锁,也大可放心,绝对不会死等。

这是因为MySQL内部有一套死锁检测机制,一旦发生死锁会立即回滚一个事务,让另一个事务执行下去。并且这个死锁回滚的的错误消息也会发送给客户端,如果应用端详细处理了各种异常,是会发现死锁的,不用经常追着DBA问:“数据库最近有没有死锁啊?”

1
ERROR 1213 (40001): Deadlock found when trying to get lock;try restarting transaction.

简单来讲,死锁是高并发环境下,对热点数据更新时,不同的事务加锁顺序不一致造成的。 产生死锁并不可怕,应用端发现死锁后重新发起事务就行了,并不会对数据库造成什么危害。即使是在正常的业务中,死锁也会时不时的发生。当然,若死锁太频繁,导致事务无法进行,那问题就很严重了。

MySQL中的死锁检测

由于死锁是两个事务进入了一个相互等待的怪圈,不借助外力是无法打破的。死锁检测机制就是打破这一怪圈的外力。在mysql中有许多类似队列的数据结构来存放各种各样的数据。就事务而言,每个事务的发起,其持有哪些锁,等待哪些锁,以及哪些锁被哪些事务所等待等,这些信息都会保存在相应的队列中,死锁检测机制就是遍历这些队列来检查是否有死锁的存在。一旦发现相互等待的怪圈,则立即回滚一个代价较小的事务,让另一个事务执行下去。被回滚的事务称为受害者(victim)。执行show engine innodb status\G 可以看的最近的死锁情况。

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
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2019-06-12 18:44:43 0x7f5746556700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 34 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 4266051 srv_active, 0 srv_shutdown, 402120 srv_idle
srv_master_thread log flush and writes: 4668171
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 213069725
OS WAIT ARRAY INFO: signal count 639352073
RW-shared spins 0, rounds 195598399, OS waits 29349856
RW-excl spins 0, rounds 4403276621, OS waits 148061454
RW-sx spins 2574783, rounds 59697317, OS waits 1189541
Spin rounds per wait: 195598399.00 RW-shared, 4403276621.00 RW-excl, 23.19 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-06-12 18:38:59 0x7f573aebb700
*** (1) TRANSACTION:
TRANSACTION 17048041219, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 4
MySQL thread id 3292024, OS thread handle 140012635129600, query id 2727382216 11.0.0.180 test updating
update account set available_amount = available_amount+ 65.90 where id ='1'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1338 page no 28106 n bits 104 index PRIMARY of table `test`.`account` trx id 17048041219 lock_mode X locks rec but not gap waiting
Record lock, heap no 35 PHYSICAL RECORD: n_fields 19; compact format; info bits 0
0: len 7; hex 767676762d3031; asc vvvv-01;;
1: len 6; hex 0003f82476ec; asc $v ;;
2: len 7; hex 750002800d2797; asc u ' ;;
3: len 1; hex 80; asc ;;
4: len 8; hex 800000005b4d173f; asc [M ?;;
5: len 4; hex 76767676; asc vvvv;;
6: len 16; hex 52433135303530384b30595847514b4c; asc RC150508K0YXGQKL;;
7: len 8; hex 8000000000000000; asc ;;
8: len 8; hex 8000000000000000; asc ;;
9: len 3; hex 524254; asc RBT;;
10: len 8; hex 8000000000000000; asc ;;
11: len 8; hex 8000000000000000; asc ;;
12: SQL NULL;
13: len 8; hex 8000000000000000; asc ;;
14: len 8; hex 8000000000000000; asc ;;
15: len 4; hex 5961ef89; asc Ya ;;
16: len 4; hex 5d00d642; asc ] B;;
17: len 8; hex 8000016b405cb0d1; asc k@\ ;;
18: len 1; hex 80; asc ;;

*** (2) TRANSACTION:
TRANSACTION 17048041196, ACTIVE 1 sec starting index read
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 5
MySQL thread id 3316154, OS thread handle 140012627408640, query id 2727382231 11.0.0.159 test updating
update account set available_amount = available_amount+ 859.04 where id ='202cc040-a0eb-4555-9a80-8571a809add0-01'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1338 page no 28106 n bits 104 index PRIMARY of table `test`.`account` trx id 17048041196 lock_mode X locks rec but not gap
Record lock, heap no 35 PHYSICAL RECORD: n_fields 19; compact format; info bits 0
0: len 7; hex 767676762d3031; asc vvvv-01;;
1: len 6; hex 0003f82476ec; asc $v ;;
2: len 7; hex 750002800d2797; asc u ' ;;
3: len 1; hex 80; asc ;;
4: len 8; hex 800000005b4d173f; asc [M ?;;
5: len 4; hex 76767676; asc vvvv;;
6: len 16; hex 52433135303530384b30595847514b4c; asc RC150508K0YXGQKL;;
7: len 8; hex 8000000000000000; asc ;;
8: len 8; hex 8000000000000000; asc ;;
9: len 3; hex 524254; asc RBT;;
10: len 8; hex 8000000000000000; asc ;;
11: len 8; hex 8000000000000000; asc ;;
12: SQL NULL;
13: len 8; hex 8000000000000000; asc ;;
14: len 8; hex 8000000000000000; asc ;;
15: len 4; hex 5961ef89; asc Ya ;;
16: len 4; hex 5d00d642; asc ] B;;
17: len 8; hex 8000016b405cb0d1; asc k@\ ;;
18: len 1; hex 80; asc ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1338 page no 31455 n bits 128 index PRIMARY of table `test`.`account` trx id 17048041196 lock_mode X locks rec but not gap waiting
Record lock, heap no 60 PHYSICAL RECORD: n_fields 19; compact format; info bits 0
0: len 30; hex 32303263633034302d613065622d343535352d396138302d383537316138; asc 202cc040-a0eb-4555-9a80-8571a8; (total 39 bytes);
1: len 6; hex 0003f8247703; asc $w ;;
2: len 7; hex 230002802c2cb7; asc # ,, ;;
3: len 1; hex 80; asc ;;
4: len 8; hex 8000000108254323; asc %C#;;
5: len 30; hex 32303263633034302d613065622d343535352d396138302d383537316138; asc 202cc040-a0eb-4555-9a80-8571a8; (total 36 bytes);
6: len 16; hex 35393465306430343030313362623062; asc 594e0d040013bb0b;;
7: len 8; hex 8000000000000000; asc ;;
8: len 8; hex 8000000000000000; asc ;;
9: len 3; hex 524254; asc RBT;;
10: len 8; hex 8000000000000000; asc ;;
11: len 8; hex 8000000000000000; asc ;;
12: len 0; hex ; asc ;;
13: len 8; hex 8000000000000000; asc ;;
14: len 8; hex 8000000000000000; asc ;;
15: len 4; hex 5c74a896; asc \t ;;
16: len 4; hex 5d00d643; asc ] C;;
17: len 8; hex 8000016b4ad90690; asc kJ ;;
18: len 1; hex 80; asc ;;

*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------

LATEST DETECTED DEADLOCK部分会打印出最近一次的死锁情况。遗憾的是这些信息并不是很充分,但已经能够发现那个事务和那个事务发生来死锁,那个事务被回滚(受害者),哪个事务被执行。

由死锁检测机制的原理可以看的,死锁检测要遍历查询很多的队列,在高并发环境中死锁检测将会是一个CPU密集型操作,特别是当大量用户线程在等待同一个锁的时候,系统性能会明显降低。那么这项功能能不能被禁止呢?

当然是可以的啦。InnoDB中有一个变量innodb_deadlock_detect={ON|OFF}就是来控制这一行为的。这个变量大家去查查官方文档,很容易理解。 但随之而来的一个问题是,死锁检测的特性被禁止来,若是再发生来死锁,谁来打破这个怪圈?会不会就是死等了啊?

很不幸,这个是会死等的,但也不用太担心,只是死等而已,又不是等死,不要怕!因为InnoDB中还有一个变量,对事务的等待时间做了限制:innodb_lock_wait_timeout, 这个变量默认是50秒,也就是说,会死等五十秒而已。当然你还可以把它改成一个比较小的值,这样死等的时间也会降低啦!

如何降低死锁的发生

相信看了上面的分析,大家应该不会再那么担心死锁了。但也并不是说死锁就可以不管了,降低死锁的发生也是很有必要的。

从根本上讲,解铃还需系铃人,死锁是由于业务逻辑的加锁顺序不一致造成的,解决死锁还是要从业务逻辑入手。但这往往不容易实现,因为这可能涉及到业务的不同模块,不同的研发人员,却要操作同一张表的数据。

从DB的角度讲,可以提供一些诊断相关的数据,如show engine innoDB status\G命令打出的死锁信息,研发同学可以根据这一信息来确定所涉及的业务模块,以及相关的事务代码。但命令show engine innoDB status\G只能打出最近一次死锁的信息,若研发同学想查查发生了多少次死锁,它就无能为力了。如果确实有这必要,DB还可以暂时启用innodb_print_all_deadlocks来查看更多的死锁信息, 开启这个参数会把所有的死锁信息打印到mysql的错误日志里面。这只是一个debug手段,不建议长期开启。

另外,DB还可以使用较低的隔离级别,比如read_commited,降低加锁粒度,降低死锁的概率。

MySQL官方文档还提供了一些措施,大家也可以去查查,看是不是用得上!