4

I've been taught that in RDBMS any DDL always causes an implicit commit, ending any active transaction at the time the DDL is issued.

Now I found this page on the PostgreSQL wiki that states that PostgreSQL is able to perform DDL statements in a transaction, making them atomic and rollback-able.

But does that change anything with respect to the implicit commit about the DDL? Is it still executed when the DDL is issued?

watery
  • 386
  • 3
  • 17
  • 1
    There are many DBMS that support transactional DDL: Postgres, SQL Server, DB2, Firebird, SQLite, Ingres, Informix, Teradata and NuoDB are the ones where I know it for certain. And Postgres certainly does not do an implicit commit when you run DDL. drop table foo; rollback; works just fine (provided you are not running in autocommit mode of course) –  Feb 02 '15 at 21:57

1 Answers1

1

Your question is tagged database-agnostic, so I'll provide an answer that applies to SQL Server (though I suspect that it goes for most modern platforms).

For SQL Server, the answer is no; DDL statements do not automatically COMMIT or ROLLBACK an open transaction. It normally places a schema lock on the object(s) you're touching with the DDL statement, which is held until the end of the transaction.

In fact, transactions are very useful whenever you want to combine DDL and DML statements, in order to maintain atomic business rules in your database.

Don't know about other database platforms, though.

Daniel Hutmacher
  • 9,113
  • 1
  • 25
  • 52