是否可以在事务内(在SQL Server中)运行多个DDL语句? [英] Is it possible to run multiple DDL statements inside a transaction (within SQL Server)?

查看:308
本文介绍了是否可以在事务内(在SQL Server中)运行多个DDL语句?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

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

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.

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

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

我可能会将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:


  • MSDN页面数据库引擎中的隔离级别清楚地表明,在快照隔离下运行的显式事务中可以执行哪些DDL操作受到限制-但是我没有使用快照隔离,这应该导致错误。


    • 可以解释为可以在不同隔离级别下的显式事务中执行DDL操作吗?

    • 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:


      • 在问题内< a href = https://stackoverflow.com/questions/892923/unit-testing-ddl-statements-that-need-to-be-in-a-transaction>需要在事务中进行单元测试的DDL语句据说Oracle是否对DDL语句隐式提交? (即使没有引用)

      • 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)

      如果有什么重要的事情,我将通过JTDS JDBC驱动程序使用Java进行。

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

      br Touko

      推荐答案

      我知道大多数数据库都有限制,但Postgres没有。您可以在事务中运行任何数字表创建,列更改和索引更改,并且COMMIT成功后,其他用户看不到这些更改。那应该是数据库! :-)

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

      对于SQL Server,您可以在事务内部运行DDL,但是 SQL Server不会对元数据进行版本化,因此其他事务在提交事务之前就可以看到更改。但是如果您正在进行事务处理,则可以回滚某些DDL语句,但是对于那些有效和无效的DDL语句,您将需要运行一些测试。

      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语句?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