Mysql Waiting transactions diagnostic

waiting_transactions.sql

SELECT
r.trx_id as waiting_trx_id,
r.trx_mysql_thread_id as waiting_thread,
r.trx_query as waiting_query,
b.trx_id as blocking_trx_id,
b.trx_mysql_thread_id as blocking_thread,
b.trx_query as blocking_query,
bp.host as blocking_host,
rp.host as waiting_host,
bp.command as blocking_command,
rp.command as waiting_command,
bp.time as blocking_time,
rp.time as waiting_time,
bp.state as blocking_state,
rp.state as waiting_state,
bp.info as blocking_info,
rp.info as waiting_info
FROM 
information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id
INNER JOIN information_schema.processlist bp ON b.trx_mysql_thread_id = bp.id
INNER JOIN information_schema.processlist rp ON r.trx_mysql_thread_id = rp.id;