我尝试在运行 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。
相关信息
结束会话或查询
适用于 MySQL 数据库实例的选项
MySQL 网站上的 INFORMATION_SCHEMA_INNODB_TRX 表
在 MySQL 网站上识别阻塞事务
MySQL 数据库实例的常见 DBA 任务