问题描述
为什么我的查询语句被终止,我如何排查并解决?
问题分析
在 MySQL中,当一个事务和另一个发生冲突,查询可能被阻塞,同时,一个事务也可能因为长时间运行的事务/未提交的事务而阻塞(information_schema.innodb_trx query字段显示为NULL)。
解决方案
1.通过对 INNODB_TRX 表执行以下查询,查看当前正在运行的事务:
mysql> select * from information_schema.innodb_trx\G
2.您可以运行如下查询来查看哪些事务正在等待,以及看到哪个事务被阻塞
mysql> SELECT
r.trx_id waiting_trx_id,
r.trx_mysql_thread_id waiting_thread,
r.trx_query waiting_query,
b.trx_id blocking_trx_id,
b.trx_mysql_thread_id blocking_thread,
b.trx_query blocking_query
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b
ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r
ON r.trx_id = w.requesting_trx_id;
输出类似如下:
*************************** 1. row***************************
waiting_trx_id: 11359758
waiting_thread: 1257437
waiting_query: update candidates set score=3 where id=1
blocking_trx_id: 11359225
blocking_thread: 1257043
blocking_query: null
1 row in set (0.04 sec)
注:如果查询中的blocking_query显示为 null,可能是由于未提交的事务导致的冲突,您可以执行下面的操作来进一步获取到详细信息:
3.运行以下查询以确定阻塞事务的 THREAD_ID:这里需要替换 blocking_thread
mysql>SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = blocking_thread;
注:查询适用于MySQL 5.7以及以上版本
4.获取到thread id之后,使用 THREAD_ID 查询 performance_scheam.events_statements_current表[1],可以获取到具体的查询语句
mysql> SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_current WHERE THREAD_ID = THREAD_ID;
5.确定阻塞会话后,通过运行kill 语句[2]来停止该事务
mysql> kill 1261222;
ERROR 1317 (70100): Query execution was interrupted
注:对于回滚长时间运行的事务/大事务可能需要花费很长的时间。
参考文档
[1] https://dev.mysql.com/doc/refman/5.7/en/performance-schema.html
[2] https://dev.mysql.com/doc/refman/5.7/en/kill.html
如果您有其他问题,欢迎您联系火山引擎技术支持服务