Is it possible to roll back CREATE TABLE and ALTER TABLE statements in major SQL databases?

SqlTransactionsDdlCreate Table

Sql Problem Overview

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.

Sql Solutions

Solution 1 - Sql

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

Solution 2 - Sql

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.

Solution 3 - Sql

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

Solution 4 - Sql

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

Solution 5 - Sql

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.

Categories
Recommended Sql Solutions
Recommended Transactions Solutions
Recommended Ddl Solutions
Recommended Create Table Solutions

Previous Solution:

Converting DateTime format using razor

asp.net Mvc-3Razor

Next Solution:

Formatting Phone Numbers in PHP

PhpPhone Number

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionjoeforkerView Question on Stackoverflow
Solution 1 - SqljoeforkerView Answer on Stackoverflow
Solution 2 - SqlaraqnidView Answer on Stackoverflow
Solution 3 - SqlRobert SinclairView Answer on Stackoverflow
Solution 4 - SqlPaulMestView Answer on Stackoverflow
Solution 5 - SqlDave CostaView Answer on Stackoverflow