Estimating time for Rollback Operation

Mysql

Rollback is an operation, which changes the current state of the transaction to the previous state, similar to UNDO.

Undo logs are generally required if we want to roll back any of the uncommitted transactions. 

For any changes made during a transaction, it must be stored priorly, because they are required if we choose to roll back the transaction.

Entries are made in undo logs when data modifications are done. If a transaction modifies data with SQL commands, It will create discrete undo logs for each operation. Once a transaction is committed MySQL is free to purge the undo logs created in that transaction. 

To know more about undo logs, Click here 

Usually, the Rollback process will take more time than the original operation. Because It is a single-threaded process.

Transaction flow diagram

Let’s consider a table sbtest1 with 100 million records.  I am deleting the 30 Million records based on the id<=30000000 conditions. So in between, I forcefully terminated the delete operation.

mysql> show processlist;
+—-+—————–+———–+———+———+——+————————+—————————————-+
| Id | User            | Host      | db      | Command | Time | State                  | Info                                   |
+—-+—————–+———–+———+———+——+————————+—————————————-+
|  5 | event_scheduler | localhost | NULL    | Daemon  | 6669 | Waiting on empty queue | NULL                                   |
| 16 | root            | localhost | sb_test | Query   |    0 | init                   | show processlist                       |
| 17 | root            | localhost | sb_test | Query   |  669 | updating               | delete from sbtest1 where id<=30000000 |
+—-+—————–+———–+———+———+——+————————+—————————————-+
3 rows in set (0.01 sec)

mysql> kill 17;
Query OK, 0 rows affected (0.26 sec)

As we could see in the processlist, the killed transaction is currently in a rollback state. The rollback operation is performed with the help of undo logs.

mysql> show processlist;
+—-+—————–+———–+———+———+——+————————+—————————————-+
| Id | User            | Host      | db      | Command | Time | State                  | Info                                   |
+—-+—————–+———–+———+———+——+————————+—————————————-+
|  5 | event_scheduler | localhost | NULL    | Daemon  | 7306 | Waiting on empty queue | NULL                                   |
| 17 | root            | localhost | sb_test | Killed  |  704 | query end              | delete from sbtest1 where id<=30000000 |
| 18 | root            | localhost | sb_test | Sleep   |  626 |                        | NULL                                   |
| 19 | root            | localhost | NULL    | Query   |    0 | init                   | show processlist                       |
+—-+—————–+———–+———+———+——+————————+—————————————-+
4 rows in set (0.00 sec)

Now, To calculate the estimation time of the rollback process, I have fetched the undo log entries and killed transaction of thread id from information_schema.innodb_trx table output with an interval of 60 secs. The minimum setting 5-sec interval would be helpful to calculate the accurate estimation time of rollback.

mysql> pager grep -e “trx_mysql_thread_id: 17” -e trx_rows_modified
PAGER set to ‘grep -e “trx_mysql_thread_id: 17” -e trx_rows_modified’
mysql> select * from information_schema.innodb_trxG select sleep(60); select * from information_schema.innodb_trxG
         trx_rows_modified: 0
       trx_mysql_thread_id: 17
         trx_rows_modified: 18460230
2 rows in set (0.26 sec)

1 row in set (1 min 0.31 sec)

       trx_mysql_thread_id: 17
         trx_rows_modified: 17169927
1 row in set (0.09 sec)

mysql> n
PAGER set to stdout
mysql> select SEC_TO_TIME(round((17169927*60)/(1846023017169927))) as ‘Estimation Time of Rollback’;
+—————————–+
| Estimation Time of Rollback |
+—————————–+
| 00:13:18                     |
+—————————–+
1 row in set (0.18 sec)

From the above-shared logs, we could see that It will take 13 mins 18 secs more to complete the rollback operation.

To Make the above estimation process into more simple, I have created the RollbackTimeCalc function.

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 ;

We have to pass two parameters to the functions 

RollbackTimeCalc(x,y);

Where 

x is the processlist id of the killed transaction,

y is the time interval of undo log entries

​​mysql> select RollbackTimeCalc(18,5);
+———————————————————————————————————-+
| RollbackTimeCalc(18,5)                                                   |
+———————————————————————————————————+
| Estimation Time of Rollback: 00:06:09 Pending rows to rollback 10341861 |
+———————————————————————————————————-+
1 row in set (5.37 sec)

With the above-created function, we could easily estimate the approximate time of rollback operations is 06 mins 09 sec. 

Note:

Committed transactions cannot be rolled back. 

It is better to set autocommit=0 if we are performing major changes in the databases. and don’t forget to add autocommit=1 at the end of the transaction.