select
r.trx_id waiting_trx_id,
r.trx_mysql_thread_id waiting_thread,
r.trx_query waiting_query,
b.trx_id blocking_trx_id,
b.trx_mysql_thread_id blocking_thread,
b.trx_query blocking_query
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;
查看更具体的事务等待状况
select
b.trx_state,
e.state,
e.time,
d.state as block_state,
d.time as block_time,
a.requesting_trx_id,
a.requested_lock_id,
b.trx_query,
b.trx_mysql_thread_id,
a.blocking_trx_id,
a.blocking_lock_id,
c.trx_query as block_trx_query,
c.trx_mysql_thread_id as block_trx_mysql_tread_id
from
information_schema.innodb_lock_waits a
left join information_schema.innodb_trx b on a.requesting_trx_id = b.trx_id
left join information_schema.innodb_trx c on a.blocking_trx_id = c.trx_id
left join information_schema.processlist d on c.trx_mysql_thread_id = d.id
left join information_schema.processlist e on b.trx_mysql_thread_id = e.id
order by
a.requesting_trx_id;
查看未关闭的事务
mysql 5.5/mysql5.7
select
a.trx_id,
a.trx_state,
a.trx_started,
a.trx_query,
b.id,
b. user,
b. host,
b.db,
b.command,
b.time,
b.state,
b.info
from
information_schema.innodb_trx a
left join information_sc