mysql会话监控阻塞的sql

Question

当mysql数据库中的表被我锁上几个后，为什么用这两个sql语句查不出被锁的表和被锁的个数，大神们有没有什么好的sql语句啊，求助！！！

 SELECT 
  p2.`HOST` 被阻塞方host,
  p2.`USER` 被阻塞方用户,
  r.trx_id 被阻塞方事务id,
  r.trx_mysql_thread_id 被阻塞方线程号,
  TIMESTAMPDIFF(
    SECOND,
    r.trx_wait_started,
    CURRENT_TIMESTAMP
  ) 等待时间,
  r.trx_query 被阻塞的查询,
  l.lock_table 阻塞方锁住的表,
  m.`lock_mode` 被阻塞方的锁模式,
  m.`lock_type` "被阻塞方的锁类型(表锁还是行锁)",
  m.`lock_index` 被阻塞方锁住的索引,
  m.`lock_space` 被阻塞方锁对象的space_id,
  m.lock_page 被阻塞方事务锁定页的数量,
  m.lock_rec 被阻塞方事务锁定行的数量,
  m.lock_data 被阻塞方事务锁定记录的主键值,
  p.`HOST` 阻塞方主机,
  p.`USER` 阻塞方用户,
  b.trx_id 阻塞方事务id,
  b.trx_mysql_thread_id 阻塞方线程号,
  b.trx_query 阻塞方查询,
  l.`lock_mode` 阻塞方的锁模式,
  l.`lock_type` "阻塞方的锁类型(表锁还是行锁)",
  l.`lock_index` 阻塞方锁住的索引,
  l.`lock_space` 阻塞方锁对象的space_id,
  l.lock_page 阻塞方事务锁定页的数量,
  l.lock_rec 阻塞方事务锁定行的数量,
  l.lock_data 阻塞方事务锁定记录的主键值,
  IF (
    p.COMMAND = 'Sleep',
    CONCAT(p.TIME, ' 秒'),
    0
  ) 阻塞方事务空闲的时间 
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.INNODB_LOCKS l 
    ON w.blocking_lock_id = l.lock_id 
    AND l.`lock_trx_id` = b.`trx_id` 
  INNER JOIN information_schema.INNODB_LOCKS m 
    ON m.`lock_id` = w.`requested_lock_id` 
    AND m.`lock_trx_id` = r.`trx_id` 
  INNER JOIN information_schema.PROCESSLIST p 
    ON p.ID = b.trx_mysql_thread_id 
  INNER JOIN information_schema.PROCESSLIST p2 
    ON p2.ID = r.trx_mysql_thread_id;

 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;

Answer 1

http://blog.csdn.net/yabingshi_tech/article/details/46562985