select
'Blocker' role,
p.id,
p.user,
left(p.host, locate(':', p.host) - 1) host,
tx.trx_id,
tx.trx_state,
tx.trx_started,
timestampdiff(second, tx.trx_started, now()) duration,
lo.lock_mode,
lo.lock_type,
lo.lock_table,
lo.lock_index,
tx.trx_query,
tx.trx_tables_in_use,
tx.trx_tables_locked,
tx.trx_rows_locked
from
information_schema.innodb_trx tx,
information_schema.innodb_lock_waits lw,
information_schema.innodb_locks lo,
information_schema.processlist p
where
lw.blocking_trx_id = tx.trx_id
and p.id = tx.trx_mysql_thread_id
and lo.lock_trx_id = tx.trx_id
union all
select
'Blockee' role,
p.id,
p.user,
left(p.host, locate(':', p.host) - 1) host,
tx.trx_id,
tx.trx_state,
tx.trx_started,
timestampdiff(second, tx.trx_started, now()) duration,
lo.lock_mode,
lo.lock_type,
lo.lock_table,
lo.lock_index,
tx.trx_query,
tx.trx_tables_in_use,
tx.trx_tables_locked,
tx.trx_rows_locked
from
information_schema.innodb_trx tx,
information_schema.innodb_lock_waits lw,
information_schema.innodb_locks lo,
information_schema.processlist p
where
lw.requesting_trx_id = tx.trx_id
and p.id = tx.trx_mysql_thread_id
and lo.lock_trx_id = tx.trx_id \G