Yao shows you how to
find idling MySQL transactions


I tried to run a query on my Amazon Relational Database Service (Amazon RDS) 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?

The most common cause for blocked queries is uncommitted InnoDB transactions, which can cause new queries to be blocked, sleep, and eventually fail when they exceed the lock wait timeout.

Identify uncommitted transactions

1.     Identify any currently running transactions by running the following query against the INNODB_TRX table:

select * from information_schema.innodb_trx\G

2.    Match the running transactions in the results of the query with the behavior and workload of your application. This can be a time-consuming process, but it's important to understand the potential impact of each query on the state of your database and your application before taking action.

3.    After you identify the blocking transaction, review the transaction's trx_state, and consider the implications of stopping or rolling back the transaction. Note: A rollback of a long-running operation can generate I/O or locking activity on your system that might harm your database, application, or use case.

Stop transactions

If you want to stop the transaction, you can stop (kill) any running InnoDB transactions by following these steps:

1.    Find all InnoDB transactions in the Running status by running the following command:

select * from information_schema.innodb_trx\G

2.    Note the thread ID of the InnoDB transaction that you want to stop.

3.    Stop (kill) any Running transactions by running the following command, where thread-id is a thread ID of a running InnoDB transaction:

CALL mysql.rds_kill(thread-ID)

After the transaction is stopped, try running the blocked query again.

Did this page help you? Yes | No

Back to the AWS Support Knowledge Center

Need help? Visit the AWS Support Center

Published: 2017-03-15

Updated: 2018-07-05