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.

  • 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. Jan 25, 2011 at 12:07
  • Exactly, quite annoying IMO :)
    – Joril
    Jan 25, 2011 at 12:48
  • I'm personally surprised that Postgres allows it -- can you even roll back a DROP or a RENAME ?
    – Joe
    Jan 25, 2011 at 22:17
  • 1
    As long as you don't drop the whole database, yes :) See the link I just added
    – Joril
    Jan 26, 2011 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, 2011 at 8:37

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.

  • 2
    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, 2011 at 15:30
  • This may explain the issue for some databases, but system catalogs are regular tables on Oracle. Dec 28, 2012 at 15:30

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.

  • 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. Oct 17, 2011 at 15:16

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)?


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!
    – Gaius
    Jan 25, 2011 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, 2011 at 22:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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