3

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.

Improve this question
7
  • what made you to think that MySQL is not a ACID compliant ? – simplifiedDB Jun 29 '17 at 7:12
  • 1
    Pls click the link at last to see it. – Mannoj Jun 29 '17 at 7:25
  • 1
    @Mannoj did you test with other isolation levels or only repeatable read? – ypercubeᵀᴹ Jun 30 '17 at 11:42
  • Just read repeatable. – Mannoj Jun 30 '17 at 11:42
  • 1
    So test with serializable as well. – ypercubeᵀᴹ Jun 30 '17 at 11:43
3

No

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

See this blog entry for more information.

Improve this answer
3
  • There is a choice to go for read committed for users to pick. – Mannoj Jun 30 '17 at 1:17
  • @Mannoj Read committed is a step lower in guarantees, in read committed you see what other people commit. You don't have a snapshot that protects you from that. Repeatable read provides a snapshot, but in MySQL that snapshot isn't what you're writing. You're just reading from it. – Evan Carroll Jun 30 '17 at 1:30
  • 1
    Got it. I just read the blog you posted above. Indeed quite scary to get such a difference. I was testing isolations with selects but never know updates/deletes are reading for which it is not supposed to. Seems to be a terrible bug, if this was a feature in Mysql it has to be documented somewhere. I don't think I have seen such explicit comments anywhere. Marking your answer as right one. Whenever Mysql pushes its changes to fix this bug, I will revisit and update my question to YES it is ACID compliant. Until then Mysql stays as "Not an ACID Compliant". – Mannoj Jun 30 '17 at 7:56
8

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.

Improve this answer
0
1

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 :)

Improve this answer
4
  • Except that's not really Isolation in any sane way. You work off of a snapshot, and then when you commit regardless of what your snapshot has you can affect the rows inside of and outside of your snapshot, because they may have been modified. It's Isolation only in so far as what you see, not what's happening. – Evan Carroll Jun 29 '17 at 17:36
  • 1
    Consistency means things like check constraints are checked. AFAIK MySQL doesn't actually check these (unless things have changed in recent versions) – Martin Smith Jun 29 '17 at 18:33
  • To be fully compliant, the following should swap the values of the two columns: update t set x = y, y = x but it doesn't work correctly, even with InnoDB – a_horse_with_no_name Sep 12 '20 at 13:51
  • I think thats how Mysql works . During single table update it evaluate from left to right as mentioned here : dev.mysql.com/doc/refman/8.0/en/update.html and has been discussed previously also stackoverflow.com/questions/37649/… But same case is opposite in postgres. Value get swapped. Seems PG maintains old view but MySQL dont. But why a person should do multiple updates in just one SQL . it doesnt make practical sense to me. – simplifiedDB Sep 12 '20 at 21:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.