Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Recently I found out that MySQL doesn't support rollback of DDL such as "alter table"... Being used to PostgreSQL, that struck me as odd, but a friend of mine told me that even Oracle doesn't allow it.. Are there technical reasons for not supporting it? Is it simply an "uninteresting" feature for them?

Edit: just found this comparison. It looks like there are many DBMSes that do support transactional DDL.

MySQL doesn't allow DDL inside a transaction. Before executing the DDL statements, it commits the current transaction (all current DML statements inside this transaction!) without any warning.
–
Frank HeikensJan 25 '11 at 12:07

4 Answers
4

The reason why this works in PostgreSQL is that the system catalogs are regular tables. So creating a new function, for example, just requires inserting a row into the pg_proc table, changing the default value of a column just requires making an update to some row in pg_attrdef, and so on. Since tables are transactional anyway, you'd almost have to go out of your way not to have it work that way. (Lots of painful implementation details omitted here. ;-) )

I suppose, not knowing the source code, that other database engines use some custom internal structures to represent their system catalog information. And so they'd have to put in extra effort, a lot of extra effort likely, to make transactional DDL work, and it's apparently not a priority for them.

The flip side of this is that this is the reason why major version upgrades of PostgreSQL are so painful. Other products can presumably design their internal metadata structures with changes and updates in mind, and so there are no issues with upgrading to a new major version. In PostgreSQL, there is no way to change a system catalog table to suddenly look like a newer version of a system catalog table, at least not while keeping the system online, because that would require access to the system catalogs. Urgh.

I principally use SQL Server and it does. I know Oracle doesn't but I thought Oracle might be an aberration.

In SQL Server, I'm quite certain you can run multiple DDL statements in a single transaction although I also think there's a couple of restrictions (which I have all forgotten). You can do a create or an alter or a drop of most things and roll it back, if you like. Red-Gate SQL Compare (a tool I love) takes advantage of this.

The problem with doing this is that your transaction scope becomes fairly interesting... When you involve the system catalogs in an update transaction (DDL), you run the risk of taking some really important locks and you may block access to the system catalogs. Users can't do much if their queries can't find their tables in the catalogs!

On balance, though, it's handy to be able to include DDL in a multi-statement transaction.

More usefully, the SQL Server DDL command TRUNCATEcan also be an element of a multi-statement transaction. You can truncate a target table (very fast), build it, and then do a commit if you like the result. If something goes wrong, you rollback and voila!, it's like you never disturbed the table. Log space is also minimized. I take advantage of that fairly often.

In SQL Server we can rollback DDL statements, it's not using auto commit at the end of the statement. In other DBMS I don't know, but I remember that in Oracle one can't do the same.
I believe it's specific to each DBMS, not sure what would the SQL standard say about this, but I'm sure no producer implements 100% the standard.

Oracle has shared query parsing, so a SELECT * FROM table_a done by one session is (normally) the same as that of another session. That would break if one session thought there was ten columns in the table and another thought there were eleven.

Interesting you should say that, I had a similar kind of issue the other day, the application was supposed to be "hot" deployable, but changing the table structure for the new version, it had no way to recompile JDBC PreparedStatements apart from restarting it, so much for that!
–
GaiusJan 25 '11 at 21:37

2

As an aside, the 11gR2 version introduces the concept of Editions to assist with hot upgrades. Effectively existing connections use one edition (with five columns). You start a new edition, add a column and start some new connections using the new edition for new sessions. Once all outstanding sessions are finished, the old edition is cruft and everything uses the new edition. No rollback, but you don't put new activity on your new edition until its all working.
–
GaryJan 26 '11 at 22:32