Is Mysql ACID Compliant?

Question

Is Mysql not an ACID compliant according to Postgresql? In some blogs I see Mysql is not ACID compliant. How true is that?

Let't not consider the replication here, lets consider a standalone and how efficient is Mysql ACID?

In my understanding for Mysql-ACID.

A - Atomicity (Set of transactions should all be committed if one fails it has to rollback. Yes means all are committed , no means even one failed it has to Rollback).

I.E. Features that supports in Mysql are.

• start Transaction; ..... commit ;
• auto_commit=1;

C - Consistency.

( PK,FK,UK,NOT-NULL). It adheres to Relations and constraints for Databases. Instance a parent key can be deleted only when its child key is removed.

I - Isolation. Isolation between users and their state of commit.

Read Repeatable Read Uncommitted Read Committed Serialized

D - Durability. At the event of DB crash innodb recovers the DB by applying committed transaction from iblog file and discards not-committed transaction.

Click here for the source of this question. - Is it because the blog is created @2001?

UPDATE Jun-30-2017: As per "Evan Carroll" response and I have personally tested the blog experiment on 5.7.18-enterprise. The results obtained from the experiment seems to be Mysql is Not an ACID Compliant.

Answer 1

No

I don't think permitting Phantom Writes in Repeatable Read satisfies any ACID compliance.

See this blog entry for more information.

Answer 2

If you use InnoDB or a similar storage engine then it should be ACID compliant (ref: https://en.wikipedia.org/wiki/InnoDB). myISAM, the old default and still very commonly used, most definitely isn't ACID compliant. If you mix the two (you might find simpler table types perform better and are acceptable for volatile data that can and will be reproduced again, such as staging tables for ETL processes) then you solution will not be entirely compliant.

A large caveat with ACID compliance is that for performance reasons most databases use an isolation level that does not guarantee the "I" part - this is within the ANSI SQL specs. To offer proper Isolation you need to guarantee that transactions are serialisable, an isolation level that some DBs don't even support. For example MySQL+InnoDB defaults to "repeatable read", while MS SQL Server defaults to the slightly more strict "read committed", both offer "serialisable" but it is not the default. Why isn't is always supported and usually not the default? Performance: a full isolation requirement can significantly limit concurrency.

There are a few good articles on the subject. For one example http://www.bailis.org/blog/when-is-acid-acid-rarely/ is a short and informative place to start with some interesting discussion in the comments.

Answer 3

There is lot of debate on this whether MySQL really and completely follow ACID properties , and everyone have their own opinion . As per MySQL doc https://dev.mysql.com/doc/refman/5.6/en/mysql-acid.html

MySQL with Innodb engines closely follow ACID property .But that is their view .

But we should not forget that with the newly versions , MySQL has improved a lot and i personally appreciate Oracle that they are optimizing it in a very good way. So previous version may not follow ACID , but today it is.

However , 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 the complete transaction. As per my experience Innodb engines do rollback whenever any crash occurs in mid of transaction .It handles by storing the results of transactional statements (the dirty pages or modified rows) in a double write buffer (if enable) or redo-logs or binary logs (if enable) and writing these results back to disk irrespective that the transaction should be in prepared state. If it is not then transaction will be rolled back. Just kill your mysqld and then restart it and observe your error log file.

2. Consistency also followed in Innodb as there are various logging mechanism (buffers) which record all changes happening to your database and help us to ensure that no in-consistency will occur.

3. Isolation : 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.It handles by storing the results of transactional statements (the modified rows) in a memory buffer and writing these results back to disk and to the binary log from the buffer only once the transaction is committed.

4. Durability : As per MySQL DOC he 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 example binary log ensure durability in case of any failure.

Let me know , if this help you :)