mysql死锁实例_MySql一个生产死锁案例分析

最新推荐文章于 2024-05-13 02:58:20 发布

圣明君

最新推荐文章于 2024-05-13 02:58:20 发布

阅读量693

点赞数

文章标签： mysql死锁实例

本文链接：https://blog.csdn.net/weixin_34283655/article/details/113226062

版权

本文分析了一个MySQL生产环境中的死锁实例，涉及两个事务更新同一表`tb_elc_forecast`中不同记录时产生的死锁。死锁发生在两个事务尝试获取对方已锁定记录的排他锁。通过日志，推测可能由于事务在更新前进行了查询操作，导致共享锁冲突，进而引发死锁。解决方法包括检查事务隔离级别、避免长时间持有锁、优化事务执行顺序等。

摘要由CSDN通过智能技术生成

接到上级一个生产环境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