I understand it’s a bit of an old topic but people still have doubts. However, I have published an answer on this topic here, but now I feel to make my answer more connected with people with more improved thought.

There is already a lot of debate on this whether MySQL completely follows ACID properties, and everyone has their own opinion. MySQL docs also don’t convey complete information except for the glossary here.

MySQL with InnoDB engines closely follows the ACID property.

Let me try to put all properties one by one how MySQL with Innodb follows these properties :

1. Atomicity says that either rollback or commit in case any part of a transaction fails. As per my experience, InnoDB engines do crash recovery in case of OS reboot, power failure, etc or it rolls back whenever any crash occurs in mid of transaction. So in case, if any uncommitted transaction got failed due to any reason, I.e. not flushed (reached) to disk yet, InnoDB roll back the transaction . Hence I can say it is ATOMIC. Obviously, the redo log plays an important role here in re-executing the failed transactions.
2. Consistency says that the data must always remain in a consistent state. DB must behave as per requirement. In simpler words only required data is allowed to insert and must complete. Also, the various constraints must be followed and should not get changed during the course of a transaction. InnoDB has all the constraints like Foreign keys, primary key etc. and doesn’t allow unauthorized values to be inserted.
3. Isolation says that integrity should be maintained and data should not come in inconsistency due to concurrent transaction. .Innodb provides several row level locking which avoids (if properly handled) any other process to acquire lock on resource which is already in use by other process. Innodb follow all isolation i.e. serialized, repeatable read,read committed and read uncommitted. So we can set it as per our requirement.
4. Durability : As per MySQL DOC the durability aspect of the ACID model involves MySQL software features interacting with your particular hardware configuration. Because of the many possibilities depending on the capabilities of your CPU, network, and storage devices, this aspect is the most complicated to provide concrete guidelines for. (And those guidelines might take the form of buy “new hardware”.) . For more clarification, innodb maintains double write buffers which ensures that in case of crash , data will not be lost and can be recovered from it after DB comes up.

Hope this help. Post your comment for any queries.

About Ankit Kapoor

Ankit Kapoor is a database geek and enjoy playing with open source databases like MySQL and PG.
Connect with me on linkedin