记一次MySQL执行修改语句超时问题

mysql · 浏览次数 : 0

小编点评

**原因分析：** 1. **提交事务终止导致死锁：**提交事务后，如果数据库连接正常，服务器会为事务分配锁，等待事务完成。如果由于提交事务失败或网络问题导致服务器停止响应，事务会自动放弃锁，导致其他事务无法获取锁，从而进入死锁状态。 2. **wait_timeout和interactive_timeout设置影响锁释放时间：**`wait_timeout`和`interactive_timeout`参数控制了当服务器停止响应时锁的等待时间和交互式锁的等待时间。如果 `wait_timeout`或 `interactive_timeout`设置过低，可能会导致死锁发生。 3. **XA事务回滚操作可能导致死锁：**`XA`事务是一种分布式事务，在执行回滚操作时，需要协调所有参与事务的节点。如果节点间存在网络问题或故障，回滚操作可能会导致事务互相阻塞，从而导致死锁。 **问题排查：** 1. **检查服务器日志：**查看 `mysql` 日志，以了解最近发生死锁的详细信息，包括事务 ID、锁类型、持有者 ID、请求类型等。 2. **查看锁持有和请求情况：**使用 `SHOW INNODB_TRX`、`PERFORMANCE_SCHEMA.DATA_LOCKS` 和 `PERFORMANCE_SCHEMA.DATA_LOCK_WAITS` 等视图查询锁的持有和请求情况，以确定哪些事务正在阻塞其他事务。 3. **检查XA事务信息：**使用 `SHOW XA_RECOVER`等视图查询XA事务信息，以了解回滚操作的状态和结果。 4. **调试线程详情：**使用 `SHOW FULL PROCESSLIST`等视图查询当前线程详情，以查看每个事务的 ID、状态等信息。检查 `trx_mysql_thread_id` 是否为 0，因为可能表示事务被中断。

正文

异常问题

原因分析

这个问题发生在开发环境，怀疑是提交事务时终止项目运行，没有提交该事务，造成死锁
调试该事务时时间太长，为什么说有这个原因呢，因为通过查找日志显示

The client was disconnected by the server because of inactivity. See wait_timeout and interactive_timeout for configuring this behavior.

问题排查

# 当前运行的所有事务
select * from information_schema.innodb_trx;
# 查看锁的持有和请求情况 MySQL8.0
select * from performance_schema.data_locks;
# 查看锁的等待情况 MySQL8.0
select * from performance_schema.data_lock_waits;
# 当前线程详情
show full processlist;


复制

1.发现事务id176602一直在运行状态

锁的持有和请求情况

kill 掉事务关联的mysql线程ID （trx_mysql_thread_id）

KILL 362
复制

trx_mysql_thread_id = 0 无法Kill 接着排查
确认 trx_mysql_thread_id = 0 为XA（分布式）事务

执行回滚操作

#查看XA事务信息
xa recover;
#执行报错可能没有权限
GRANT XA_RECOVER_ADMIN ON *.* TO root@'%' ;
复制

得到

回滚XA事务操作

# XA事务回滚命令的格式：
xa rollback 'left(data,gtrid_length)'，'substr(data,gtrid_length+1,bqual_length)', formatID;
#示列
SELECT left('55f3cdd8-4f45-49c8-bbca-2738fa5a1514:35',38);
SELECT substr('55f3cdd8-4f45-49c8-bbca-2738fa5a1514:35',39,1);
xa rollback '55f3cdd8-4f45-49c8-bbca-2738fa5a1514:3', '5',1;
复制