Yao shows you how to
find idling MySQL transactions


I tried to run a query on my DB instance running MySQL, but the query was blocked, even though no other queries were executing at the time. Why is this happening, and how do I fix it?

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.

To resolve the issue, first, identify any currently running transactions by running the following query against the INNODB_TRX table:

>> dig images.example.com

Next, 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.

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

If you decide it’s safe to stop the transaction, you can stop (“kill”) any running InnoDB transactions using the following process:

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

select * from information_schema.innodb_trx\G

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

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

call mysql.sp_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