一次数据库死锁的问题排查

最新推荐文章于 2024-04-20 00:53:26 发布
最新推荐文章于 2024-04-20 00:53:26 发布
阅读量1.3k 收藏 1
点赞数
分类专栏： 故障排除 Pachira 文章标签： 故障排除
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_38522652/article/details/93068105
版权

一次死锁问题的排查

由于外键导致的死锁问题排查

使用jmeter 进行前端稳定性循环压测的时候分别进行模型的上线、下线、修改、共享、取消共享操作时出现了大量的死锁的异常。

2019-06-18 15:00:47,086 [qtp2128665553-168] o.h.e.j.s.SqlExceptionHelper WARN SQL Error: 1213, SQLState: 40001
2019-06-18 15:00:47,087 [qtp2128665553-168] o.h.e.j.s.SqlExceptionHelper ERROR Deadlock found when trying to get lock; try restarting transaction

问题排查

由于我们的数据库默认是innodb，所以先查询下发生死锁的sql show engine innodb status

显示如下：

LATEST DETECTED DEADLOCK

2019-06-18 21:52:08 7ff17cdf9700
*** (1) TRANSACTION:
TRANSACTION 3620735312, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 2
MySQL thread id 12, OS thread handle 0x7ff17cdb8700, query id 1908119 192.168.129.214 root updating
update he_model set ai_model=’[“你好”,“再见”,“修改”]’, color=’#000000’, conetext_num=5, create_time=‘2019-06-18 21:52:08’, creator=813, deleted=0, dim_formula=null, dim_tree=null, warning_id=null, fly_screen=0, word_interval=10, last_modifier=813, last_modify_date=‘2019-06-18 21:52:08.19’, model_group_id=72, model_type=‘ai’, name=‘AI模型’, process_percent=‘1’, rte_content=‘

AI模型修改

’, status=1, strategy_formular=null, strategy_tree=null, text_formula=null, text_tree=null, warning=0 where id=809
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2208 page no 293 n bits 112 index PRIMARY of table haw21063. he_model trx id 3620735312 lock_mode X locks rec but not gap waiting
Record lock, heap no 38 PHYSICAL RECORD: n_fields 27; compact format; info bits 0
0: len 4; hex 80000329; asc );;
1: len 6; hex 0000d7d00927; asc ';;
2: len 7; hex 0b00001ead0998; asc ;;
3: len 28; hex 5b22e4bda0e5a5bd222c22e5868de8a781222c22e8b0a2e8b0a2225d; asc [" “,” “,” "];;
4: len 7; hex 23303030303030; asc #000000;;
5: len 4; hex 80000005; asc ;;
6: len 5; hex 99a3655d08; asc e] ;;
7: len 1; hex 00; asc ;;
8: SQL NULL;
9: SQL NULL;
10: len 1; hex 00; asc ;;
11: len 4; hex 8000000a; asc ;;
12: len 5; hex 99a3655d08; asc e] ;;
13: len 2; hex 6169; asc ai;;
14: len 8; hex 4149e6a8a1e59e8b; asc AI ;;
15: len 1; hex 31; asc 1;;
16: len 15; hex 3c703e4149e6a8a1e59e8b3c2f703e; asc

AI

;;
17: len 4; hex 80000000; asc ;;
18: SQL NULL;
19: SQL NULL;
20: SQL NULL;
21: SQL NULL;
22: len 1; hex 00; asc ;;
23: len 4; hex 8000032d; asc -;;
24: SQL NULL;
25: len 4; hex 8000032d; asc -;;
26: len 4; hex 80000048; asc H;;

*** (2) TRANSACTION:
TRANSACTION 3620735311, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
6 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 2
MySQL thread id 11, OS thread handle 0x7ff17cdf9700, query id 1908121 192.168.129.214 root updating
update he_model set ai_model=’[“你好”,“再见”,“修改”]’, color=’#000000’, conetext_num=5, create_time=‘2019-06-18 21:52:08’, creator=813, deleted=0, dim_formula=null, dim_tree=null, warning_id=null, fly_screen=0, word_interval=10, last_modifier=812, last_modify_date=‘2019-06-18 21:52:08.19’, model_group_id=72, model_type=‘ai’, name=‘AI模型’, process_percent=‘1’, rte_content=‘

