首页 > 代码库 > MySQL定位锁争用比较严重的表
MySQL定位锁争用比较严重的表
1:查看当前的事务
mysql> SELECT * FROM information_schema.innodb_trx \G
2:查看当前锁定的事务
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
3:查看当前等锁的事务
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, TIMESTAMPDIFF( SECOND, r.trx_wait_started, CURRENT_TIMESTAMP ) wait_time, r.trx_query waiting_query, l.lock_table waiting_table_lock, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, SUBSTRING( p. HOST, 1, INSTR(p. HOST, ‘:‘) - 1 ) blocking_host, SUBSTRING(p. HOST, INSTR(p. HOST, ‘:‘) + 1) blocking_port, IF (p.COMMAND = ‘Sleep‘, p.TIME, 0) idel_in_trx, 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 INNER JOIN information_schema.INNODB_LOCKS l ON w.requested_lock_id = l.lock_id LEFT JOIN information_schema. PROCESSLIST p ON p.ID = b.trx_mysql_thread_id ORDER BY wait_time DESC;
MySQL定位锁争用比较严重的表
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。