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

解决方法

查询被阻止是因为 InnoDB 中的一个事务正在等待另一个事务释放锁。未提交的事务也可能会阻止查询。这些事务显示为 NULL。

使用 INNODB_TRX 表可识别未提交的事务

要识别阻止您的查询的查询或会话，请完成以下步骤：

1. 要查看当前正在运行的事务，请对 INNODB_TRX 表运行以下查询：

   select * from information_schema.innodb_trx\G

2. 要查看正在等待的事务以及阻止这些事务的事务，请根据您的 MySQL 版本运行以下查询之一。
   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 值。在这种情况下，请使用前面的查询来查找 blocking_thread PROCESSLIST_ID。

3. 对于 MySQL 5.7 或更高版本，通过替换阻止 THREAD_ID 运行以下查询，以确定阻止事务的 PROCESSLIST_ID：

   SELECT PROCESSLIST_ID FROM performance_schema.threads where THREAD_ID = blocking;

4. 使用 THREAD_ID 对 events_statements_current 表运行以下查询，以确定该线程运行的最后一个查询：

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

   **注意：**请将 THREAD_ID 替换为您在步骤 3 中获取的值。

5. 运行以下命令以停止事务：

   CALL mysql.rds_kill(PROCESSLIST_ID);

   **注意：**停止或回滚长时间运行的事务非常耗时且占用大量 I/O。

识别 XA 阻止事务

如果 blocking_thread 为 0，且 CALL mysql.rds_kill(PROCESSLIST_ID); 不起作用，则 XA 事务可能会阻止您。

要识别导致阻止的 XA 事务，请运行以下命令：

XA RECOVER;

前面的命令将返回处于 Prepared（准备）状态的 XA 事务的输出。要停止阻止，请使用数据列回滚或提交事务：

XA RECOVER;

+----------+--------------+--------------+--------------------+
| formatID | gtrid_length | bqual_length | data               |
+----------+--------------+--------------+--------------------+
|   1      |           10 |            0 | RePostTest         |
+----------+--------------+--------------+--------------------+
1 row in set (0.00 sec)

XA COMMIT 'RePostTest';
XA ROLLBACK 'RePostTest';

如果您在执行提交或回滚操作时收到以下错误，请将 XID 转换为十六进制并回滚事务：

“错误 1397 (XAE04)： XAER_NOTA： 未知的 XID”

要恢复事务，请运行以下命令检索 gtrid_length 和 bqual_length，以提取数据字段的必要部分：

mysql> xa recover convert xid;
+----------+--------------+--------------+----------------------------------------+
| formatID | gtrid_length | bqual_length | data                                   |
+----------+--------------+--------------+----------------------------------------+
|   1      |           10 |            0 | 0x5265506F737454657374                 |
+----------+--------------+--------------+----------------------------------------+
1 row in set (0.00 sec)

mysql> XA ROLLBACK X'5265506F737454657374';
Query OK, 0 rows affected (0.01 sec)

有关如何转换 XID 值的详细信息，请参阅 MySQL 网站上的 XA transaction SQL statements。

相关信息

结束 RDS for MySQL 的会话或查询

适用于 MySQL 数据库实例的选项

MySQL 网站上的 The INFORMATION_SCHEMA_INNODB_TRX table

MySQL 网站上的 Identifying blocking transactions

MySQL 数据库实例的数据库管理员常见任务