AI模型修改

’, status=1, strategy_formular=null, strategy_tree=null, text_formula=null, text_tree=null, warning=0 where id=809
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 2208 page no 293 n bits 112 index PRIMARY of table haw21063. he_model trx id 3620735311 lock mode S locks rec but not gap
Record lock, heap no 38 PHYSICAL RECORD: n_fields 27; compact format; info bits 0
0: len 4; hex 80000329; asc );;
1: len 6; hex 0000d7d00927; asc ';;
2: len 7; hex 0b00001ead0998; asc ;;
3: len 28; hex 5b22e4bda0e5a5bd222c22e5868de8a781222c22e8b0a2e8b0a2225d; asc [" “,” “,” "];;
4: len 7; hex 23303030303030; asc #000000;;
5: len 4; hex 80000005; asc ;;
6: len 5; hex 99a3655d08; asc e] ;;
7: len 1; hex 00; asc ;;
8: SQL NULL;
9: SQL NULL;
10: len 1; hex 00; asc ;;
11: len 4; hex 8000000a; asc ;;
12: len 5; hex 99a3655d08; asc e] ;;
13: len 2; hex 6169; asc ai;;
14: len 8; hex 4149e6a8a1e59e8b; asc AI ;;
15: len 1; hex 31; asc 1;;
16: len 15; hex 3c703e4149e6a8a1e59e8b3c2f703e; as
17: len 4; hex 80000000; asc ;;
18: SQL NULL;
19: SQL NULL;
20: SQL NULL;
21: SQL NULL;
22: len 1; hex 00; asc ;;
23: len 4; hex 8000032d; asc -;;
24: SQL NULL;
25: len 4; hex 8000032d; asc -;;
26: len 4; hex 80000048; asc H;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2208 page no 293 n bits 112 index PRIMARY of table haw21063.he_model trx id 3620735311 lock_mode X locks rec but not gap waiting
Record lock, heap no 38 PHYSICAL RECORD: n_fields 27; compact format; info bits 0
0: len 4; hex 80000329; asc );;
1: len 6; hex 0000d7d00927; asc ';;
2: len 7; hex 0b00001ead0998; asc ;;
3: len 28; hex 5b22e4bda0e5a5bd222c22e5868de8a781222c22e8b0a2e8b0a2225d; asc [" “,” “,” "];;
4: len 7; hex 23303030303030; asc #000000;;
5: len 4; hex 80000005; asc ;;
6: len 5; hex 99a3655d08; asc e] ;;
7: len 1; hex 00; asc ;;
8: SQL NULL;
9: SQL NULL;
10: len 1; hex 00; asc ;;
11: len 4; hex 8000000a; asc ;;
12: len 5; hex 99a3655d08; asc e] ;;
13: len 2; hex 6169; asc ai;;
14: len 8; hex 4149e6a8a1e59e8b; asc AI ;;
15: len 1; hex 31; asc 1;;
16: len 15; hex 3c703e4149e6a8a1e59e8b3c2f703e; asc

AI

;;
17: len 4; hex 80000000; asc ;;
18: SQL NULL;
19: SQL NULL;
20: SQL NULL;
21: SQL NULL;
22: len 1; hex 00; asc ;;
23: len 4; hex 8000032d; asc -;;
24: SQL NULL;
25: len 4; hex 8000032d; asc -;;
26: len 4; hex 80000048; asc H;;

在修改模型id为809的模型发生了死锁，这个不可能的呀，如果同时修改一个记录，会设置X锁进行排他，不能发生死锁的问题呀。
之后百度了一下，遇到这种现象

初步分析

查看Innodb的操作二进制文件，首先查看二进制文件是否开启

