是否可以在一个事务中（在 SQL Server 中）运行多个 DDL 语句？

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

提问by Touko

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, PostgreSQL at least) could also be interesting.

我想知道是否可以在事务中运行多个 DDL 语句。我对 SQL Server 特别感兴趣，尽管其他数据库（至少是 Oracle、PostgreSQL）的答案也可能很有趣。

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

我一直在为事务中创建的表做一些“CREATE TABLE”和“CREATE VIEW”，似乎存在一些不一致，我想知道 DDL 是否不应该在事务中完成......

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:

我可能可以将 DDL 移到事务之外，但我想为此获得一些参考。到目前为止我发现了什么：

• MSDN page Isolation Levels in the Database Enginetells 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 Statementsstates that only one DDL statement can be executed in a given transaction- is this valid also for SQL Server used straight?

• 数据库引擎中的MSDN 页面隔离级别清楚地表明，对于在快照隔离下运行的显式事务中可以执行的 DDL 操作存在限制- 但我没有使用快照隔离，这应该会导致错误。
  • 这可以解释为可以在不同隔离级别下的显式事务中执行 DDL 操作吗？
• 甲骨文？对于SQL Server用户指南＃DDL语句的数据库网关，各国只有一个DDL语句可以在给定的交易执行-这是同样有效的SQL Server直用吗？

For Oracle:

对于甲骨文：

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

• 在 SO 问题中，需要在事务中进行单元测试 DDL 语句，据说 Oracle 对 DDL 语句进行了隐式提交？（虽然没有参考）

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

如果这很重要，我将通过 JTDS JDBC 驱动程序使用 Java 执行此操作。

b.r. Touko

br Touko

回答by David Roussel

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! :-)

我知道大多数数据库都有限制，但 Postgres 没有。您可以在事务中运行任意数量的表创建、列更改和索引更改，并且更改对其他用户不可见，单元 COMMIT 成功。数据库应该是这样的！:-)

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.

对于 SQL Server，您可以在事务内运行 DDL，但SQL Server 不会对元数据进行版本化，因此在事务提交之前，其他人可以看到更改。但是，如果您在事务中，某些 DDL 语句可以回滚，但是对于哪些有效，哪些无效，您需要运行一些测试。

回答by HLGEM

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.

如果您正在动态创建表、视图等（除了表变量或临时表），您可能真的需要重新考虑您的设计。这不是通常应该从用户界面发生的事情。即使您必须允许进行一些自定义，DDL 语句也不应该在运行事务性插入/更新/删除的同时发生。最好将这些功能分开。

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.

此外，某些 DDL 语句必须始终是批处理的第一条语句。运行它们时也要注意这一点。

回答by Stuart

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

可能是在MS SQL中，运行DDL和DML语句时会触发隐式事务。如果您关闭此功能是否有帮助，请使用 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.

编辑：另一种可能性 - 您不能将 CREATE VIEW 与同一批次中的其他语句结合使用。创建表没问题。您可以使用 GO 分离批次。

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

EDIT2：你可以在一个事务中使用多个 DDL，只要用 GO 分开来创建不同的批次。

回答by hythlodayr

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

对于一般情况和 IIRC，假设 DDL 语句是事务性的是不安全的。

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.

也就是说，模式更改如何在事务中交互（假设它确实如此）有很大的余地。我相信这可以由供应商或什至特定的安装（即，由 dba 决定）。所以至少，不要使用一个 DBMS 来假设其他人会处理 DDL 语句。

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.

编辑：MySql 是一个完全不支持 DDL 事务的 DBMS 示例。此外，如果您有数据库复制/镜像，您必须非常小心复制服务（Sybase 的复制是常态，不管你信不信）实际上会复制 DDL 语句。