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

Previously ,whenever we needed to create index or alter a heavy Innodb table’s definition in production , we always ended up with lot of time wastage and down time. This is because MySQL usually acquire exclusive lock on this table till alteration get completed and don’t permit other DML operation to execute on same table.Also while doing this mysql do “copying to tmp table” operation which is really lot of time consuming and night mare for all of us because it is not possible to execute same in production.

However , prior to MySQL 5.6 we have plugins to achieve same but a overhead to activate it.

With current version , we have capability to avoid “copy to tmp table” operation using arguments like ALGORITHM and we can avoid locking using LOCK=NONE.

So now we can use:

CREATE INDEX index_name ON table_name (column_name) ALGORITHM=IN PLACE LOCK=NONE;

Let me explain you. Today I have created a b tree index on innodb table with size on disk 15 GB and have record size 30 millions.

On simple alter or create index/drop index , index creation/drop was taking 18-20 minutes and also blocking other DML operation to execute .

After supplying above mentioned argument create index took 2 min 23 seconds and for drop , it took 0.11 seconds. It was a huge difference.

This is because only meta data of this table has been changed and is accessible by other DML operation too.

Let me know , if it is useful for you with time stats and cpu stats.

Regards

Ankit