接到上级一个生产环境MySQL死锁日志信息文件,需要找出原因并解决问题。我将死锁日志部分贴出如下:
在mysql中使用命令:SHOW ENGINE INNODB STATUS;总能获取到最近一些问题信息,通过搜索deadlock 关键字即可找到死锁的相关日志信息。
2019-09-25 13:28:25 7fc0301ca700InnoDB: transactions deadlock detected, dumping detailed information.
2019-09-25 13:28:25 7fc0301ca700
*** (1) TRANSACTION:
TRANSACTION 48000896642, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 5
MySQL thread id 1585229, OS thread handle 0x7fc030e7c700, query id 36142536228 100.64.177.170 oms8 updating
update tb_elc_forecast
set capital = capital - 1.0, outbound = outbound + 1.0
where id = 1131766 and capital >= 1.0
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1697 page no 31889 n bits 120 index `PRIMARY` of table `oms8`.`tb_elc_forecast` trx id 48000896642 lock_mode X locks rec but not gap waiting
Record lock, heap no 20 PHYSICAL RECORD: n_fields 46; compact format; info bits 0
0: len 4; hex 801144f6; asc D ;;
1: len 6; hex 000b2d138e83; asc - ;;
2: len 7; hex 1b0013b0a9145c; asc \;;
3: len 30; hex 3637666533626536382d616562622d313165392d393838332d6436636337; asc 67fe3be68-aebb-11e9-9883-d6cc7; (total 37 bytes);
4: len 10; hex 4d324c50303530303030; asc M2LP050000;;
5: len 11; hex 5730323130313439393137; asc W0210149917;;
6: len 4; hex 20bcbe4c; asc L;;
7: len 4; hex 000080bf; asc ;;
8: len 4; hex 006e2f47; asc n/G;;
9: len 4; hex 005c5646; asc \VF;;
10: len 4; hex 00000000; asc ;;
11: len 7; hex 323031392d3039; asc 2019-09;;
12: len 9; hex 4252414e4453495445; asc BRANDSITE;;
13: len 14; hex 776d735f656c635f62616f7a756e; asc wms_elc_baozun;;
14: len 4; hex 80000002; asc ;;
15: len 4; hex 80000001; asc ;;
16: len 4; hex 80000000; asc ;;
17: len 5; hex 99a3b313ab; asc ;;
18: SQL NULL;
19: len 3; hex 737973; asc sys;;
20: SQL NULL;
21: len 4; hex 80019737; asc 7;;
22: SQL NULL;
23: SQL NULL;
24: SQL NULL;
25: SQL NULL;
26: SQL NULL;
27: len 2; hex 3130; asc 10;;
28: len 12; hex 4252414e44534954455f3130; asc BRANDSITE_10;;
29: SQL NULL;
30: len 30; hex 494c4c554d494e4154494e4720464143452042415345205320374d4c2f2e; asc ILLUMINATING FACE BASE S 7ML/.; (total 88 bytes);
31: len 9; hex 4252414e4453495445; asc BRANDSITE;;
32: len 1; hex 59; asc Y;;
33: len 1; hex 59; asc Y;;
34: len 4; hex 80000000; asc ;;
35: len 0; hex ; asc ;;
36: len 4; hex 00000000; asc ;;
37: len 4; hex 00000000; asc ;;
38: len 4; hex 80000000; asc ;;
39: len 4; hex 80000000; asc ;;
40: SQL NULL;
41: SQL NULL;
42: len 4; hex 00000000; asc ;;
43: len 4; hex 00000000; asc ;;
44: SQL NULL;
45: SQL NULL;
*** (2) TRANSACTION:
TRANSACTION 48000896643, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 3
MySQL thread id 1584766, OS thread handle 0x7fc0301ca700, query id 36142536229 100.64.177.168 oms8 updating
update tb_elc_forecast
set capital = capital - 1.0, outbound = outbound + 1.0
where id = 1164727 and capital >= 1.0
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1697 page no 31889 n bits 120 index `PRIMARY` of table `oms8`.`tb_elc_forecast` trx id 48000896643 lock_mode X locks rec but not gap
Record lock, heap no 20 PHYSICAL RECORD: n_fields 46; compact format; info bits 0
0: len 4; hex 801144f6; asc D ;;
1: len 6; hex 000b2d138e83; asc - ;;
2: len 7; hex 1b0013b0a9145c; asc \;;
3: len 30; hex 3637666533626536382d616562622d313165392d393838332d6436636337; asc 67fe3be68-aebb-11e9-9883-d6cc7; (total 37 bytes);
4: len 10; hex 4d324c50303530303030; asc M2LP050000;;
5: len 11; hex 5730323130313439393137; asc W0210149917;;
6: len 4; hex 20bcbe4c; asc L;;
7: len 4; hex 000080bf; asc ;;
8: len 4; hex 006e2f47; asc n/G;;
9: len 4; hex 005c5646; asc \VF;;
10: len 4; hex 00000000; asc ;;
11: len 7; hex 323031392d3039; asc 2019-09;;
12: len 9; hex 4252414e4453495445; asc BRANDSITE;;
13: len 14; hex 776d735f656c635f62616f7a756e; asc wms_elc_baozun;;
14: len 4; hex 80000002; asc ;;
15: len 4; hex 80000001; asc ;;
16: len 4; hex 80000000; asc ;;
17: len 5; hex 99a3b313ab; asc ;;
18: SQL NULL;
19: len 3; hex 737973; asc sys;;
20: SQL NULL;
21: len 4; hex 80019737; asc 7;;
22: SQL NULL;
23: SQL NULL;
24: SQL NULL;
25: SQL NULL;
26: SQL NULL;
27: len 2; hex 3130; asc 10;;
28: len 12; hex 4252414e44534954455f3130; asc BRANDSITE_10;;
29: SQL NULL;
30: len 30; hex 494c4c554d494e4154494e4720464143452042415345205320374d4c2f2e; asc ILLUMINATING FACE BASE S 7ML/.; (total 88 bytes);
31: len 9; hex 4252414e4453495445; asc BRANDSITE;;
32: len 1; hex 59; asc Y;;
33: len 1; hex 59; asc Y;;
34: len 4; hex 80000000; asc ;;
35: len 0; hex ; asc ;;
36: len 4; hex 00000000; asc ;;
37: len 4; hex 00000000; asc ;;
38: len 4; hex 80000000; asc ;;
39: len 4; hex 80000000;