MySQL – Is It ACID Compliant?

acidMySQLpostgresql

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.

Best Answer

No

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

See this blog entry for more information.

Related Solutions

Postgresql – Read Committed Isolation Level

The difference lies between a query and a transaction. A transaction can contain any number of queries. To illustrate the difference, I set up a small example:

CREATE TABLE table_to_be_updated (
    id serial PRIMARY KEY,
    other_column text,
    column_changing text
);

INSERT INTO table_to_be_updated (other_column, column_changing)
VALUES
('value', 'old_value'),
('value', 'other_value'),
('nonvalue', 'doesnt matter');

Then run two transactions concurrently (issuing the commands one by one, the middle line wants to depict the timeline):

                                | <-- BEGIN;
                                |
                                |
                                |     UPDATE table_to_be_updated
BEGIN; -----------------------> |     SET column_changing = 'new_value'
                                |     WHERE
                                |         other_column = 'value' AND
                                |         column_changing = 'old_value';
                                |
                                |
SELECT column_changing -------> |     -- update not yet committed
FROM table_to_be_updated        |
WHERE other_column = 'value';   | <-- COMMIT;
                                |
                                |
SELECT column_changing -------> |
FROM table_to_be_updated        |
WHERE other_column = 'value';   |
                                |
                                |
COMMIT; ----------------------> |

Running these in READ COMMITTED isolation level, the first query returns a row with 'old_value', while the second one shows a row with 'new_value'. On an other run, I change the left-hand-side transaction isolation level:

SET transaction ISOLATION LEVEL REPEATABLE READ;

(The command must be the first statement in a transaction.) Now both SELECTs return the same rowset, while a third one after committing both transactions will show the new row.

MySQL: Why statement-based binlog format cannot work with Innodb READ UNCOMMITTED or READ COMMITTED Isolation levels

The semantics of those isolation modes cannot be guaranteed in SBR since the Slave is unlikely to execute the statements of multiple threads in the same order.

More specifically, from the Change Log:

----- 2008-11-14 5.1.30 General Availability -- Bugs Fixed -- -----

With statement-based binary logging format and a transaction isolation level of READ COMMITTED or stricter, InnoDB printed an error because statement-based logging might lead to inconsistency between master and slave databases. However, this error was printed even when binary logging was not enabled (in which case, no such inconsistency can occur). Bug #40360

Related Question