How to Estimate time for Rollback in a cancelled transaction MySQL ?

Feb 7, 2022

Mins to Read

All

Rollback is an operation, which changes the current state of the transaction to the previous state. Undo logs are generally required if we want to roll back any of the uncommitted transactions and it plays a major role in Isolation.

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, you can check our previous blogs on overview to undo logs.

Rollback

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

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 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_trx\G select sleep(60); select * from information_schema.innodb_trx\G
         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)/(18460230-17169927))) 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.
Chunk the transactions into smaller ones. Heavy deletes and heavy updates can be handled via tools like pt-archiver and oak-chunk-update.
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.

Streamline Your MySQL Operations! Mydbops provides expert MySQL database management services to optimize rollbacks, prevent data loss, and ensure peak performance. Contact us today and let our team empower your MySQL deployments!

No items found.