MySQL5.7中实现定时执行某个SQL语句/存储过程，示例：定时清理MySQL进程死锁信息【测试成功】

巴韭特锁螺丝 2025-01-15

一、需求背景

某项目中，一直出现数据库死锁的情况发生，无法从代码层面解决，此时没有办法只能通过定期清理死锁信息来尝试。

二、查询死锁信息相关SQL

SHOW OPEN TABLES WHERE In_use > 0;


SELECT * FROM information_schema.innodb_trx


SELECT * from information_schema.`PROCESSLIST` WHERE Time > 1000
AND USER = 'yl' ORDER BY TIME desc;


kill 61


SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';




SET GLOBAL innodb_lock_wait_timeout = 150;
commit;


SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';


SET GLOBAL max_connections = 2000;


SHOW VARIABLES LIKE 'max_connections';


SHOW STATUS LIKE 'Threads_connected';


SELECT * FROM information_schema.innodb_trx

三、创建存储过程函数

CREATE DEFINER=`yl`@`%` PROCEDURE `kill_long_running_processes_yl`()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE process_id INT;
  DECLARE cur CURSOR FOR
    SELECT id
    FROM information_schema.PROCESSLIST
    WHERE Time > 1000
      AND USER = 'yl';
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;


  OPEN cur;


  read_loop: LOOP
    FETCH cur INTO process_id;
    IF done THEN
      LEAVE read_loop;
    END IF;


    -- 在这里添加额外的检查或日志 (可选)
    -- SELECT 'Killing process:', process_id;


    KILL process_id;
  END LOOP;


  CLOSE cur;
END

CALL kill_long_running_processes_yl(); #手工执行一次验证

四、开启event事件任务

show variables like '%sche%';

set global event_scheduler=1; #如果没有开启则手工开启

五、创建事件任务

create event if not exists LOCK_monitor
    on schedule every 600 second  
    on completion preserve
    do call kill_long_running_processes_yl();

六、开启/关闭事件任务

alter event LOCK_monitor ON
COMPLETION PRESERVE DISABLE;   ###关闭事件任务


alter event LOCK_monitor ON
COMPLETION PRESERVE ENABLE;    ###开启事件任务

七、查询事件任务

SHOW EVENTS;    #查看全部事件

SELECT * FROM INFORMATION_SCHEMA.EVENTS;  ##查看事件详细信息

select * from information_schema.events where status = 'enabled'; ###查看所有开启的事件

SELECT * FROM mysql.event; ###使用mysql.event表来查看事件

文章转载自巴韭特锁螺丝，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL5.7中实现定时执行某个SQL语句/存储过程，示例：定时清理MySQL进程死锁信息【测试成功】

评论