mysql死锁查看

查看事务等待状况

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