【作者】 刘博:携程技术保障中心数据库高级经理,主要关注SQL Server和Mysql的运维和故障处理。 【环境】 版本号:5.6.21 隔离级别:REPEATABLE READ 【问题描述】 接
【作者】
刘博:携程技术保障中心数据库高级经理,主要关注SQL Server和Mysql的运维和故障处理。
【环境】
版本号:5.6.21
隔离级别:REPEATABLE READ
【问题描述】
接到监控报警,有一个线上的应用DeadLock报错,每15分钟会准时出现,报错统计如下图:
mysql> show engine innodb status\G
*** (1) TRANSACTioN:
TRANSACTION 102973, ACTIVE 11 sec starting index read
mysql tables in use 3, locked 3
LOCK WaiT 4 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 6, OS thread handle 140024996574976, query id 83 localhost us updating
UPDATE TestTable
SET column1 = 1,
Column2 = sysdate(),
Column3= '026'
Column4 = 0
AND column5 = 485
AND column6 = 'SEK'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 417 page no 1493 n bits 1000 index idx_column6 of table test.TestTable trx id 102973 lock_mode X waiting
Record lock, heap no 859 PHYSICAL RECORD: n_fields 2; compact fORMat; info bits 0
0: len 3; hex 53454b; asc SEK;;
1: len 8; hex 80000000007e1452; asc ~ R;;
*** (2) TRANSACTION:
TRANSACTION 102972, ACTIVE 26 sec starting index read
mysql tables in use 3, locked 3
219 lock struct(s), heap size 24784, 2906 row lock(s), undo log entries 7
MySQL thread id 5, OS thread handle 140024996841216, query id 84 localhost us updating
UPDATE TestTable
SET Column1 = 1,
Column2 = sysdate(),
Column3 = '026'
Column4 = 0
AND Column5 = 485
AND Column6 = 'SEK'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 417 page no 1493 n bits 1000 index idx_Column6 of table test.TestTable trx id 102972 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 859 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 3; hex 53454b; asc SEK;;
1: len 8; hex 80000000007e1452; asc ~ R;;
--结束END--
本文标题: MySQL DeadLock故障排查全过程记录
本文链接: https://www.lsjlt.com/news/51943.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-14
2024-05-14
2024-05-14
2024-05-14
2024-05-14
2024-05-14
2024-05-14
2024-05-14
2024-05-14
2024-05-14
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0