mysql> show variables like ‘log_bin’;
±--------------±------+
| Variable_name | Value |
±--------------±------+
| log_bin | OFF |
±--------------±------+
二进制文件默认不开启，需要去数据库my.cnf增加二进制文件开关，find / -name “my.cnf” 找到对应的cnf文件

默认配置二进制文件
2. 二进制文件开启后，重启mysql，使用jmeter进行稳定性测试，发生死锁日志。

根据thread id 、修改记录、时间点定位到mysql-bin.000001文件行
3. 注意一下，默认二进制文件直接使用文本编辑器打不开，所以需要将二进制文件转换为txt文件来查看

mysqlbinlog -u root -p mysql-bin.000001 > 1.txt
转换的txt文件显示需要结合数据库查询show binlog events in ‘mysql-bin.000001’;来确定pos的id对应的sql，根据修改到的内容和时间点定位到在修改模型的时候会对模型修改历史进行修改

顺序事务1事务2说明
1begin
2begin
3insert into model_update_history (ai_model, create_time, dim_formula, dim_tree, word_interval, model, model_name, model_type, modifier, modify_time, strategy_formular, strategy_tree, text_formula, text_tree) values ('[""你好"",""再见"",""谢谢""]', '2019-06-18 21:52:08.191', null, null, 10, 809, 'AI模型', 'ai', 'modeler', '2019-06-18 21:52:08', null, null, null, null)"
4insert into model_update_history (ai_model, create_time, dim_formula, dim_tree, word_interval, model, model_name, model_type, modifier, modify_time, strategy_formular, strategy_tree, text_formula, text_tree) values ('[""你好"",""再见"",""谢谢""]', '2019-06-18 21:52:08.191', null, null, 10, 809, 'AI模型', 'ai', 'modeler', '2019-06-18 21:52:08', null, null, null, null)"事务2 在he_model_history获取到history的锁，然后修改了历史记录，然后向A表申请S锁来查询数据
6update he_model set ai_model='["你好","再见","修改"]', color='#000000', conetext_num=5, create_time='2019-06-18 21:52:08', creator=813, deleted=0, dim_formula=null, dim_tree=null, warning_id=null, fly_screen=0, word_interval=10, last_modifier=813, last_modify_date='2019-06-18 21:52:08.19', model_group_id=72, model_type='ai', name='AI模型', process_percent='1', rte_content='

AI模型修改

', status=1, strategy_formular=null, strategy_tree=null, text_formula=null, text_tree=null, warning=0 where id=809		事务1 在he_model表上申请到X锁以修改he_model表记录，并且要检查model_update_history是否满足外键约束，需要S锁
update he_model set ai_model='["你好","再见","修改"]', color='#000000', conetext_num=5, create_time='2019-06-18 21:52:08', creator=813, deleted=0, dim_formula=null, dim_tree=null, warning_id=null, fly_screen=0, word_interval=10, last_modifier=813, last_modify_date='2019-06-18 21:52:08.19', model_group_id=72, model_type='ai', name='AI模型', process_percent='1', rte_content='

AI模型修改

', status=1, strategy_formular=null, strategy_tree=null, text_formula=null, text_tree=null, warning=0 where		事物2打算给sku表id为809记录上 X 排它锁，发现被其他事务上了，而且此事务居然还在等他提交，这时MYSQL立刻回滚事务2…(发现MYSQL返回死锁信息，记录该信息到错误日志…发送回滚指令…mysql已经“帮”他回滚了
7事务1发现别人的锁释放了，获得X锁，执行成功
执行成功
8事务1执行成功，记录binlog日志
commit

解决办法：
对表B上的外键列建立索引，从而使事务A发生时将range锁放到索引上，从而降低死锁发生的概率
​

