5 ms·

> 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 sch

> 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 http://dev.mysql.com/doc/refman/5.5/en/implicit-commit.html

gizmogwai 13y ago
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.
mathnode 13y ago
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.
- rpedela 13y ago
  "Transactional DDL is a dangerous game with any DBMS." Can you give an example?
- eonil 13y ago
  I think that's exactly why we must have correct ACID transaction around DDL…
Dylan16807 13y ago
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.
- bcoates 13y ago
  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.
- tene 13y ago
  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.
  - Dylan16807 13y ago
    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?
    - saurik 13y ago
      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.
voidmain 13y ago
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.
makmanalp 13y ago
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.
jpitz 13y ago
MySQL isn't the only engine that does this. http://docs.oracle.com/cd/E17952_01/refman-5.1-en/implicit-commit.html 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.
- lcampbell 13y ago
  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-roll-back-create-table-and-alter-table-statements-in-major-sql http://stackoverflow.com/questions/4692690/is-it-possible-to...
- bunderbunder 13y ago
  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.

gizmogwai 13y ago

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.

mathnode 13y ago

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.

rpedela 13y ago
"Transactional DDL is a dangerous game with any DBMS." Can you give an example?
eonil 13y ago
I think that's exactly why we must have correct ACID transaction around DDL…

Dylan16807 13y ago

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.

bcoates 13y ago
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.
tene 13y ago
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.
- Dylan16807 13y ago
  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?
  - saurik 13y ago
    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.

voidmain 13y ago

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.

makmanalp 13y ago

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.

jpitz 13y ago

MySQL isn't the only engine that does this. http://docs.oracle.com/cd/E17952_01/refman-5.1-en/implicit-commit.html 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.

lcampbell 13y ago
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-roll-back-create-table-and-alter-table-statements-in-major-sql http://stackoverflow.com/questions/4692690/is-it-possible-to...
bunderbunder 13y ago
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.