Hacker News Reader

Comment by lcampbell | Hacker News Reader

> MySQL provides ACID transactions on a single machine

That made me chortle. If you can't roll back an `ALTER TABLE` command (e.g., to back out of a failed schema migration), you don't really have good ACID semantics. Here's a list of other fun MySQL commands that sidestep transactions: http://dev.mysql.com/doc/refman/5.5/en/implicit-commit.html

View on HN

MySQL isn't the only engine that does this.

http://docs.oracle.com/cd/E17952_01/refman-5.1-en/implicit-c...

Edit - I was wrong!

Not only did I link to the wrong docs, but Oracle fixed what I considered to be a longstanding bug.

Thanks to those who pointed it out.

For a very, very long time, Oracle has enforced implicit transaction boundaries around DDL, and it is about time that finally changed.

Those docs look like they're MySQL docs, not Oracle Database docs. Stackoverflow suggests[1] that as-of Oracle Database 11g Release 2, DDL changes are isolated within the current transaction.

[1] http://stackoverflow.com/questions/4692690/is-it-possible-to...

That link is to a page from the MySQL 5.1 reference manual.

As of about 4 years ago Oracle's been the owners of MySQL, so that's the reason why you're seeing it hosted at docs.oracle.com.

ACID refers to actions that can take place within a transaction. Transactions refer to logical operation to the data. The data is the content, not de container. DML affects content, DDL affects the container. ALTER TABLE is a DDL operation.

Now, I know that _some_ databases offer some level of support to include DLL operations within a transaction, but its far from being common, and most of the implementations are quite recent.

Not even just alter table, it's all DDL, I believe.

This has a way of biting one in the ass in the worst possible moment, during a migration.

The property you are looking for - that every SQL command can take place in a transaction - is surely a very desirable property for a SQL database to have, but from my perspective a database can provide genuine ACID transactions even if it only supports a subset of read and write operations within them.

Actually, I think we are giving too much credit to Oracle! There are (relatively subtle, but real) concurrency anomalies in their so-called "serializable" isolation level.

Transactional DDL is a dangerous game with any DBMS. You must know the idioms of the systems you use, and use them accordingly. You would be a fool otherwise.

"Transactional DDL is a dangerous game with any DBMS."

Can you give an example?

I think that's exactly why we must have correct ACID transaction around DDL…

Isn't ALTER TABLE a fundamentally slow and blocking action anyway? It's easy enough to make a new table and swap it in. Transactions for row-level actions are far more important.

The ability to do a schema migration consisting of several changes as an all-or-nothing, all-at-once change would be useful even if they can't figure out how to do it concurrently with other operations on the tables.

It shouldn't require too crazy of an implementation to allow reads concurrent with schema modification (seeing the old version of the world) and it's not impossible to allow concurrent, transactional writes too.

It happens to be a slow and blocking action on MySQL, yes. That's yet another problem with MySQL. You can only make a new table and swap it in if you can stop writing to your old table, or don't care if records go missing. I've lost weeks trying increasingly convoluted methods to get an online schema migration safely in MySQL, with no satisfying conclusion. You can sometimes get away with pt-online-schema-change's method of adding triggers to copy new rows while copying over the existing table contents, but that failed pretty badly for us because of collisions with the innodb gap lock on the table, so we ended up having to do multiple passes, populating the new table once without the live updates, then copying it over again while the live updates were copied over with triggers. It was a mess.

I think I expressed myself poorly. Undoubtedly it would be good if it did the on-disk data changes in the background, and a schema update seemed like an instant atomic action. But it's still a sequence point, and I see no way that transactions after it could complete while the schema update is uncommitted. So what do you actually gain by being able to do the schema update in a transaction?

The original comment was about being able to roll back, not the ability to run things in parallel. With PostgreSQL, I can start a transaction in which I'm going to perform five separate "alter table" commands; if one of these fails (maybe because I change the type of a field in a way that is incompatible with some of the data, or maybe because this is some kind of "migration" that came with a project and it has a bug: maybe it tries to rename a column, but there's a typo in the name) the entire migration will roll back atomically.

Yeah, I'm just saying that you can work around that pretty easily. There are more important things than rolling back schemas that mysql lacks. And if it got those things, schema updates would be pretty easy even without the ability to roll them back. Not extremely convenient, but convenient enough.

Schema transactions are not nearly as useful/important as data transactions.

Reply on news.ycombinator.com