糖醋鱼小排528
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql死锁日志分析_MySQL 死锁日志分析
weixin_36074668的博客
01-19 482
------------------------LATESTDETECTEDDEADLOCK------------------------1408241:01:24***(1)TRANSACTION:TRANSACTION110E,ACTIVE73secstartingindexread##事务ID=110E，活跃了73smysqltablesinuse...
LATEST DETECTED DEADLOCK
weixin_34014555的博客
06-19 2882
大家好！针对6月2日 LOANDB CPU高负载问题分析 6月2日上午10点5分，发现Loandb的数据库的CPU负载非常高，CPU的IDEL值几乎接近为零。期初认为是业务方进行后台管理操作导致，在与开发联系后关闭管理后台的应用后数据库的CPU负载依然非常高。 后来在查看MYSQL innodb的数据库引擎状态信息日志中发现了问题的根本原因。 1...
线上频出MySQL死锁问题！分享一下自己教科书般的排查和分析过程
最新发布
2401_84424379的博客
04-20 574
事务2的 UPDATE test_table SET money = money + 4 WHERE user_id = 4 语句在等待锁：它通过普通索引 idx_user_id 更新，先获取了 user_id=4 的 X 锁，接着去申请对应行的主键（Record Lock）的行锁但是被阻塞（waiting），并不包括间隙锁（not gap）。模糊结论肯定是有问题的，最大的问题在于导致的 SQL 语句不正确，即：死锁的原因是真实的，但是具体是因为哪些 SQL 导致的死锁是不清楚的。
数据库LATEST DETECTED DEADLOCK
Cloveme的博客
12-27 1947
分析：A读一条纪录，然后修改该条纪录 B请求修改数据 A的事务里锁的性质由共享锁企图上升到独占锁(for update),而用户B里的独占锁由于A有共享锁存在所以必须等A释 放掉共享锁，而A由于B的独占锁而无法上升的独占锁也就不可能释放共享锁，于是出现了死锁。 ...
ORA-00060: Deadlock detected
EVISWANG的博客
11-19 1321
Thu Nov 19 01:13:49 2015 ORA-00060: Deadlock detected. More info infile /u01/oracle/diag/rdbms/appserv/appserv/trace/appserv_ora_20577.trc. 去查看相应的trace文件appserv_ora_20577.trc的死锁信息 PS:相应的trc文件
14.5.5.1 An InnoDB Deadlock Example 一个InnoDB 死锁实例
weixin_30521161的博客
10-31 58
14.5.5.1 An InnoDB Deadlock Example 一个InnoDB 死锁实例 下面的例子演示了一个错误可以发生当一个lock 请求会导致一个死锁,例子设计2个客户端，A和B: Jekins:/root# mysql -uroot -p1234567 -e"SHOW ENGINE INNODB STATUS\G;" | grep -i dead Warning:...
一次mysql死锁的排查过程
12-14
　以前接触到的数据库死锁，都是批量更新时加锁顺序不一致而导致的死锁，但是上周却遇到了一个很难理解的死锁。借着这个机会又重新学习了一下mysql的死锁知识以及常见的死锁场景。在多方调研以及和同事们的讨论下...
一次Mysql死锁排查过程的全纪录
12-16
之前接触到的数据库死锁，都是批量更新时加锁顺序不一致而导致的死锁，但是上周却遇到了一个很难理解的死锁。借着这个机会又重新学习了一下mysql的死锁知识以及常见的死锁场景。在多方调研以及和同事们的讨论下终于...
记一次公司仓库数据库服务器死锁过程及解决办法
12-15
死锁的四个必要条件： 互斥条件(Mutual exclusion)：资源不能被...仓库拣货卡死，排查了数据库的很多地方，都没有头绪，最后到SQL Server 错误日志里查看，终于发现了蛛丝马迹 EXEC xp_readerrorlog 0,1,NULL,NULL,'20
MySQL redo死锁问题排查及解决过程分析
09-09
被告知在多实例场景下 MySQL Server hang 住，无法测试下去，原生版本不存在这个问题，而新版本上出现了这个问题，不禁心头一颤，心中不禁感到奇怪，还好现场环境还在，为排查问题提供了一个好的环境，随即便投入到...
获取数据库死锁信息过程
07-18
一个经典的获取SQLSERVEr数据库死锁及引起锁定的进程的存储过程。执行时，必须具有数据库超级管理员权限。
Mysql死锁问题Deadlock found when trying to get lock；try restarting transaction
dongdong199033的博客
01-08 2131
表的排他锁（lock_mode X），相当于两个事务都在等对方持有的排他锁，于是就死锁了。先去服务器上，看看死锁的日志，找到 mysql 的安装路径，使用如下命令登录 mysql。mysql -h 数据库IP地址 -P 数据库端口 -u 用户名 -p 库名。表的排他锁（lock_mode X），而事务2在根据主键ID更新。可以看到事务1在根据主键ID更新。去看了下代码，操作步骤如下。增加分布式锁，让并发不存在。1、根据主键ID更新。2、根据主键ID更新。3、根据主键ID更新。
【mysql】show engine innodb status 解读
MYSQL轻松学的专栏
06-26 2537
​InnoDB是MySQL使用最多的存储引擎，通常InnoDB状态可以通过show engine innodb status\G查看。 由于MySQL不同版本采用InnoDB引擎版本不同，5.6后对show engine innodb status信息进行了优化，本文环境为5.7。 MySQL版本 InnoDB引擎版本 5.1.x 1.0.x版本（官方称为InnoDB P...
怎么看死锁？
huanghuang9426的博客
12-07 1569
show engine innodb status 命令 LATESTDETECTED DEADLOCK，就是记录的最后一次死锁信息。 这个结果分成三部分： TRANSACTION，第一个事务的信息 TRANSACTION，是第二个事务的信息 WE ROLL BACK TRA NSACTION (1)，是最终的处理结果，表示回滚了第一个事务。 第一个事务的信息中： WAITING FOR THIS LOCK TO BE GRANTED，表示的是这个事务在等待的锁信息； index c of ta
Mysql报Deadlock found when trying to get lock； try restarting transaction问题解决
热门推荐
a1k2l45k的博客
12-07 4万+
mysql deadLock
mysql死锁 DeadLock定位与解决
进化的深山猿
01-24 2万+
mysql死锁问题定位与解决 java后台报错如下 java.util.concurrent.ExecutionException: org.springframework.dao.DeadlockLoserDataAccessException: ### Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQ...
总结线上遇到的MySQL死锁问题
clamaa的专栏
11-04 257
  线上遇到了MySQL死锁的相关问题，需要查看MySQL出现的Deadlock日志，可以通过执行:   show engine innodb status     来查看innodb类型数据库的状态，查找laster detected deadlock部分，可以看到最近造成死锁的两条sql   ------------------------ LATES...
