- SELECT
- p2.`HOST` Blockedhost,
- p2.`USER` BlockedUser,
- r.trx_id BlockedTrxId,
- r.trx_mysql_thread_id BlockedThreadId,
- TIMESTAMPDIFF(
- SECOND,
- r.trx_wait_started,
- CURRENT_TIMESTAMP
- ) WaitTime,
- r.trx_query BlockedQuery,
- l.lock_table BlockedTable,
- m.`lock_mode` BlockedLockMode,
- m.`lock_type` BlockedLockType,
- m.`lock_index` BlockedLockIndex,
- m.`lock_space` BlockedLockSpace,
- m.lock_page BlockedLockPage,
- m.lock_rec BlockedLockRec,
- m.lock_data BlockedLockData,
- p.`HOST` blocking_host,
- p.`USER` blocking_user,
- b.trx_id BlockingTrxid,
- b.trx_mysql_thread_id BlockingThreadId,
- b.trx_query BlockingQuery,
- l.`lock_mode` BlockingLockMode,
- l.`lock_type` BlockingLockType,
- l.`lock_index` BlockingLockIndex,
- l.`lock_space` BlockingLockSpace,
- l.lock_page BlockingLockPage,
- l.lock_rec BlockingLockRec,
- l.lock_data BlockingLockData,
- IF (p.COMMAND = 'Sleep', CONCAT(p.TIME,' seconds'), 0) idel_in_trx
- 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
- ORDER BY
- WaitTime DESC \G;
- 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
- ORDER BY
- 等待时间 DESC \G;