Why do I have a blocked query on my Amazon RDS for MySQL DB instance when there's no other active session?

Short description

A query on your Amazon RDS for MySQL DB instance can become blocked even when no other session appears active. This occurs when an uncommitted InnoDB transaction holds a lock or when an XA transaction remains in a PREPARED state without an associated session.

Resolution

Identify uncommitted transactions that block queries

To identify the session that blocks your query, complete the following steps:

1. To view running transactions, run the following query against the INNODB_TRX table:

   SELECT * FROM information_schema.innodb_trx\G

2. To view the waiting transactions and the transactions that block them, run one of the following queries based on your MySQL version:
   MySQL 5.7 and earlier:

   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;
   

   Note: You must activate the performance_schema on your DB instance for the preceding query to work. For more information, see Performance schema quick start on the MySQL website. The blocked transaction can proceed only when the blocking transaction commits or rolls back. If the blocking_query column returns NULL, then the blocking session is idle but still holds locks because it hasn't committed the transaction.

3. If the blocking_query column returns NULL, then run the following query to find the PROCESSLIST_ID of the blocking thread:

   SELECT PROCESSLIST_ID FROM performance_schema.threads WHERE THREAD_ID = BLOCKING_THREAD_ID;

   Note: Replace BLOCKING_THREAD_ID with your blocking thread ID.

4. To determine the last query that the thread ran, run the following query:

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

   Note: Replace BLOCKING_THREAD_ID with your blocking thread ID.

5. To stop the transaction, run the following command:

   CALL mysql.rds_kill(PROCESSLIST_ID);

   Note: Replace PROCESSLIST_ID with your process list ID. A long-running transaction takes significant time and I/O resources to stop or roll back.

Note: Metadata locks from DDL operations such as ALTER TABLE can also block queries but might not appear in the INNODB_TRX table. For more information, see The metadata_locks table on the MySQL website.

Identify XA blocking transactions

An XA transaction in a PREPARED state can persist without an associated session. Because no session exists, the transaction doesn't appear in the process list and CALL mysql.rds_kill(PROCESSLIST_ID) can't stop it.

If the blocking_thread is 0 and rds_kill doesn't work, then an XA transaction might block you.

To identify XA transactions that are in a PREPARED state, run the following command:

XA RECOVER;

Example output:

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

To commit the transaction, run the following command:

XA COMMIT 'RePostTest';

Note: Replace RePostTest with your data column value.

To roll back the transaction, run the following command:

XA ROLLBACK 'RePostTest';

Note: Replace RePostTest with your data column value.

When you commit or roll back a transaction, then you might receive the following error message:

"ERROR 1397 (XAE04): XAER_NOTA: Unknown XID"

If you receive the preceding error message, then run the following command to convert the XID to hexadecimal.

XA RECOVER CONVERT XID;

Example output:

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

Then, use the hexadecimal value in the data column to roll back the transaction.

For more information, see XA transaction SQL statements on the MySQL website.

Related information

Ending a session or query for RDS for MySQL

Options for MySQL DB instances

The INFORMATION_SCHEMA INNODB_TRX Table on the MySQL website

Identifying Blocking Transactions on the MySQL website

Common DBA tasks for MySQL DB instances