是否可以在事务内（在 SQL Server 内）运行多个 DDL 语句？
我想知道是否可以在一个事务内运行多个 DDL 语句。 我对 SQL Server 特别感兴趣，尽管其他数据库（至少是 Oracle、PostgreSQL）的答案也可能很有趣。
我一直在为事务内创建的表执行一些“CREATE TABLE”和“CREATE VIEW”，似乎存在一些不一致，我想知道 DDL 是否不应在事务内完成...
我可以可能将 DDL 移到事务之外，但是 我想得到一些这方面的参考。 到目前为止我发现了什么：
- MSDN页面数据库引擎中的隔离级别 清楚地表明在快照隔离下运行的显式事务中可以执行哪些 DDL 操作是有限制的 - 但我没有使用快照隔离，这应该会导致错误。
- 这可以解释为可以在不同隔离级别下的显式事务中执行 DDL 操作吗？
- 适用于 SQL Server 的 Oracle® Database Gateway 用户指南# DDL 语句 规定在给定事务中只能执行一个 DDL 语句 - 这对于直接使用 SQL Server 也有效吗？
对于 Oracle：
- 在 SO 问题 单元测试 DDL 语句中需要在事务中 据说Oracle会隐式提交DDL语句？ （即使没有参考资料）
如果有什么重要的事情，我会通过 JTDS JDBC 驱动程序使用 Java 来完成此操作。
br 东子
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.
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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
我知道大多数数据库都有限制，但 Postgres 没有。 您可以在事务中运行任意数量的表创建、列更改和索引更改，并且在 COMMIT 成功后，其他用户无法看到这些更改。 数据库就应该是这样的！ :-)
对于 SQL Server，您可以在事务内运行 DDL，但是 SQL Server 不对元数据进行版本控制，因此在事务提交之前其他人可以看到更改。 但是 如果您处于事务中，某些 DDL 语句可以回滚，但对于哪些有效、哪些无效，您需要运行一些测试。
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.
如果您正在动态创建表、视图等（除了表变量或临时表），您可能确实需要重新考虑您的设计。 这不是通常应该从用户界面发生的事情。 即使您必须允许某些自定义，DDL 语句也不应该与运行事务插入/更新/删除同时发生。 最好将这些功能分开。
这也需要充分考虑和测试当两个用户尝试同时更改同一个表的结构然后运行事务来插入数据时会发生什么情况。 当您允许用户调整数据库结构时，可能会发生一些真正可怕的事情。
此外，某些 DDL 语句必须始终是批处理的第一个语句。 当你运行它们时也要注意这一点。
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.
对于一般情况和 IIRC，假设 DDL 语句是事务性的并不安全。
也就是说，模式变更如何在事务中交互（假设确实如此）有很大的余地。 我相信这可以由供应商甚至特定的安装（即，直到 dba）来实现。 因此，至少，不要使用一个 DBMS 来假设其他 DBMS 会处理 DDL 语句。
编辑：MySql 是一个 DBMS 的示例，它根本不支持 DDL 事务。 另外，如果您有数据库复制/镜像，则必须非常小心复制服务（无论您相信与否，Sybase 的复制是常态）实际上会复制 DDL 语句。
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.
莫非在MS SQL中，运行DDL和DML语句时会触发隐式事务。 如果您关闭此功能是否有帮助，请使用
SET IMPLICIT_TRANSACTIONS
编辑：另一种可能性
- 您不能将 CREATE VIEW 与同一批次中的其他语句组合起来。 创建表就可以了。
您可以使用 GO 分隔批次。
EDIT2：您可以在一个事务中使用多个DDL，只要用GO分隔即可创建不同的批次。
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.