Is it possible to run multiple DDL statements inside a transaction (within SQL Server)?

Question

Hi,

I'm wondering if it is possible to run multiple DDL statements inside a transaction. I'm specially interested on SQL Server, even though answers with other databases (Oracle, Postgre at least) could also be interesting.

I've been doing some "CREATE TABLE" and "CREATE VIEW" for the created table inside a transaction and there seems to be some inconsistencies and I'm wondering if the DDLs shouldn't be done inside the transaction...

I could probably move the DDL outside the transaction but I'd like to get some reference for this. What I have found this far:

• MSDN page Isolation Levels in the Database Engine tells clearly that there are restrictions on what DDL operations can be performed in an explicit transaction that is running under snapshot isolation - but I'm not using snapshot isolation and this should result as an error.
  • This could be interpreted so that DDL operations can be performend in an explicit transaction under different isolation levels?
• Oracle® Database Gateway for SQL Server User's Guide#DDL Statements states that only one DDL statement can be executed in a given transaction - is this valid also for SQL Server used straight?

For Oracle:

• Within SO question Unit testing DDL statements that need to be in a transaction it is said that Oracle does implicit commit for a DDL statement? (even though no references)

If it matters something, I'm doing this with Java through the JTDS JDBC driver.

b.r. Touko

Answer 1

Could it be that in MS SQL, Implicit transactions are triggered when DDL and DML statements are run. If you toggle this off does this help, use SET IMPLICIT_TRANSACTIONS

EDIT: another possibility - You can't combine CREATE VIEW with other statements in the same batch. CREATE TABLE is ok. You separate batches with GO.

EDIT2: You CAN use multiple DDL in a transaction as long as separated with GO to create different batches.

Answer 2

For the general case and IIRC, it's not safe to assume DDL statements are transactional.

That is to say, there is a great deal of leeway on how schema alterations interact within a transaction (assuming it does at all). This can be by vendor or even by the particular installation (i.e., up to the dba) I believe. So at the very least, don't use one DBMS to assume that others will treat DDL statements the say.

Edit: MySql is an example of a DBMS which doesn't support DDL transactions at all. Also, if you have database replication/mirroring you have to be very careful that the replication service (Sybase's replication is the norm, believe it or not) will actually replicate the DDL statement.

Answer 3

If you are creating tables, views, etc on the fly (other than table variables or temp tables), you may truly need to rethink your design. This is not stuff that should normally happen from the user interface. Even if you must allow some customization, the DDL statements should not be happening at the same time as running transactional inserts/updates/deletes. It is far better to separate these functions.

This is also something that needs a healthy dose of consideration and testing as to what happens when two users try to change the structure of the same table at the same time and then run a transaction to insert data. There's some truly scary stuff that can happen when you allow users to make adjustments to your database structure.

Also some DDL statements must always be the first statement of a batch. Look out for that too when you are running them.

Answer 4

I know most databases have restrictions, but Postgres doesn't. You can run any number table creations, column changes and index changes in a transaction, and the changes aren't visible to other users unit COMMIT succeeds. That's how databases should be! :-)

As for SQL Server you can run DDL inside of a transaction, but SQL Server does not version metadata, and so changes would be visible to others before the transaction commits. But some DDL statements can be rolled back if you are in a transaction, but for which ones work and which ones don't you'll need to run some tests.