Kill会话

拼接杀掉sleep的会话

SELECT GROUP_CONCAT(CONCAT('kill ',id) SEPARATOR '; ') AS cmd FROM information_schema.processlist 
WHERE USER='scm' 
AND command='Sleep'
AND db='scm'

查找阻塞：

8.0里：

SELECT
b.trx_mysql_thread_id as 被阻塞id,
b.trx_query as 被阻塞sql,
c.trx_mysql_thread_id as 阻塞id,
c.trx_query as 阻塞sql,
c.OBJECT_SCHEMA,
OBJECT_NAME,
INDEX_NAME
FROM performance_schema.data_lock_waits a
LEFT JOIN
information_schema.INNODB_TRX b ON a.REQUESTING_ENGINE_TRANSACTION_ID = b.trx_id
LEFT JOIN
information_schema.INNODB_TRX c ON a.BLOCKING_ENGINE_TRANSACTION_ID = c.trx_id
LEFT JOIN
performance_schema.data_locks c ON a.REQUESTING_ENGINE_LOCK_ID = c.ENGINE_LOCK_ID

其中trx_mysql_thread_id与processlist中id相同，用于kill

8.0查找阻塞头的id，以便kill

SELECT a.*
FROM information_schema.PROCESSLIST a
inner join performance_schema.threads b
on a.ID = b.PROCESSLIST_ID
inner join performance_schema.data_lock_waits c
on b.THREAD_ID = c.BLOCKING_THREAD_ID
inner join performance_schema.threads d
on d.THREAD_ID = c.REQUESTING_THREAD_ID
where d.PROCESSLIST_ID = 被阻塞的会话id

查看未提交的事务

select t1.id, t2.thread_id, t3.sql_text
from information_schema.processlist t1, 
 performance_schema.threads t2,
performance_schema.events_statements_current t3
where t1.DB='scm'
and t1.id=t2.processlist_id 
and t2.thread_id = t3.thread_id