定位空闲会话事务没提交导致锁阻塞的脚本

---- 查看事务锁阻塞线程和等待线程
SELECT r.trx_id AS waiting_trx_id,r.`trx_mysql_thread_id` AS waiting_thread,
       TIMESTAMPDIFF(SECOND,r.trx_wait_started,CURRENT_TIMESTAMP) AS wait_time,
       r.`trx_query` AS waiting_query,l.`lock_table` AS waiting_table_lock,
       l.lock_index AS waiting_index_lock,
       b.trx_id AS blocking_trx_id,b.`trx_mysql_thread_id` AS blocking_thread,
       SUBSTRING(p.host,1,INSTR(p.host,':')-1) AS blocking_host,
       SUBSTRING(p.host,INSTR(p.host,':')+1) AS blocking_port,
       IF(p.command="Sleep",p.time,0) AS idle_in_trx,
       b.`trx_query` AS blocking_query
FROM information_schema.`INNODB_LOCK_WAITS` AS w
INNER JOIN information_schema.`INNODB_TRX` AS b  ON b.trx_id=w.blocking_trx_id
INNER JOIN information_schema.`INNODB_TRX` AS r ON  r.trx_id = w.requesting_trx_id
INNER JOIN information_schema.`INNODB_LOCKS` AS l ON w.requested_lock_id=l.lock_id
LEFT JOIN information_schema.`PROCESSLIST` AS p ON p.id=b.trx_mysql_thread_id
ORDER BY wait_time DESC\G





SELECT *  FROM information_schema.`INNODB_LOCKS`\G


-- 如果你因为线程在一个事务中空闲而正在遭受大量的锁操作，下面的这个变种查询可以
-- 告诉你有多少查询被哪些线程阻塞，而没有多余的无用信息。
SELECT CONCAT('thread ',b.trx_mysql_thread_id,' from ',p.host) AS who_blocks,
       IF(p.command="Sleep",p.time,0) AS idle_in_trx,
       MAX(TIMESTAMPDIFF(SECOND,r.trx_wait_started,NOW())) AS max_wait_time,
       COUNT(*) AS num_waiters
FROM information_schema.`INNODB_LOCK_WAITS` AS w
INNER JOIN information_schema.`INNODB_TRX` AS b ON b.trx_id=w.`blocking_trx_id`
INNER JOIN information_schema.`INNODB_TRX` AS r ON r.`trx_id`= w.`requesting_trx_id`
LEFT JOIN   information_schema.`PROCESSLIST` AS p ON p.id= b.`trx_mysql_thread_id`
GROUP BY who_blocks ORDER BY num_waiters DESC\G

*************************** 1. ROW ***************************
   who_blocks: thread 4 FROM localhost -- 注意线程4是阻塞的源头
  idle_in_trx: 2051
max_wait_time: 10
  num_waiters: 3  -- 线程4 阻塞的 线程最多，由此判断线程4是阻塞的源头
*************************** 2. ROW ***************************
   who_blocks: thread 20 FROM Lenovo-PC:51603
  idle_in_trx: 0
max_wait_time: 6
  num_waiters: 2
*************************** 3. ROW ***************************
   who_blocks: thread 11 FROM localhost
  idle_in_trx: 0
max_wait_time: 5
  num_waiters: 1
3 ROWS IN SET (0.00 sec)



set global tx_isolation='read-committed';
set session tx_isolation='read-committed';

select @@global.tx_isolation,@@tx_isolation;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21582653/viewspace-2136990/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21582653/viewspace-2136990/