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 !!!

4 thoughts on “Is your InnoDB transactions are completely Atomicity ( ACID ) by default ?

  1. Your application should retry the update or roll back. InnoDB is not losing atomicity. You commit a transaction with changes and that os just a bug in your app. Note that InnoDB always rolls back whole transactions on deadlock. Your application has to handle failed statements.

    Liked by 1 person

    1. Hi Justin,

      Thanks for your comment . Our applications has the proper error handling mechanism to handle the failures .

      I had the issue, when I tested the variable innodb_rollback_on_timeout with different threshold ( ON and OFF ). The complete flow was documented in this blog . And, there is no deadlock happens , it is a LOCK WAIT TIMEOUT .

      Even the document says “InnoDB rolls back only the last statement on a transaction timeout by default.”

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create your website at WordPress.com
Get started
%d bloggers like this: