CODEWITHSUNDEEP
sqltransactionsddlcreate-table
joeforker
joeforker

Reputation: 41777

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

Describe how each database handles transactions with DDL.

Upvotes: 135

Views: 109477

Answers (5)

PaulMest
PaulMest

Reputation: 15035

Looks like the other answers are pretty outdated.

As of 2019:

  • Postgres has supported transactional DDL for many releases.
  • SQLite has supported transactional DDL for many releases.
  • MySQL has supported Atomic DDL since 8.0 (which was released in 2018).

Upvotes: 6

Robert Sinclair
Robert Sinclair

Reputation: 5426

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

Upvotes: 8

joeforker
joeforker

Reputation: 41777

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'.

Upvotes: 184

araqnid
araqnid

Reputation: 133662

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.

Upvotes: 37

Dave Costa
Dave Costa

Reputation: 48121

While it is not strictly speaking a "rollback", in Oracle the FLASHBACK command can be used to undo these types of changes, if the database has been configured to support it.

Upvotes: 4

Related Questions