Mysql內建查詢阻塞語句

標籤：comm   比較   模式   mysq   線程   AC   locking   索引值   color   

 

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    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;

沒有阻塞的情況:

 

Mysql內建查詢阻塞語句