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

Short description

Blocked queries can occur because a transaction in InnoDB is waiting for another transaction to release a lock. Queries can also be blocked because of uncommitted transactions. These transactions can appear as NULL. Follow these steps to identify the query or session that might be blocking your query.

Resolution

Identify uncommitted transactions

1.    View currently running transactions by running this query against the INNODB_TRX table:

select * from information_schema.innodb_trx\G

2.    Run this query to see which transactions are waiting and which transactions are blocking them.

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

For 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't proceed until the other transaction commits or rolls back.

When identifying blocking transactions, a NULL value is reported for the blocking query if the session that issued the query has become idle. In this case, use the query in Step 2 to find the blocking_thread processlist ID.

3.    For MySQL 5.7 or later, run this query to determine the THREAD_ID of the blocking transaction by the substituting blocking_thread processlist ID:

SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = blocking_thread;

4.    Use the THREAD_ID to query the performance schema events_statements_current table. This determines the last query run by the thread.

Note: Make sure to substitute THREAD_ID with value returned in Step 3.

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

5.    After you identify the blocking session or thread-ID, stop the transaction by running this procedure:

Note: Before you run this procedure, evaluate whether you need the transaction or if it is safe to stop.

CALL mysql.rds_kill(thread-ID);

Note: Stopping or rolling back a long-running operation can be time-consuming and I/O intensive.

Related information

Ending a session or query

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