Is it possible to roll back CREATE TABLE and ALTER TABLE statements in major SQL databases?
I am working on a program that issues DDL. I would like to know whether CREATE TABLE and similar DDL can be rolled back in
- Postgres
- MySQL
- SQLite
- et al
Describe how each database handles transactions with DDL.
http://wiki.postgresql.org/wiki/Transactional_DDL_in_PostgreSQL:_A_Competitive_Analysis provides an overview of this issue from PostgreSQL's perspective.
Is DDL transactional according to this document?
- PostgreSQL - yes
- MySQL - no; DDL causes an implicit commit
- Oracle Database 11g Release 2 and above - by default, no, but an alternative called edition-based redefinition exists
- Older versions of Oracle - no; DDL causes an implicit commit
- SQL Server - yes
- Sybase Adaptive Server - yes
- DB2 - yes
- Informix - yes
- Firebird (Interbase) - yes
SQLite also appears to have transactional DDL as well. I was able to ROLLBACK a CREATE TABLE statement in SQLite. Its CREATE TABLE documentation does not mention any special transactional 'gotchas'.
PostgreSQL has transactional DDL for most database objects (certainly tables, indices etc but not databases, users). However practically any DDL will get an ACCESS EXCLUSIVE lock on the target object, making it completely inaccessible until the DDL transaction finishes. Also, not all situations are quite handled- for example, if you try to select from table foo while another transaction is dropping it and creating a replacement table foo, then the blocked transaction will finally receive an error rather than finding the new foo table. (Edit: this was fixed in or before PostgreSQL 9.3)
CREATE INDEX ... CONCURRENTLY is exceptional, it uses three transactions to add an index to a table while allowing concurrent updates, so it cannot itself be performed in a transaction.
Also the database maintenance command VACUUM cannot be used in a transaction.
Can't be done with MySQL it seems, very dumb, but true... (as per the accepted answer)
"The CREATE TABLE statement in InnoDB is processed as a single transaction. This means that a ROLLBACK from the user does not undo CREATE TABLE statements the user made during that transaction."
https://dev.mysql.com/doc/refman/5.7/en/implicit-commit.html
Tried a few different ways and it simply won't roll back..
Work around is to simply set a failure flag and do "drop table tblname" if one of the queries failed..