mysql 制造一个死锁_MySql一个生产死锁案例分析
weixin_35748716的博客
01-27 802
接到上级一个生产环境MySQL死锁日志信息文件，需要找出原因并解决问题。我将死锁日志部分贴出如下：在mysql中使用命令：SHOW ENGINE INNODB STATUS;总能获取到最近一些问题信息，通过搜索deadlock 关键字即可找到死锁的相关日志信息。2019-09-25 13:28:25 7fc0301ca700InnoDB: transactions deadlock detecte...
死锁问题怎么排查 db2
05-24
在 DB2 中，死锁通常是指两个或多个事务相互等待对方持有的资源，导致它们都无法继续执行下去。为了排查 DB2 中的死锁问题，可以采取以下步骤： 1. 确认是否存在死锁：可以通过查询数据库的系统日志或使用 DB2 的工具来检测是否存在死锁情况。 2. 分析死锁的原因：可以通过查看系统日志或使用 DB2 的工具来了解死锁发生的原因，例如哪些事务参与了死锁、哪些资源被互相占用等。 3. 确认死锁的对象：可以使用 DB2 的工具来查看哪些表或索引被锁定，以及锁定的类型和持有者。 4. 手动解锁：如果死锁的情况比较简单，可以手动解锁来恢复正常运行。例如，可以杀死一个参与死锁的事务，或者手动释放被占用的资源。 5. 优化查询语句：如果死锁的情况比较复杂，可能需要优化查询语句或更改数据库结构来避免死锁的发生。 总之，在排查 DB2 中的死锁问题时，需要仔细分析死锁的原因和对象，并采取合适的措施来解决问题。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值