mysql排查锁等待超时记录

select a.trx_id 事务id ,a.trx_mysql_thread_id 事务线程id,a.trx_query 事务sql from INFORMATION_SCHEMA.INNODB_LOCKS b,INFORMATION_SCHEMA.innodb_trx a where b.lock_trx_id=a.trx_id;

show OPEN TABLES where In_use > 0;

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

select * from information_schema.INNODB_TRX;

select * from information_schema.PROCESSLIST WHERE ID = '661499';

show engine innodb status;

show PROCESSLIST;

select thread_id,event_name,lock_time,digest_text,current_schema from events_statements_history_long where thread_id=1632 ;

-- 设置打印general_log，所有mysql日志

show variables like '%general%';

set global general_log=1;
或
set global general_log=ON;

-- 查看日志输出信息

show variables where Variable_name="log_output";

 

可以在my.cnf中设置innodb_rollback_on_timeout=on