mysql 数据库锁表排查

SHOW OPEN TABLES WHERE In_use > 0;
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
SELECT * from performance_schema.events_statements_current;
SELECT * from performance_schema.threads;
SELECT * from information_schema.PROCESSLIST;
select * from performance_schema.events_statements_history where thread_id=507;

SELECT b.trx_mysql_thread_id as ‘blocked_thread_id’ ,b.trx_query AS ‘blocked_sql_text’
,c.trx_mysql_thread_id AS ‘blocker_thread_id’,c.trx_query AS ‘blocker_sql_text’
,f.SQL_TEXT,d.ID,c.trx_started
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
JOIN information_schema.PROCESSLIST d ON d.ID = c.trx_mysql_thread_id
JOIN performance_schema.threads e ON e.PROCESSLIST_ID = d.ID
JOIN performance_schema.events_statements_current f ON f.THREAD_ID = e.THREAD_ID;