Why was a query to my Amazon RDS for MySQL DB instance blocked when there's no other active session?

Resolution

Blocked queries occur because a transaction in InnoDB is waiting for another transaction to release a lock. Uncommitted transactions can also block queries. These transactions appear as NULL.

Use INNODB_TRX table to identify uncommitted transactions

To identify the query or session that's blocking your query, complete the following steps:

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

   select * from information_schema.innodb_trx\G

2. To view the transactions that are waiting and the transactions that are blocking 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: The blocked transaction can proceed only when the other transaction commits or rolls back. When the session that issued the query is idle, a NULL value is reported for the blocking query. In this case, use the preceding query to find the blocking_thread PROCESSLIST_ID.

3. For MySQL 5.7 or later, run the following query to determine the PROCESSLIST_ID of the blocking transaction by the substitute blocking THREAD_ID:

   SELECT PROCESSLIST_ID FROM performance_schema.threads where THREAD_ID = blocking;

4. Run the following query on the events_statements_current table with the THREAD_ID to determine the last query that the thread ran:

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

   Note: Replace THREAD_ID with the value that you received in step 3.

5. Run the following command to stop the transaction:

   CALL mysql.rds_kill(PROCESSLIST_ID); 

   Note: To stop or roll back a long-running transaction is time-consuming and I/O intensive.

Identify XA blocking transactions

If the blocking_thread is 0 and CALL mysql.rds_kill(PROCESSLIST_ID); doesn't work, then an XA transaction might block you.

To identify the XA transaction that's causing the block, run the following command:

XA RECOVER;

The preceding command returns an output for XA transactions that are in the Prepared state. To stop the block, use the data column to roll back or commit the transaction:

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';

If you receive the following error when you perform the commit or rollback, then convert the XID to hexadecimal and roll back the transaction:

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

To recover the transaction, run the following command to retrieve the gtrid_length and bqual_length to extract the necessary parts of the data field:

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)

For more information about how to convert XID values, 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