Take the 2-minute tour ×
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.

share|improve this question
3  
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 Heikens Jan 25 '11 at 12:07
    
Exactly, quite annoying IMO :) –  Joril Jan 25 '11 at 12:48
    
I'm personally surprised that Postgres allows it -- can you even roll back a DROP or a RENAME ? –  Joe Jan 25 '11 at 22:17
    
As long as you don't drop the whole database, yes :) See the link I just added –  Joril Jan 26 '11 at 8:27
    
So only Oracle and MySQL don't allow DDL statements within transactions. This feature really allows easy schema deploys. –  Marian Jan 26 '11 at 8:37
show 2 more comments

4 Answers

up vote 14 down vote accepted

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.

share|improve this answer
    
Well I'd prefer my DB in a consistent state all the time over ease-of-DB-version-upgrade, but I guess it's a matter of opinion :) Thanks for your explanation! –  Joril Jan 25 '11 at 15:30
    
This may explain the issue for some databases, but system catalogs are regular tables on Oracle. –  Leigh Riffel Dec 28 '12 at 15:30
add comment

Most don't? Bummer.

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 TRUNCATE can 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.

share|improve this answer
2  
Here's an answer that shows an example of transaction-bound DDL in SQL Server. Also, I always thought TRUNCATE couldn't be rolled back. I was wrong. –  Nick Chammas Oct 17 '11 at 15:16
add comment

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.

There's a similar question on SO: Is it possible to run multiple DDL statements inside a transaction (within SQL Server)?

share|improve this answer
add comment

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.

share|improve this answer
    
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! –  Gaius Jan 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. –  Gary Jan 26 '11 at 22:32
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.