mysql中如何实现定位空闲会话事务没提交导致锁阻塞的脚本
发布时间：2022-01-10 17:10:51 所属栏目：MySql教程 来源：互联网
导读：这篇文章主要为大家展示了mysql中如何实现定位空闲会话事务没提交导致锁阻塞的脚本，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下mysql中如何实现定位空闲会话事务没提交导致锁阻塞的脚本这篇文章吧。 --- 查
|
这篇文章主要为大家展示了“mysql中如何实现定位空闲会话事务没提交导致锁阻塞的脚本”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下“mysql中如何实现定位空闲会话事务没提交导致锁阻塞的脚本”这篇文章吧。 --- 查看事务锁阻塞线程和等待线程 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 DESCG 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 DESCG *************************** 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; 以上是“mysql中如何实现定位空闲会话事务没提交导致锁阻塞的脚本”这篇文章的所有内容，感谢各位的阅读！ （编辑：张家口站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