Why was a query to my Amazon RDS MySQL DB instance blocked when there is no other active session?
Last updated: 2020-11-16
I tried to run a query on my Amazon Relational Database Service (Amazon RDS) DB instance that is running MySQL, but the query was blocked. No other queries were executing at the time. Why was the query blocked, and how do I resolve this issue?
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 (can appear as NULL). Follow the steps below to identify the query or session that might be blocking your query.
Identify uncommitted transactions
1. View currently running transactions by executing the following query against the INNODB_TRX table:
select * from information_schema.innodb_trx\G
2. Run the following query to see which transactions are waiting and which transactions are blocking them:
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;
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 onwards, run the following 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 executed by the thread.
Note: Make sure to substitute the 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 the following procedure:
Note: Stopping or rolling back a long running operation can be time-consuming and IO intensive.