MySQL - Automatically recover from a big commit latency bottleneck

recover_commit_latency.sql

-- Note, this will CANCEL commits if they take more than 20s to complete.
-- It is assumed that if you have commit latency greater than 20s that something is terribly wrong, 
-- and you are now losing data due to a lack of throughput already.
-- This will help resume opperation at the cost of potentially dropping locking changes.

-- Create a stored proceedure that can recover from a commit latency lock-up
-- Do not run multiple of this at once.
-- When running loop and kill till there are none to kill.
DROP PROCEDURE IF EXISTS `recover_commit_latency`;
DELIMITER ;;
CREATE PROCEDURE `recover_commit_latency`()
BEGIN
  DECLARE sql_string MEDIUMTEXT;
  SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
  SET FOREIGN_KEY_CHECKS = 0;
  SET @running = (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.PROCESSLIST
    WHERE
      INFO LIKE "%recover_commit_latency%"
      AND INFO NOT LIKE "%INFORMATION_SCHEMA.PROCESSLIST%"
    );
  IF @running = 0 THEN 
    SELECT @sql_string := 'recover_commit_latency';
    WHILE sql_string != '' DO
      SELECT @sql_string := CONCAT('KILL ', ID, ';') -- recover_commit_latency
      FROM INFORMATION_SCHEMA.PROCESSLIST
      WHERE COMMAND != 'Sleep'
        AND INFO = 'COMMIT'
        AND TIME > 20
      ORDER BY TIME DESC
      LIMIT 1;
      IF @sql_string != '' THEN
        PREPARE st FROM @sql_string;
        EXECUTE st;
      END IF;
    END WHILE;
  END IF;
END;;
DELIMITER ;

-- Create an event to fire that procedure every second
DROP EVENT IF EXISTS `recover_commit_latency`;
CREATE EVENT `recover_commit_latency`
ON SCHEDULE EVERY 1 SECOND
STARTS CURRENT_TIMESTAMP
ON COMPLETION PRESERVE
DO
  CALL recover_commit_latency();

-- Show current events
SHOW EVENTS;