MySQL Transaction Locks

trax_locks.sql

select * from INFORMATION_SCHEMA.INNODB_TRX;

select * from INFORMATION_SCHEMA.INNODB_LOCKS;

SELECT CONCAT('KILL ', trx.trx_mysql_thread_id) kill_statement, trx.trx_mysql_thread_id thd_id, ps.user, ps.host, trx.trx_query FROM INFORMATION_SCHEMA.INNODB_TRX trx JOIN INFORMATION_SCHEMA.PROCESSLIST ps ON trx.trx_mysql_thread_id = ps.id WHERE user != 'system_user';

KILL 17333;

-- http://www.markleith.co.uk/2011/05/31/finding-and-killing-long-running-innodb-transactions-with-events/

ALTER EVENT kill_long_transactions DISABLE;
ALTER EVENT kill_long_transactions ENABLE;