Identifying and killing blocking transactions in InnoDB

The MySQL Server has a few options to help deal with transactions holding InnoDB locks for excessive periods of time. The –innodb-lock-wait-timeout option is one such option, but that just affects statements waiting on locks already held by another transaction. If you want to ensure your application isn’t holding locks for long periods of time to start with, what options do you have?

Well, the manual has a useful example that leverages InnoDB tables in INFORMATION_SCHEMA to show which transactions are locking rows needed by other transactions. That’s cool stuff, and you can take this a step further by looking for all connections with open transactions which exceed a given threshold or are blocking other transactions:

The next step you can take is to create an EVENT which logs this, or even issues KILL CONNECTION (sadly, there is no KILL TRANSACTION, and KILL QUERY doesn’t rollback the transaction) commands on the blocking transaction. You can also create control tables that allow you to fine-tune reporting or action thresholds for specific user/host combinations:

And now there’s an EVENT running every 5 seconds which will kill connections having InnoDB transactions older than the threshold (30s, unless configured differently for the user/host combination in the control table). It will also log information to an audit table (test.innodb_trx_kill_audit) about the connections before they are killed. One could easily tweak the stored procedure to simply record the audit details instead of killing connections. Here’s an example of this in action:

Fair point. The example above assumes the DBA will create the stored routine with an appropriate security context for successful execution, and focuses more on how to use the INFORMATION_SCHEMA tables effectively. The documentation regarding stored routine security is a very useful resource.

The first query above pulls information from the information_schema.processlist table (aliased as “p”). You can include the info column in that query to return any currently-executing SQL statements for that connection.