为什么在没有其他活动会话的情况下,对 Amazon RDS MySQL 数据库实例的查询被阻止?

上次更新日期:2022 年 7 月 25 日

我尝试在运行 MySQL 的 Amazon Relational Database Service (Amazon RDS) 数据库实例上运行查询,但查询被阻止了。当时并没有执行任何其他查询。为什么它会被阻止,我又该如何解决这个问题?

简短描述

由于 InnoDB 中有一个事务正在等待另一个事务释放锁,因此可能会发生查询被阻止。查询也可能因为未提交的事务而被阻止。这些事务可能显示为 NULL。按照以下步骤确定可能正在阻止您查询的查询或会话。

解决方法

确定未提交的事务处理

1.    通过对 INNODB_TRX 表运行此查询,查看当前正在运行的事务:

select * from information_schema.innodb_trx\G

2.    运行此查询以查看哪些事务正在等待以及哪些事务阻止了它们。

对于 MySQL 5.7 及更低版本:

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;

对于 MySQL 8.0:

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       performance_schema.data_lock_waits w
INNER JOIN information_schema.innodb_trx b
  ON b.trx_id = w.blocking_engine_transaction_id
INNER JOIN information_schema.innodb_trx r
  ON r.trx_id = w.requesting_engine_transaction_id;

注意:在另一个事务提交或回退之前,被阻止的事务无法继续。

确定阻塞事务时,如果发出该查询的会话变为空闲,则为阻止查询报告 NULL 值。在这种情况下,请使用第 2 步中的查询来查找 blocking_thread 进程列表 ID。

3.    对于 MySQL 5.7 或更高版本,通过替换 blocking_thread 进程列表 ID 运行此查询以确定阻止事务的 THREAD_ID:

SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = blocking_thread;

4.    使用 THREAD_ID 查询性能架构 events_statements_current 表。这将决定该线程最后执行的查询。

注意:确保用步骤 3 中返回的值替代 THREAD_ID

SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_current WHERE THREAD_ID = THREAD_ID;

5.    确定阻止会话或 thread-ID 后,通过运行此程序来停止该事务:

注意:在运行此程序之前,请评估您是否需要该事务,或者是否可以安全地停止。

CALL mysql.rds_kill(thread-ID);

注意:停止或回滚长时间运行的操作可能非常耗时且耗费 I/O。