主页 / dba / 问题 / 237935
Accepted
Evan Carroll
Asked: 2019-05-12 17:42:45 +0800 CST

SQL Server 在提交之前是否允许（使可见）事务内部的 DDL 到事务？

  • 772

在 PostgreSQL 中，我可以创建一个包含一些测试数据的表，然后在事务中将其迁移到不同类型的新列，从而导致一个表重写COMMIT，

CREATE TABLE foo ( a int );
INSERT INTO foo VALUES (1),(2),(3);

其次是，

BEGIN;
  ALTER TABLE foo ADD COLUMN b varchar;
  UPDATE foo SET b = CAST(a AS varchar);
  ALTER TABLE foo DROP COLUMN a;
COMMIT;

但是，在 Microsoft 的 SQL Server 中，同样的事情似乎会产生错误。比较这个工作db fiddle，其中ADD(column) 命令在事务之外，

-- txn1
BEGIN TRANSACTION;
  ALTER TABLE foo ADD b varchar;
COMMIT;

-- txn2
BEGIN TRANSACTION;
  UPDATE foo SET b = CAST( a AS varchar );
  ALTER TABLE foo DROP COLUMN a;
COMMIT;

到这个不起作用的数据库小提琴，

-- txn1
BEGIN TRANSACTION;
  ALTER TABLE foo ADD b varchar;
  UPDATE foo SET b = CAST( a AS varchar );
  ALTER TABLE foo DROP COLUMN a;
COMMIT;

而是错误

Msg 207 Level 16 State 1 Line 2
Invalid column name 'b'.

关于 DDL，是否有让这个事务可见的行为，就像 PostgreSQL 一样？

sql-server transaction

3 个回答

  1. Best Answer

    一般来说，没有。SQL Server 在执行之前在当前范围内编译整个批处理，因此引用的实体必须存在（语句级重新编译也可能在以后发生）。主要的例外是延迟名称解析，但它适用于表，而不是列：

    延迟名称解析只能在您引用不存在的表对象时使用。所有其他对象在创建存储过程时必须存在。例如，当您在存储过程中引用现有表时，您不能列出该表不存在的列。

    常见的解决方法涉及动态代码（如 Joe 的回答），或将 DML 和 DDL 分成单独的批次。

    对于这种特定情况，您还可以编写：

    BEGIN TRANSACTION;

    ALTER TABLE dbo.foo
        ALTER COLUMN a varchar(11) NOT NULL
        WITH (ONLINE = ON);

    EXECUTE sys.sp_rename
        @objname = N'dbo.foo.a',
        @newname = N'b',
        @objtype = 'COLUMN';

COMMIT TRANSACTION;

    您仍然无法b在同一批次和范围内访问重命名的列，但它确实完成了工作。

    对于 SQL Server，有一种观点认为在事务中混合 DDL 和 DML 并不是一个好主意。过去有一些错误，这样做会导致不正确的日志记录和无法恢复的数据库。尽管如此，人们还是会这样做，尤其是使用临时表。它可能会导致一些非常难以理解的代码。

    • 17

  2. 这是你要找的吗？

    BEGIN TRANSACTION;
  ALTER TABLE foo ADD b varchar;
  EXEC sp_executesql N'UPDATE foo SET b = CAST( a AS varchar )';
  ALTER TABLE foo DROP COLUMN a;
COMMIT;
    • 12

  3. 对于 Paul White 回答的“一般不”的陈述，我希望以下内容可以直接回答这个问题，但也有助于显示这种过程的系统性局限性，并引导您远离那些不容易管理和暴露的方法风险。

    可以多次提到不要在进行 DML 的同时进行 DDL 更改。良好的编程将这些功能分开以保持可支持性并避免意大利面条串的变化。

    正如 Paul 简洁地指出的那样，SQL Server 是批量工作的。

    现在，对于那些怀疑这是否有效的人，它可能不适用于您的实例，但某些版本（如 2017）实际上可以工作！这是证明： 在此处输入图像描述

    [测试代码 - 可能不适用于许多版本的 SQL Server]

    USE master
GO
CREATE TABLE foo (a VARCHAR(11) )
GO
BEGIN TRANSACTION;
    INSERT INTO dbo.foo (a)
    VALUES ('entry')
/*****
[2] Check Values
*****/
    SELECT a FROM dbo.foo
/*****
[3] Add Column
*****/
    ALTER TABLE dbo.foo
        ADD b VARCHAR(11)
/*****
[3] Insert value into this new column in the same batch
-- Again, this is just an example. Please do not do this in production
*****/
    IF EXISTS (SELECT * FROM sys.columns WHERE object_ID('foo') = object_id
            AND name = 'b')
        INSERT INTO dbo.foo (b)
        VALUES ('d')
COMMIT TRANSACTION;
/*****
[4] SELECT outside transaction
-- this will fail
*****/
    --IF EXISTS (SELECT * FROM sys.columns WHERE object_ID('foo') = object_id
    --      AND name = 'b')
    --  SELECT b FROM dbo.foo
-- this will work...but a SELECT * ???
IF EXISTS (SELECT * FROM sys.columns WHERE object_ID('foo') = object_id
            AND name = 'b')
        SELECT * FROM dbo.foo

DROP TABLE dbo.foo

    [结论]

    所以是的，您可以像@AndriyM-SQL 2017 上的 dbfiddle 指出的那样，对 SQL Server 的某些版本或补丁在同一批次中执行 DDL 和 DML ，但并非所有 DML 都受支持，并且不能保证总是如此。如果它有效，那可能是您的 SQL Server 版本的异常，这可能会在您修补或迁移到新版本时导致严重的问题。

    • 另外，一般来说，您的设计应该预见到变化。我了解修改/添加列可能会对表格产生影响，但您可以分批正确设计。

    [额外信用]

    至于 EXISTS 语句，就像 Paul 所说的那样，在进入代码的下一步之前，还有很多其他方法可以验证代码。

    • EXISTS 语句可以帮助您创建适用于所有 SQL Server 版本的代码
    • 它是一个布尔函数，允许在一个语句中进行复杂检查
    • 2

相关问题