T-SQL不能回滚(T-SQL could not rollback)

我有一个具有纯粹的顺序流一些代码，没有交易。我夹在他们一开始事务并提交事务

begin transaction

......--My code here......
......
......--code to create Table1
......
ALTER TABLE [dbo].[Table1]  WITH CHECK ADD  CONSTRAINT [FK_constraint] FOREIGN KEY([field1], [field2])
REFERENCES [dbo].[Table2] ([field3], [field4])
GO
....
......--End of My code here......


rollback transaction
commit transaction

当我在Management Studio中运行脚本，直到正上方“回滚事务”，如果由零发生，如师一个简单的错误，我运行“回滚事务”，所有的改变都是rolledback没有问题。

但是，如果ALTER TABLE语句，因为表2不存在失败，它然后触发进一步的错误。

消息1767年，级别16，状态0，第2行外键“FK_Constraint引用了无效的表 'dbo.Table2'。
消息1750，级别16，状态0，第2行无法创建约束。请参阅以前的错误。
消息1767年，级别16，状态0，第2行外键“FK_xxxxxx”引用了无效的表“表1”。

当我运行“回滚事务”，我得到这个错误消息“ROLLBACK TRANSACTION请求没有相应的BEGIN TRANSACTION。” 这是愚蠢的，因为我做的顶部有一个开始交易！

请告诉我什么地方出了错。任何帮助将非常感激。使用SQL-Server 2008中。

编辑：

我加

SELECT @@TRANCOUNT;

之前和 “ALTER TABLE [DBO]。[表1] WITH CHECK ADD的约束” 后

....
SELECT @@TRANCOUNT;
ALTER TABLE [dbo].[Table1]  WITH CHECK ADD  CONSTRAINT [FK_constraint] FOREIGN KEY([field1], [field2]) REFERENCES [dbo].[Table2] ([field3], [field4])
GO 
SELECT @@TRANCOUNT;
....

结果分别为1和0。在alter table自动回滚的错误我交易！？我不明白这一点。

Answer 1:

我觉得有由SQL Server本身没有什么可以做与DDL错误的严重性，处理SQL服务器处理，它的一些自动处理（强行回滚例如交易）。

什么你可以做的是使你的脚本代码，应对它周围，并提供脚本的用户具有描述性的错误。

一个例子：

--  drop table thetransformersmorethanmeetstheeye
--  select * from thetransformersmorethanmeetstheeye



--  first batch begins here         

    begin tran

    create table thetransformersmorethanmeetstheeye(i int); -- non-erring if not yet existing

    -- even there's an error here, @@ERROR will be 0 on next batch
    ALTER TABLE [dbo].[Table1]  WITH CHECK ADD  CONSTRAINT [FK_constraint] FOREIGN KEY([field1], [field2])
    REFERENCES [dbo].[Table2] ([field3], [field4]);             

go  -- first batch ends here



--  second batch begins here

    if @@TRANCOUNT > 0 begin        
        PRINT 'I have a control here if things needed be committed or rolled back';

        -- @@ERROR is always zero here, even there's an error before the GO batch. 
        -- @@ERROR cannot span two batches, it's always gets reset to zero on next batch
        PRINT @@ERROR; 


        -- But you can choose whether to COMMIT or ROLLBACK non-erring things here
        -- COMMIT TRAN;
        -- ROLLBACK TRAN;

    end
    else if @@TRANCOUNT = 0 begin
        PRINT 'Sql Server automatically rollback the transaction. Nothing can do about it';
    end
    else begin
        PRINT 'Anomaly occured, @@TRANCOUNT cannot be -1, report this to Microsoft!';
    end

--  second batch implicitly ends here

Answer 2:

发生这种情况的唯一方法是，如果存在这样SPID没有打开的事务。

而已。而且也没有打开的事务的唯一办法是，要么：

老了以后一个承诺你永远不会开始一个新的事务或回滚
你有另一个提交或回滚的地方你没注意到
东西杀了你的连接或（如被迫回滚您的spid外kill从另一个会话命令）

你没有提供太多的代码。是否有任何错误捕获，或在您的查询网页不会显示任何其他条件逻辑？

Answer 3:

据我所知，ALTER TABLE命令将创建自己的新的事务，当它失败，将回滚事务。一个进程内的单回滚将导致PROC内的所有打开的事务回滚。所以你看到的错误，因为ALTER TABLE语句失败的尝试做到这一点之前被隐式回滚您的交易..

您可以通过检查@TRANCOUNT你的代码中，只有调用回滚确认这一点很容易不够时，它不是零

Answer 4:

从错误ALTER TABLE语句是一个编译错误，而不是运行时错误-所以在这永远不会执行发生时声明整批。我猜，有没有GO之间BEGIN TRANSACTION和ALTER TABLE -因此BEGIN TRANSACTION永远不会执行，哪些SQL Server的告诉你是完全正确的。

尝试添加GO后立即BEGIN TRANSACTION 。

Answer 5:

鉴于这种：

create table z
(
i int identity(1,1) not null,
zzz int not null
);

当您尝试以下..

begin try

    begin transaction

    alter table z drop column aaa;

    commit tran;

end try
begin catch 

    print 'hello';
    SELECT
        ERROR_NUMBER() as ErrorNumber,
        ERROR_MESSAGE() as ErrorMessage;

    IF (XACT_STATE()) = -1
    BEGIN
        PRINT
            N'The transaction is in an uncommittable state. ' +
            'Rolling back transaction.'
        ROLLBACK TRANSACTION;
    END;

end catch

print 'reached';

可以被捕获..the错误：

ErrorNumber ErrorMessage
4924    ALTER TABLE DROP COLUMN failed because column 'aaa' does not exist in table 'z'.

但尝试改变alter table z drop column aaa; 要alter table z add zzz int; ，SQL Server可以捕获的错误..

每个表中的列名必须是唯一的。列名在表“Z”“ZZZ”被指定多次。

..但不会产生回控制你，CATCH块不会被触发。似乎没有硬性规定哪些错误是追赶能，哪些不是。

为了说明差异，这里的错误捕捉，能够通过你的代码

下面是一个错误未追赶能够通过你的代码，它类似于您的问题。

请注意，有没有网有（通过SELECT ERROR_NUMBER() as ErrorNumber, ERROR_MESSAGE() as ErrorMessage; 这意味着，SQL Server将它检测到异常后未产生回控制你。

也许你可以在这里看到其他细节，可以帮助： http://msdn.microsoft.com/en-us/library/ms179296.aspx

请参阅本准则的错误处理@@ ERROR和/或TRY - CATCH

顺便说一句，在PostgreSQL上所有种类的DDL错误是赶超能够通过你的代码。

do $$


begin

    -- alter table z drop column aaa;
    alter table z add zzz int;


exception when others then 

    raise notice 'The transaction is in an uncommittable state. '
                     'Transaction was rolled back';

    raise notice 'Yo this is good! --> % %', SQLERRM, SQLSTATE;
end;


$$ language 'plpgsql';

下面是dev的渲染错误信息alter table z drop column aaa; PostgreSQL的：