MySQL not ACID compliant?

Sometimes, the question "Which FOSS DBMS to use for my application, MySQL or PostgreSQL?" arises in forum topics, mailing lists, blogs, etc. The answer is not as simple as the question itself and I won't try to answer it here. Instead, I will stand at an issue that arises from the answers given by various people.

It seems that there's a considerable percentage of developers out there that consider MySQL a RBDMS like system. You want transactions? Forget it! You want foreign keys? Forget it! Use PostgreSQL! Well, maybe these people can explain us why the heck MySQL is by far the most popular DBMS used in the web nowadays since it lacks these basic features.

These people might forget the fact that MySQL has a pluggable architecture that accepts a variety of storage engines that each has its own implementation of how to store and validate data. A basic MySQL installation comes with the MyISAM, InnoDB, BDB, memory, archive, etc. storage engines. For most generic use, the first two are mostly used (and that's why they're considered the most stable). When most people talk about MySQL, they have the equation MySQL=MyISAM in their minds, which is of course wrong. There's an option for the MySQL configuration file (my.cnf or my.ini) called default-storage-engine, that selects the default storage engine upon the creation of tables when there's no indication which storage engine to use. Simply because the default storage engine until now is MyISAM and the developers don't bother to define it while creating the database tables do not mean that MySQL is not ACID compliant. InnoDB is a more than fine storage engine, that it supports transactions, foreign keys, row level locking and multi versioning concurrency control besides other. I think that the upcoming MySQL 5.5 series will make InnoDB the default storage engine. Maybe then it will be the time that people actually realize that MySQL is more than just a RDBMS like system.

This is not a MySQL biased point of view. Of course MySQL has its weaknesses, regardless of the underlying storage engines, since there are features that must be implemented in the server portion and the fact is that each newer version brings more and more features on the table. On the other hand, PostgreSQL is known to be an excellent alternative. I haven't personally used it, however I plan to "play" with it in the near future.

υπολογιστές / computers: 
Χρήσιμο / Useful: 
No votes yet
Ψηφίστε για την ποιότητα του άρθρου / Vote for the article's quality

Σχόλια

Η MySQL υποστηρίζει  συναλλαγές, ξένα κλειδιά, αναφορική  ακεραιότητα, κλπ..

Απλώς για ένα αρκετά μεγάλο διάστημα δεν χρησιμοποιούσαν την κατάλληλη μηχανή ως προεπιλεγμένη.

Οποιοσδήποτε ήθελε να κάνει χρήση των παραπάνω δυνατοτήτων φθάνει να δήλωνε στο σχήμα της σχέσεων της βάσης τη μηχανή InnoDB.

Οπότε θεωρώ πως το άρθρο ξεκινάει λάθος λέγοντας πως:

"You want transactions? Forget it! You want foreign keys? Forget it! Use PostgreSQL!"

Φαίνεται περισσότερο προπαγανδιστικό παρά επιστημονικό (δηλαδή αντικειμενικό).

Σίγουρα η PosgreSQL είναι ένα σύστημα πιο πλήρες (αντικειμενοσχεσιακό) και αξίζει να το χρησιμοποιήσει κάποιος.

Όχι όμως για τους λόγους που αναφέρθηκαν..

Φιλικά.

Ευστάθιε,

Καταρχήν σ' ευχαριστώ για το σχόλιό σου. Όσον αφορά αυτά που λες, συμφωνώ 100%. Απλά μάλλον δεν κατάλαβες ότι αυτά που έγραφα δεν ήταν λεγόμενα δικά μου, αλλά ανθρώπων που δε θεωρούσαν τη MySQL ως σωστό εργαλείο (DBMS) για τη δουλειά. Αυτά που γράφω έχουν κι ένα βαθμό ειρωνείας, που δείχνει την (εσκεμμένη;) άγνοια των παραπάνω ανθρώπων. Δουλεύω MySQL εδώ και χρόνια και δεν έχω κανένα παράπονο για τις δουλειές που την χρησιμοποιώ. Χθες εγκατέστησα και την PostgreSQL, απλά για να παίξω λίγο και με αυτό το DBMS και να δω τις δυνατότητές του.

Να είσαι καλά.

The reason MySQL is the most popular is because it was taught first and spread fast, not because the tool is relevant.
InnoDB is NOT ACID COMPLIANT, subqueries and joins are often very slow in MySQL, triggers / foreign keys aren't really enforced, etc. etc.
There is today, with versions 5.5.x, still a lot of difference between a serious dbms (i.e. mssql, postgresql) and mysql (myToydb or InnoDB).
MySQL is absolutely not on par with MSSQL or Orcl or ... PostgreSQL IS. and it doesn't take much DBA experience to feel it.
To anyone who still thinks MySQL is an "ok solution", please learn a bit more SQL, try PostgreSQL or even MSSQL express, you'll see how much is missing in there.