mysq1l Innodb中监控阻塞

Innodb中监控阻塞

select b.trx_mysql_thread_id AS'被阻塞线程'
,b.trx_query AS '被阻塞SQL'
,c.trx_mysql_thread_id AS '阻塞线程'
,c.trx_query AS '阻塞SQL'
,(UNIX_TIMESTAMP()-UNIX_TIMESTAMP(c.trx_started)) AS '阻塞时间'
FROM information_schema.innodb_lock_waits a
JOIN information_schema.innodb_trx b ON a.requesting_trx_id=b.trx_id
JOIN information_schema.innodb_trx c ON a.blocking_trx_id=c.trx_id
#WHERE (UNIX_TIMESTAMP()-UNIX_TIMESTAMP(c.trx_started))>60

#查询当前连接号
select connection_id()

#设置超时间

set globel innodb_lock_wait_timeout=180;

user test;

select * from t for update;

时实监控：

SET GLOBAL general_log = 'ON'; --开启日志监控。
SHOW VARIABLES LIKE "general_log%";

#在日志中查看不方便 ，如果让sql语句显示在数据库中的一个表中
SET GLOBAL log_output = 'TABLE';

SELECT * FROM mysql.general_log ORDER BY event_time DESC

SELECT * FROM information_schema.PROCESSLIST WHERE info IS NOT NULL;