mysql 如何处理僵尸事务(空等待不提交也不回滚的事务)

创建存储过程，处理僵尸事务线程，如果发现僵尸事务线程超过2分钟不提交不回滚，杀死线程。

CREATE DEFINER=`root`@`%` PROCEDURE `kill_idle_transactions`()
BEGIN
    #定义变量
    DECLARE done INT DEFAULT FALSE; -- 游标中止状态的默认值
    DECLARE kill_command VARCHAR(255);  -- 杀死线程的命令
    DECLARE trx_started DATETIME;  -- 事务的开始时间
    DECLARE process_id BIGINT; -- 事务线程的id
    DECLARE process_user VARCHAR(16); -- 执行事务线程的用户
    DECLARE process_host VARCHAR(64); -- 执行事务线程所在的机器
    DECLARE process_db VARCHAR(64); -- 当前事务线程所在的数据库
    DECLARE process_command VARCHAR(16); -- 当前事务线程的执行状态
    DECLARE process_time INT; -- 当前事务线程所执行语句的执行时间
    DECLARE process_state VARCHAR(64); -- 当前事务线程的状态
    DECLARE process_info LONGTEXT; -- 当前事务线程执行的语句
		
    -- 定义游标 这里查询的是没有执行语句,没有提交,空等待并且睡眠中的事务链接以及相关的附加消息, 
    -- 同一个事务的执行时间{process.TIME}每次执行语句都会重置, 如果出现时间特别长的并且执行语句{process.INFO}为空,就可以判断是僵尸事务线程。
    DECLARE cur CURSOR FOR
    SELECT
      CONCAT('KILL ', trx_mysql_thread_id, ';') AS kill_command,  -- 拼接杀死线程的命令
      trx.trx_started, -- 事务的开始时间
      process.ID, -- 事务线程的id
      process.USER, -- 执行事务线程的用户
      process.HOST, -- 执行事务线程所在的机器
      process.DB, -- 当前事务线程所在的数据库
      process.COMMAND, -- 当前事务线程的执行状态
      process.TIME,  -- 当前事务线程当前语句的执行时间
      process.STATE, -- 当前事务线程的状态
      process.INFO -- 当前事务线程执行的语句
    FROM
      information_schema.innodb_trx trx
    LEFT JOIN information_schema.PROCESSLIST process ON trx_mysql_thread_id = process.ID
    WHERE
      process.db =  DATABASE()  -- 判断是当前数据库的
      AND process.INFO IS NULL -- 判断当前执行语句为空的
      AND process.`command`='Sleep'; -- 判断当前事务执行状态为睡眠状态的
    -- 声明 CONTINUE HANDLER 处理游标完成后的状态
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
		
		
		
		#创建日志表
    SET @create_table_sql = NULL;

    -- 动态生成表名
    -- SET @dynamic_table_name = CONCAT('event_kill_idle_tran_log_', DATABASE());
    SET @dynamic_table_name = 'event_kill_idle_tran_log';
    -- 构建创建表的SQL语句
    SET @create_table_sql = CONCAT(
        'CREATE TABLE IF NOT EXISTS ', @dynamic_table_name, ' (
            execution_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT ''执行时间'',
            kill_command VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT ''杀死线程的命令'',
            trx_started DATETIME DEFAULT NULL COMMENT ''事务的开始时间'',
            process_id BIGINT DEFAULT NULL COMMENT ''事务线程的id'',
            process_user VARCHAR(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT ''执行事务线程的用户'',
            process_host VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT ''执行事务线程所在的机器'',
            process_db VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT ''当前事务线程所在的数据库'',
            process_command VARCHAR(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT ''当前事务线程的执行状态'',
            process_time INT DEFAULT NULL COMMENT ''当前事务线程当前语句的执行时间'',
            process_state VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT ''当前事务线程的状态'',
            process_info LONGTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci COMMENT ''当前事务线程执行的语句'',
            PRIMARY KEY (execution_time)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT=''记录杀死僵尸线程的日志消息''
    ');

    -- 执行创建表的SQL语句
    PREPARE create_table_stmt FROM @create_table_sql;
    EXECUTE create_table_stmt;
    DEALLOCATE PREPARE create_table_stmt;
		
		
		
    -- 打开游标
    OPEN cur;

    -- 循环遍历游标中的每一行
    read_loop: LOOP
        FETCH cur INTO kill_command, trx_started, process_id, process_user, process_host, process_db, process_command, process_time, process_state, process_info;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 在这里你可以插入到你的表中，示例为：
        SET @insert_sql = CONCAT('
            INSERT INTO ', @dynamic_table_name, ' (
                kill_command, trx_started, process_id, process_user, process_host, process_db,
                process_command, process_time, process_state, process_info
            ) VALUES ('
                ,IFNULL(CONCAT("'",kill_command,"'"),'NULL'), ','
                ,IFNULL(CONCAT("'",trx_started,"'"),'NULL'), ','
                ,IFNULL(CONCAT("'",process_id,"'"),'NULL'), ','
                ,IFNULL(CONCAT("'",process_user,"'"),'NULL'), ','
                ,IFNULL(CONCAT("'",process_host,"'"),'NULL'), ','
                ,IFNULL(CONCAT("'",process_db,"'"),'NULL'), ','
                ,IFNULL(CONCAT("'",process_command,"'"),'NULL'), ','
                ,IFNULL(CONCAT("'",process_time,"'"),'NULL'), ','
                ,IFNULL(CONCAT("'",process_state,"'"),'NULL'), ','
                ,IFNULL(CONCAT("'",process_info,"'"),'NULL'), ')');

    -- 如果执行时间超过120秒 并且执行语句为空的 就杀死线程并记录日志
    IF process_time > 120 AND process_info IS NULL THEN
            -- 执行插入操作
            PREPARE insert_stmt FROM @insert_sql;
            EXECUTE insert_stmt;
            DEALLOCATE PREPARE insert_stmt;

            -- 执行 KILL 命令
            SET @kill_stmt = kill_command;
            PREPARE kill_stmt FROM @kill_stmt;
            EXECUTE kill_stmt;
            DEALLOCATE PREPARE kill_stmt;
    END IF;
    END LOOP;

    -- 关闭游标
    CLOSE cur;
		

END// 
DELIMITER ;

创建事件每分钟执行一次，执行存储过程逻辑

DELIMITER //
CREATE EVENT `kill_idle_transactions` 
ON SCHEDULE EVERY 1 MINUTE -- 每一分钟执行一次
STARTS '2024-08-15 16:56:18' -- 开始执行时间为2024-08-15 16:56:18
ON COMPLETION NOT PRESERVE -- 事件完成后不保留（即事件完成后不会自动重新调度）
ENABLE -- 启用该事件
COMMENT '每分钟执行杀死僵尸线程的存储过程' -- 事件的描述性注释，解释事件的用途
DO
BEGIN
    CALL kill_idle_transactions(); -- 调用存储过程 kill_idle_transactions
END;// 
DELIMITER;