128

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.

joeforker
  • 40,459
  • 37
  • 151
  • 246
  • Just to complement this thread, [H2](http://www.h2database.com/html/main.html) also doesn't support transactional DDL statements for most of SQL commands, according to [this](http://www.h2database.com/html/advanced.html#transaction_isolation). – Gabriel Paim Apr 09 '19 at 13:17

5 Answers5

172

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

marmarta
  • 838
  • 5
  • 20
joeforker
  • 40,459
  • 37
  • 151
  • 246
  • 9
    However, the default Python driver for sqlite prevents transactional SQL. http://bugs.python.org/issue10740 – joeforker Sep 17 '13 at 18:57
  • 2
    So the answer is "Yes, they can be rolled back, unless you are using MySQL or older versions of Oracle." – rjmunro Apr 24 '15 at 09:51
  • No, there are other SQL databases besides the ones listed. – joeforker Apr 30 '15 at 18:42
  • There are'nt any other "**major** SQL databases" :-) – rjmunro May 01 '15 at 10:34
  • 3
    There is an open issue in MariaDB for adding transactional DDL support: https://jira.mariadb.org/browse/MDEV-4259. Please vote for it. – Gili Apr 13 '17 at 15:34
  • 1
    The somewhat limited `ALTER TABLE` statement of SQLite is also able to be rolled back. It is not explicitly mentioned in the [documentation](https://sqlite.org/lang_altertable.html). What is mentioned there is how to perform "advanced" changes inside a transaction. – Thomas Jul 07 '17 at 14:41
  • H2: no ("most data definition language (DDL) statements, such as "create table", commit the current transaction") – Slava Semushin Oct 17 '18 at 10:26
  • Note: SQL Server does **not** _fully_ support transactional DDL: https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms191544(v=sql.105) – Leponzo Sep 09 '21 at 20:07
34

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.

araqnid
  • 127,052
  • 24
  • 157
  • 134
  • I'd argue that if I try to select from table `foo` while another transaction is dropping and recreating it, then I an OK with the old version or error. I am not OK with the new version, because it was not committed yet, so I must not see it. I am OK with an error, because in concurrent transactional access one has to be prepared to restart transactions anyway. If errors happen more often than necessary it might reduce performance, but it is still correct. – Jan Hudec Jun 04 '14 at 14:39
  • 1
    @JanHudec: you won't see an uncommitted version of the new table, only the result of the entire transaction that dropped/recreated it. i.e. a transaction that drops, recreates and repopulates a table is effectively atomic wrt other processes selecting from that table. (but everything will get blocked as soon as they even try to read the table's schema) – araqnid Jun 04 '14 at 16:08
8

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

Robert Sinclair
  • 4,550
  • 2
  • 44
  • 46
  • 1
    Unfortunately as of v10.5, MariaDB still doesn't support transactional DDL, see [this](https://mariadb.com/kb/en/start-transaction/#ddl-statements) for detail. – Ham Jun 11 '21 at 14:14
7

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).
PaulMest
  • 12,925
  • 7
  • 53
  • 50
  • 3
    One should note that the Atomic DDL in MySQL 8 refers to merely atomic DDL statements, but not transactional statements. A DDL statement, atomic or not, mostly still causes implicit commit and thus cannot be executed within another transaction (e.g. `START TRANSACTION ... COMMIT;`. So you still cannot roll back DDL statements in a transaction if latter one in the same transaction fails. (see note under https://dev.mysql.com/doc/refman/8.0/en/atomic-ddl.html#atomic-ddl-characteristics) – Lacek Dec 27 '19 at 15:21
4

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.

Dave Costa
  • 47,262
  • 8
  • 56
  • 72