Is your InnoDB transactions are completely Atomicity ( ACID ) by default ?

The blog title seems something crazy ?

Yes, by default your InnoDB transactions don’t have the Atomicity . But, you can control the atomicity of your InnoDB transaction with the variable innodb_rollback_on_timeout. We had a weird data inconsistency issue within the transaction . In this blog , I am going to explain “How it will happen?” and “How it can be fixed ?” .

Whats is Atomicity ?

From Wiki ,

Transactions are often composed of multiple statements. Atomicity guarantees that each transaction is treated as a single “unit”, which either succeeds completely, or fails completely: if any of the statements constituting a transaction fails to complete, the entire transaction fails and the database is left unchanged.

What is Innodb_rollback_on_timeout ?

From MySQL documentation,

InnoDB rolls back only the last statement on a transaction timeout by default. If –innodb-rollback-on-timeout is specified, a transaction timeout causes InnoDB to abort and roll back the entire transaction.

Here the transaction timeout will be happen based on the “innodb_lock_wait_timeout” setting .

All good, now we can experiment how it is working practically .

Experiment 1 ( Innodb_rollback_on_timeout = OFF )

I have created two MySQL client sessions ,

session 1 :

• innodb_rollback_on_timeout=OFF
• innodb_wait_timeout=50
• Started a transaction
• Ran the UPDATE to set name=’christ’ where id=1 ( not COMMIT )

now I am going to lock the second row ( id=2 ) at different session ( session 2 )

session 2 :

• Started the transaction at session 2
• Ran the UPDATE to set name=’sri’ where id=2 ( not COMMIT )

Now, I am going to update the same id = 2 at session 1 , so that it will exceed the innodb_lock_wait_timeout and the statement will fail .

session 1 :

• Trying to access the row ( id=2 ) through the UPDATE
• The UPDATE was failed with lock wait timeout because the session 2 is already hold that row .
• Triggered the COMMIT

But after commit the transaction,

My first UPDATE was committed even the second statement was failed . You can see the output from the screenshot .

Sounds like ATOMICITY is missing ?

Lets do the second experiment …

Experiment 2 ( Innodb_rollback_on_timeout = ON ) :

I am going to repeat the same procedure with innodb_rollback_on_timeout=ON,

session 1 :

started the transaction and updated the row id=1

session 2 :

locking the row id=2

session 1 :

Creating the lock wait timeout inside the transaction and committing the transaction .

No changes happened . Make sense ?

With innodb_rollback_on_timeout = ON , Both the statements were not committed and the complete transaction has rolled back . THE ENTIRE TRANSACTION HAS ROLLBACK . So, this is the exact Atomicity right ?

Note : Changing the innodb_rollback_on_timeout requires the MySQL restart .

Hope this blog helps to understand the variable innodb_rollback_on_timeout . The blog proves, Having the innodb_rollback_on_timeout = ON provides the perfect/complete atomicity on your InnoDB system .

Thanks !!!