如何估计取消事务 MySQL 中的回滚时间？

DELIMITER $$

CREATE FUNCTION RollbackTimeCalc(processID INT, timeInterval INT)
RETURNS VARCHAR(225)
DETERMINISTIC
BEGIN 
  DECLARE RollbackModifiedBeforeInterval INT; 
  DECLARE RollbackModifiedAfterInterval INT;
  DECLARE RollbackPendingRows INT; 
  DECLARE Result varchar(20);
      
      SELECT trx_rows_modified INTO RollbackModifiedBeforeInterval from information_schema.innodb_trx where trx_mysql_thread_id = processID and trx_state = 'ROLLING BACK';
      do sleep(timeInterval);
      SELECT trx_rows_modified INTO RollbackModifiedAfterInterval from information_schema.innodb_trx where trx_mysql_thread_id = processID and trx_state = 'ROLLING BACK';
      set Result=SEC_TO_TIME(round((RollbackModifiedAfterInterval*timeInterval)/(RollbackModifiedBeforeInterval-RollbackModifiedAfterInterval)));
      SELECT trx_rows_modified INTO RollbackPendingRows from information_schema.innodb_trx where trx_mysql_thread_id = processID and trx_state = 'ROLLING BACK';
      RETURN(CONCAT('Estimation Time of Rollback : ', Result, ' Pending rows to rollback ', RollbackPendingRows));
END$$
DELIMITER ;

可以看到下面有个会话在执行delete的大批量删除数据操作

[test]> show full processlist;
+-----+------+-----------+---------------+---------+------+----------+--------------------------------------------------------------------+---------+-----------+---------------+
| Id  | User | Host      | db            | Command | Time | State    | Info                                                               | Time_ms | Rows_sent | Rows_examined |
+-----+------+-----------+---------------+---------+------+----------+--------------------------------------------------------------------+---------+-----------+---------------+
| 133 | root | localhost | test          | Query   |    0 | init     | show full processlist                                              |       0 |         0 |             0 |
| 135 | root | localhost | test          | Sleep   |    6 |          | NULL                                                               |    5617 |         0 |             0 |
| 136 | root | localhost | dba_testdb    | Query   |   36 | updating | delete from tb_core_data where 1=1                                 |   36314 |         0 |        869139 |
+-----+------+-----------+---------------+---------+------+----------+--------------------------------------------------------------------+---------+-----------+---------------+
3 rows in set (0.00 sec)

另开一个窗口，把id为136的会话给kill掉，触发MySQL的事务回滚动作。


然后，另开一个窗口 多次执行刚才创建的function， 入参2个，第一个是连接id，第二个是sleep的秒数

[test]> select RollbackTimeCalc(136,5);
+------------------------------------------------------------------------+
| RollbackTimeCalc(136,5)                                                |
+------------------------------------------------------------------------+
| Estimation Time of Rollback : 00:00:33 Pending rows to rollback 915007 |
+------------------------------------------------------------------------+
1 row in set (5.02 sec)

[test]> select RollbackTimeCalc(136,5);
+------------------------------------------------------------------------+
| RollbackTimeCalc(136,5)                                                |
+------------------------------------------------------------------------+
| Estimation Time of Rollback : 00:00:20 Pending rows to rollback 606635 |
+------------------------------------------------------------------------+
1 row in set (5.00 sec)

[test]> select RollbackTimeCalc(136,5);
+------------------------------------------------------------------------+
| RollbackTimeCalc(136,5)                                                |
+------------------------------------------------------------------------+
| Estimation Time of Rollback : 00:00:14 Pending rows to rollback 414103 |
+------------------------------------------------------------------------+
1 row in set (5.00 sec)

[test]> select RollbackTimeCalc(136,5);
+------------------------------------------------------------------------+
| RollbackTimeCalc(136,5)                                                |
+------------------------------------------------------------------------+
| Estimation Time of Rollback : 00:00:07 Pending rows to rollback 218920 |
+------------------------------------------------------------------------+
1 row in set (5.00 sec)

[test]> select RollbackTimeCalc(136,5);
+-----------------------------------------------------------------------+
| RollbackTimeCalc(136,5)                                               |
+-----------------------------------------------------------------------+
| Estimation Time of Rollback : 00:00:02 Pending rows to rollback 51773 |
+-----------------------------------------------------------------------+
1 row in set (5.00 sec)

[test]> select RollbackTimeCalc(136,5);
+-------------------------+
| RollbackTimeCalc(136,5) |
+-------------------------+
| NULL                    |
+-------------------------+
1 row in set (5.00 sec)

可以看到  Estimation Time of Rollback （回滚需要的时间）在不断的变小，直到最后返回结果为NULL。