The Transaction Behavior Impact of innodb_rollback_on_timeout in MySQL

innodb_rollback_on_timeoutI would say that innodb_rollback_on_timeout is a very important parameter. In this blog, I am going to explain “innodb_rollback_on_timeout” and how it affects the transaction behavior at the MySQL level. I describe two scenarios with practical tests, as it would be helpful to understand this parameter better.

What is innodb_rollback_on_timeout?

The parameter Innodb_rollback_on_timeout will control the behavior of the transaction when a failure occurs with timeout.

  • If –innodb-rollback-on-timeout=OFF ( default ) is specified, InnoDB rollbacks only the last statement on a transaction timeout.
  • If –innodb-rollback-on-timeout=ON is specified, a transaction timeout causes InnoDB to abort and roll back the entire transaction.

Let’s conduct the test with the below scenarios:

  • Transaction with Innodb_rollback_on_timeout = OFF 
  • Transaction with Innodb_rollback_on_timeout = ON

Test Environment

I have created this table for testing purposes.

Below I share the steps which are common for testing both scenarios. 

Common Steps

  • Create two MySQL sessions (s1 and s2)
  • At s1, create the transaction and do the update “name = ‘sakthi’ where id=2”. Don’t commit the transaction.
  • At s2, create another transaction and do the update “name = ‘herc’ where id=3”. Don’t commit the transaction.
  • At s1, again do another update “name = ‘sakthi’ where id=3”. It will create the timeout error because the row (id=3 ) is already held and locked by s2.
  • At s1, commit the transaction once the second update got the timeout error. 
  • Verify the result and compare it with the actual data.

Scenario 1 – Transaction with Innodb_rollback_on_timeout = OFF

At s1,

At s2,

At s1,

Summary:

  • At s1, we had two UPDATEs in the transaction.
  • One update is completed and another update is failed.
  • After committing the transaction, you can see the modified data in the actual table for the completed update.
  • It illustrates, if innodb_rollback_on_timeout=OFF, the entire transaction will not rollback in case of the failures with timeout. It will apply the changes for the completed SQL’s.

Scenario 2 – Transaction with Innodb_rollback_on_timeout = ON

At s1,

At s2,

At s1,

Summary:

  • At s1, we had two UPDATEs in the transaction.
  • One update is completed and another update is failed
  • After committing the transaction, you can see there is no modification happening with the completed update.
  • It illustrates, if innodb_rollback_on_timeout=ON, the entire transaction will be rolled back in case of the failure happens.

Conclusion

Here I explicitly committed the transaction to explain the behavior of the parameter “innodb_rollback_on_timeout”. 

As a DBA, I would always suggest enabling the parameter “innodb_rollback_on_timeout”. Or, your application should be good enough to handle the failed transactions. Maybe retry the failed SQL or ROLLBACK the entire transaction when the driver gets the ERROR 1205 and retry the transaction, which is something that needs to be done regardless of the value of the innodb_rollback_on_timeout value to maintain the atomicity from MySQL level. Note, you need to restart your MySQL instance to change the threshold of this parameter.

Share this post

Comment (1)

  • Andreas (@iquito_ch) Reply

    Never heard of this option before, even though I fine-tuned my Innodb configuration quite a bit. Thanks for explaining it!

    July 28, 2020 at 6:22 am

Leave a Reply