MySQL 排查锁表常用Sql

锁信息

查询是否被锁的表

show OPEN TABLES where In_use > 0;

查询innodb事务获取的锁

select * from information_schema.innodb_trx;

SELECT * FROM `performance_schema`.`data_locks`;

查询会话

当前会话列表

show PROCESSLIST;

基于IP过滤

select * from information_schema.processlist where HOST like "101.86.16.1%"

基于数据库过滤

select * from information_schema.processlist where DB="test";

会话详情

SELECT * FROM sys.session WHERE CONN_ID = 62200

查询会话执行过的sql

SELECT @dt_ts:=UNIX_TIMESTAMP(NOW());
SELECT 
@dt_timer:=MAX(SH.TIMER_START)
FROM performance_schema.threads AS T1
INNER JOIN performance_schema.events_statements_history AS SH
ON T1.`THREAD_ID`=SH.`THREAD_ID`
WHERE T1.PROCESSLIST_ID=CONNECTION_ID();

SELECT 
SH.CURRENT_SCHEMA AS database_name,
REPLACE(REPLACE(REPLACE(SH.`SQL_TEXT`,'
',' '),'
',' '),'	',' ') AS executed_sql,
FROM_UNIXTIME(@dt_ts-CAST((@dt_timer-SH.TIMER_START)/1000000000000 AS SIGNED)) AS start_time,
FROM_UNIXTIME(@dt_ts-CAST((@dt_timer+SH.TIMER_END)/1000000000000 AS SIGNED)) AS end_time,
(SH.TIMER_END-SH.TIMER_START)/1000000000000 AS used_seconds,
SH.TIMER_WAIT/1000000000000 AS wait_seconds,
SH.LOCK_TIME/1000000000000 AS lock_seconds,
SH.ROWS_AFFECTED AS affected_rows,
SH.ROWS_SENT AS send_rows
FROM performance_schema.threads AS T1
INNER JOIN performance_schema.events_statements_history AS SH
ON T1.`THREAD_ID`=SH.`THREAD_ID`
WHERE T1.PROCESSLIST_ID= [会话id]
AND SH.TIMER_START<@dt_timer
ORDER BY SH.TIMER_START ASC;

杀死会话

杀死指定会话

kill 1000;

生成杀死某一IP下会话SQL

select concat("kill ", ID, ";") as command
from information_schema.processlist
 where HOST like "127.0.0.1%"

select concat("kill ", ID, ";") as command
from information_schema.processlist
 where HOST like "101.86.16.1%"