MySQL-关于数据库锁表-阻塞整个jar应用-查锁表sql进程-慢SQL-卡顿
https://www.yisu.com/zixun/574176.html
select id, db, user, host, command, time, state, info from information_schema.processlist where command != 'Sleep' order by time desc\G;
select id, db, user, host, command, time, state, info from information_schema.processlist where command = 'Sleep' order by time desc\G;
id - 线程ID，可以用：kill id; 杀死一个线程，很有用 db - 数据库
user - 用户
host - 连库的主机IP
command - 当前执行的命令，比如最常见的：Sleep，Query，Connect 等
time - 消耗时间，单位秒，很有用 state - 执行状态，比如：Sending data，Sorting for group，Creating tmp table，Locked等等，很有用， 其他状态可以看看本文最后的参考文章
查看innodb的事务表INNODB_TRX，看下里面是否有正在锁定的事务线程，如果有，则可能是该事务阻塞了其它线程
SELECT * FROM information_schema.INNODB_TRX\G;
SELECT trx_mysql_thread_id,trx_query FROM INFORMATION_SCHEMA.INNODB_TRX where trx_query like "%course%" or trx_query like "%edu%" or trx_query like "%exam%" or trx_query like "%inte%"\G
-- and 后培训的相关的
-- 看我：
SELECT trx_mysql_thread_id,trx_query FROM INFORMATION_SCHEMA.INNODB_TRX where trx_mysql_thread_id in (select id from information_schema.processlist where command != 'Sleep' ) and (trx_query like "%course%" or trx_query like "%edu%" or trx_query like "%exam%" or trx_query like "%inte%")\G
SELECT concat("kill ", trx_mysql_thread_id, ";") as command FROM INFORMATION_SCHEMA.INNODB_TRX where trx_mysql_thread_id in (select id from information_schema.processlist where command != 'Sleep' ) and (trx_query like "%course%" or trx_query like "%edu%" or trx_query like "%exam%" or trx_query like "%inte%")\G
select concat("kill ", ID, ";") as command
from information_schema.processlist
where HOST regext "some condtion";
kill 8512720;
kill 8512665;
kill 8512664;
2024年1月17号验证下面的是可以kill 掉的
SELECT concat("kill ", trx_mysql_thread_id, ";") as command ,trx_query FROM INFORMATION_SCHEMA.INNODB_TRX
发生死锁后，看看有没有多个事务注解，尝试对锁表的sql执行逻辑加代码锁，或写个异步执行，参考《MySQL-代码-Deadlock found when trying to get lock-死锁-提交事务后异步执行》
正文到此结束
热门推荐
相关文章
近期评论
-
来自： 运维-docker专题-创建镜像-操作容器-制作jdk17的docker镜像等
-
来自： 留言板
-
来自： 留言板
-
来自： js-处理网页图片宽度自适应屏幕宽度
-
来自： 留言板
