Downloads

Blog

The Transaction Behavior Impact of innodb_rollback_on_timeout in MySQL

July 27, 2020

Author

Sri Sakthivel

Benchmarks

Insight for DBAs

Insight for Developers

Share this Post:

innodb_rollback_on_timeout I 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.

mysql> select * from rollback_on;<br>+----+-----------------+---------------------+<br>| id | name            | c_date              |<br>+----+-----------------+---------------------+<br>|  1 | jc              | 2020-07-23 00:44:09 |<br>|  2 | sri             | 2020-07-23 00:44:09 |<br>|  3 | hercules7sakthi | 2020-07-23 00:44:09 |<br>+----+-----------------+---------------------+<br>3 rows in set (0.01 sec)

mysql> select * from rollback_on; +----+-----------------+---------------------+ | id | name | c_date | +----+-----------------+---------------------+ | 1 | jc | 2020-07-23 00:44:09 | | 2 | sri | 2020-07-23 00:44:09 | | 3 | hercules7sakthi | 2020-07-23 00:44:09 | +----+-----------------+---------------------+ 3 rows in set (0.01 sec)

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

#my.cnf<br>innodb_rollback_on_timeout = OFF<br>innodb_lock_wait_timeout = 10

1	#my.cnf<br>innodb_rollback_on_timeout = OFF<br>innodb_lock_wait_timeout = 10

At s1,

mysql> begin;<br>Query OK, 0 rows affected (0.03 sec)<br><br>mysql> update rollback_on set name='sakthi' where id=2;<br>Query OK, 1 row affected (0.10 sec)<br>Rows matched: 1  Changed: 1  Warnings: 0

1	mysql> begin;<br>Query OK, 0 rows affected (0.03 sec)<br><br>mysql> update rollback_on set name='sakthi' where id=2;<br>Query OK, 1 row affected (0.10 sec)<br>Rows matched: 1 Changed: 1 Warnings: 0

At s2,

mysql> begin;<br>Query OK, 0 rows affected (0.00 sec)<br><br>mysql> update rollback_on set name='herc' where id=3;<br>Query OK, 1 row affected (0.07 sec)<br>Rows matched: 1  Changed: 1  Warnings: 0

1	mysql> begin;<br>Query OK, 0 rows affected (0.00 sec)<br><br>mysql> update rollback_on set name='herc' where id=3;<br>Query OK, 1 row affected (0.07 sec)<br>Rows matched: 1 Changed: 1 Warnings: 0

At s1,

mysql> begin;<br>Query OK, 0 rows affected (0.03 sec)<br><br>mysql> update rollback_on set name='sakthi' where id=2;<br>Query OK, 1 row affected (0.10 sec)<br>Rows matched: 1  Changed: 1  Warnings: 0<br><br>mysql> update rollback_on set name='sakthi' where id=3;<br>ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction <br><br>mysql> commit;<br>Query OK, 0 rows affected (0.05 sec)<br><br>mysql> select * from rollback_on;<br>+----+-----------------+---------------------+<br>| id | name            | c_date              |<br>+----+-----------------+---------------------+<br>|  1 | jc              | 2020-06-21 18:28:03 |<br>|  2 | sakthi          | 2020-06-21 18:28:03 |<br>|  3 | hercules7sakthi | 2020-06-21 18:28:03 |<br>+----+-----------------+---------------------+<br>3 rows in set (0.00 sec)

mysql> begin; Query OK, 0 rows affected (0.03 sec) mysql> update rollback_on set name='sakthi' where id=2; Query OK, 1 row affected (0.10 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update rollback_on set name='sakthi' where id=3; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> commit; Query OK, 0 rows affected (0.05 sec) mysql> select * from rollback_on; +----+-----------------+---------------------+ | id | name | c_date | +----+-----------------+---------------------+ | 1 | jc | 2020-06-21 18:28:03 | | 2 | sakthi | 2020-06-21 18:28:03 | | 3 | hercules7sakthi | 2020-06-21 18:28:03 | +----+-----------------+---------------------+ 3 rows in set (0.00 sec)

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

#my.cnf<br>innodb_rollback_on_timeout = ON<br>innodb_lock_wait_timeout = 10

1	#my.cnf<br>innodb_rollback_on_timeout = ON<br>innodb_lock_wait_timeout = 10

At s1,

mysql> begin;<br>Query OK, 0 rows affected (0.03 sec)<br><br>mysql> update rollback_on set name='sakthi' where id=2;<br>Query OK, 1 row affected (0.10 sec)<br>Rows matched: 1  Changed: 1  Warnings: 0

1	mysql> begin;<br>Query OK, 0 rows affected (0.03 sec)<br><br>mysql> update rollback_on set name='sakthi' where id=2;<br>Query OK, 1 row affected (0.10 sec)<br>Rows matched: 1 Changed: 1 Warnings: 0

At s2,

mysql> begin;<br>Query OK, 0 rows affected (0.00 sec)<br><br>mysql> update rollback_on set name='herc' where id=3;<br>Query OK, 1 row affected (0.07 sec)<br>Rows matched: 1  Changed: 1  Warnings: 0

1	mysql> begin;<br>Query OK, 0 rows affected (0.00 sec)<br><br>mysql> update rollback_on set name='herc' where id=3;<br>Query OK, 1 row affected (0.07 sec)<br>Rows matched: 1 Changed: 1 Warnings: 0

At s1,

mysql> begin;<br>Query OK, 0 rows affected (0.03 sec)<br><br>mysql> update rollback_on set name='sakthi' where id=2;<br>Query OK, 1 row affected (0.10 sec)<br>Rows matched: 1  Changed: 1  Warnings: 0<br><br>mysql> update rollback_on set name='sakthi' where id=3;<br>ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction <br><br>mysql> commit;<br>Query OK, 0 rows affected (0.05 sec)<br><br>mysql> select * from rollback_on;<br>+----+-----------------+---------------------+<br>| id | name            | c_date              |<br>+----+-----------------+---------------------+<br>|  1 | jc              | 2020-06-21 18:28:03 |<br>|  2 | sri             | 2020-06-21 18:28:03 |<br>|  3 | hercules7sakthi | 2020-06-21 18:28:03 |<br>+----+-----------------+---------------------+<br>3 rows in set (0.00 sec)

mysql> begin; Query OK, 0 rows affected (0.03 sec) mysql> update rollback_on set name='sakthi' where id=2; Query OK, 1 row affected (0.10 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update rollback_on set name='sakthi' where id=3; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> commit; Query OK, 0 rows affected (0.05 sec) mysql> select * from rollback_on; +----+-----------------+---------------------+ | id | name | c_date | +----+-----------------+---------------------+ | 1 | jc | 2020-06-21 18:28:03 | | 2 | sri | 2020-06-21 18:28:03 | | 3 | hercules7sakthi | 2020-06-21 18:28:03 | +----+-----------------+---------------------+ 3 rows in set (0.00 sec)

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.