如何用单查询解决“Lock wait timeout exceeded; try restarting transaction”错误

你给出的单条KILL (SELECT IDFROM INFORMATION_SCHEMA.PROCESSLIST WHEREUSER='trial_505813')语句无法实现批量终止指定用户的所有进程——MySQL的KILL命令仅支持单个进程ID作为参数，不接受子查询返回的多个ID集合。

下面是几种可行的批量处理方案：

方案1：生成批量KILL语句 #

先执行查询生成所有需要终止的进程对应的KILL命令，复制结果后执行即可：

SELECT CONCAT('KILL ', ID, ';') AS kill_commands
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE `USER`='trial_505813';

执行后会得到类似KILL 25685;、KILL 25686;这样的结果，直接复制这些语句批量执行就能终止该用户的所有进程。

方案2：用存储过程自动批量终止 #

创建一个存储过程来遍历并终止指定用户的进程：

DELIMITER //
CREATE PROCEDURE KillUserProcesses(IN userName VARCHAR(100))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE processId INT;
    DECLARE cur CURSOR FOR SELECT ID FROM INFORMATION_SCHEMA.PROCESSLIST WHERE `USER` = userName;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO processId;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET @killStmt = CONCAT('KILL ', processId);
        PREPARE stmt FROM @killStmt;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    CLOSE cur;
END //
DELIMITER ;

调用这个存储过程来终止目标用户的进程：

CALL KillUserProcesses('trial_505813');

方案3：命令行下用Shell脚本批量处理（适用于服务器端操作） #

如果能通过命令行访问MySQL，可以用以下脚本一键处理：

mysql -u root -p -e "SELECT ID FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER='trial_505813'" | grep -v ID | while read id; do mysql -u root -p -e "KILL $id"; done

注意：执行前要确保当前用户有足够权限，并且根据实际情况替换用户名和MySQL登录参数。

内容的提问来源于stack exchange，提问作者Сергей Соколов