dinsdag 6 januari 2015

Why I like PostgrteSQL more than MySQL: transactions and magic commit.

MySQL's BEGIN command is actually a COMMIT;BEGIN; which means that you can commit a transaction without ever executing a COMMIT. That by itself is... well... not very good, but the problems don't end there.

The only reason why you use transactions is so that you can revert the data when you notice that you cannot complete the work. So, you'd expect the database to do a rollback whenever it encounters a problem through which it cannot complete the transaction.

MySQL does not support DDL inside transactions. You cannot create or drop tables for example. That by itself is not a problem, it's a complex piece of MVCC that is not easily implemented. But, instead of aborting the transaction and doing a rollback whenever the user does something that breaks the transaction, MySQL will actuallt commit the transaction. Yes, commit. So, you begin work, you do some things that work, you do something that cannot be handled, and suddenly your half-completed work is committed.

Tell me, in which universe is that a good idea?

And don't give me the "users wanted it this way" excuse, nobody wants a commit when there is an error, and if they did, people which database knowledge, which I thought MySQL's developers would have, should tell them no, and implement a rollback instead.

PostgreSQL will actually force an abort on error, you cannot even choose to continue after an error. PostgreSQL also supports DDL in MVCC, which is extremely handy when doing migrations and unittests.