Mysql Question posted in
The official MySQL documentation can be found here.

How do I find and kill long-running transaction in MySQL

Our app has some long-running operations that are wrapped in a transaction, within such transaction it performs dozens of fast queries.

Recently we encountered a problem with one of such transactions – it updated a lot of resources and therefore acquired LOCKs on them, which prevents other update operations to be performed on these resources until that long-running transaction finishes.

Question:
How can we find that transaction’s process ID and kill it if we need to?
I wanted to find it via SHOW PROCESSLIST output (maybe just find a command that is executing long, for example) but this seems problematic: as I understand that started transaction will have in the Sleep state and it will not have any other details, and given we have a pool of persistent connections to DB (which also have Sleep state and show long execution time) I am not sure how I can find that specific connection that I want to kill.

This is for the future reference, the transaction we had a problem with already finished.

Hope it makes sense.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    You can get currently running transaction details from information_schema.innodb_trx table:

    SELECT
    trx_id,
    trx_mysql_thread_id,
    trx_state,
    trx_started,
    trx_wait_started,
    trx_query,
    trx_operation_state
FROM
    information_schema.innodb_trx
WHERE
    trx_state = 'RUNNING'
ORDER BY
    trx_started;

    The useful columns for the purpose of the question above are:

    1. trx_mysql_thread_id - this is the process ID that you can pass to KILL {process id}; command to kill the transaction.
    2. trx_started - shows the datetime of when the transaction was started, useful to determine which transaction is the one I am looking for.

    Also, it might be useful to JOIN information_schema.processlist table to get the current execution time of each transaction (for convenience of determining which is the longest transaction running):

    SELECT 
    trx.trx_id,
    trx.trx_mysql_thread_id,
    trx.trx_started,
    p.TIME
FROM 
    information_schema.innodb_trx trx
    JOIN information_schema.processlist p ON trx.trx_mysql_thread_id = p.ID
WHERE 
    trx.trx_state = 'RUNNING';

    You can grab more columns that you'll find useful there.


  2. 0

    Try the below statement please:

    SELECT * FROM `information_schema`.`innodb_trx` ORDER BY `trx_started`;
kill [trx_mysql_thread_id];
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search