db_event_kill_long_transactions.sql

CREATE EVENT kill_long_transactions ON SCHEDULE EVERY x SECOND
DO
  BEGIN
      DECLARE max_transaction_time INT DEFAULT 25; 
      DECLARE done INT DEFAULT 0;
      DECLARE killed_id BIGINT;
      DECLARE killed_user VARCHAR(16);
      DECLARE killed_host VARCHAR(64);
      DECLARE kill_stmt VARCHAR(20);
      DECLARE time_taken VARCHAR(20);
      DECLARE running_stmt TEXT;

      DECLARE long_transactions CURSOR FOR
         SELECT 
              trx.trx_mysql_thread_id thd_id,
              ps.user,
              ps.host,
              CURRENT_TIMESTAMP- trx.trx_started,
              trx.trx_query
         FROM INFORMATION_SCHEMA.INNODB_TRX trx
         JOIN INFORMATION_SCHEMA.PROCESSLIST ps ON trx.trx_mysql_thread_id = ps.id
        WHERE (trx.trx_started < (CURRENT_TIMESTAMP - INTERVAL ‘x’ SECOND)) AND ((ps.user!= 'migration') and (ps.user !='system user')and (ps.user !='event_scheduler') and (ps.COMMAND != 'Killed'));
       
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
     
      OPEN long_transactions;
        kill_loop: LOOP
          FETCH long_transactions INTO
            killed_id, killed_user, killed_host,time_taken, running_stmt;

          IF done THEN
            LEAVE kill_loop;
          END IF;

          SET @kill := killed_id;
          CALL mysql.rds_kill(@kill);
        END LOOP;
      CLOSE long_transactions;
  END$$
DELIMITER;