SAVE TRANSACTION与BEGIN TRANSACTION（SQL Server）有什么区别？如何嵌套事务以实现良好的效果？

Question

SAVE TRANSACTION与BEGIN TRANSACTION（SQL Server）有什么区别？如何嵌套事务以实现良好的效果？

sql-servertransactionsnested-transactions

20

我有一个存储过程，需要设置保存点（save point）以便在某些情况下撤消所有操作并向调用者返回错误代码，或接受/提交（accept/commit）操作并向调用者返回成功。但我需要它能够在调用者已经开始事务或尚未开始事务的情况下工作。文档对这个问题非常混乱。以下是我认为会起作用的方法，但我不确定所有细节。

问题在于-此存储过程（SP）由其他程序调用。因此，我不知道他们是否已经开始了事务...即使我要求用户在使用我的SP之前启动事务，我仍然对save points的正确使用有疑问。

我的SP将测试是否存在正在进行的事务，如果没有，则以BEGIN TRANSACTION开始一个事务。如果已经存在事务，则会创建一个save point，然后记录这一行为。然后，如果我必须撤消更改，如果我先前执行了BEGIN TRANSACTION，那么我会ROLLBACK TRANSACTION。如果我创建了save point，那么我就会ROLLBACK TRANSACTION MySavePointName。这种方案似乎非常有效。

这里是我有点困惑的地方-如果我想保留我所做的工作，如果我开始一个事务，我将执行COMMIT TRANSACTION。但如果我创建了一个保存点呢？我尝试了COMMIT TRANSACTION MySavePointName，但是调用者尝试提交其事务并收到错误：

“COMMIT TRANSACTION请求没有对应的BEGIN TRANSACTION。”

那么我想知道-保存点可以被回滚（这个方法有效：ROLLBACK TRANSACTION MySavePointName不会回滚调用者的事务）。但是也许永远不需要"commit"它?它只是保留在那里，以防您需要回滚到它，但是在原始事务提交（或回滚）后消失？如果有更好的嵌套事务的方法，请提供一些指导。我还没有找到如何使用BEGIN TRANSACTION进行嵌套操作，但只回滚或提交我的内部事务。似乎ROLLBACK将始终回滚到顶部事务，而COMMIT仅简单地减少@@trancount。

- Brian B

你的发现可能值得发布为答案。 - Andriy M

@Andriy Ok，我已经这么做了——删除了我的编辑并将其作为答案使用。谢谢。 - Brian B

3个回答

15

在 Brian B的回答基础上扩展。

这确保了保存点名称是唯一的，并使用 SQL Server 2012 的新 TRY/CATCH/THROW 特性。

DECLARE @mark CHAR(32) = replace(newid(), '-', '');
DECLARE @trans INT = @@TRANCOUNT;

IF @trans = 0
    BEGIN TRANSACTION @mark;
ELSE
    SAVE TRANSACTION @mark;

BEGIN TRY
    -- do work here

    IF @trans = 0
        COMMIT TRANSACTION @mark;
END TRY
BEGIN CATCH
    IF xact_state() = 1 OR (@trans = 0 AND xact_state() <> 0) ROLLBACK TRANSACTION @mark;
    THROW;
END CATCH

- Chris Chilvers

我有一段时间没有回过这篇帖子了，但喜欢那个模板，你可以直接在下一个查询中重复使用。不错的增强。 - Brian B

我在 MSDN 上看到建议将“begin transaction”语句放在 try 块内。按照这种模式做会导致一些问题吗？还是完全安全的？ - Evaldas Buinauskas

我对catch块中的if语句感到困惑。如果xact状态=-1，为什么要回滚事务？难道不应该是<>-1吗？ - Edwin Stoteler

我之前遇到一个问题，现在已经解决了。在上面的模板中，无论trans变量的值如何，我的代码都会标记COMMIT TRANSACTION。虽然大多数时候它是有效的，但有时候不起作用。我正在想，根据你的模板，如果事务没有嵌套，我们是否应该提交一个事务；如果它是嵌套的，我们是否应该不提交它呢？ - Jami

5

我在我的存储过程中使用了这种类型的事务管理器：

    CREATE PROCEDURE Ardi_Sample_Test  
        @InputCandidateID INT  
    AS  
        DECLARE @TranCounter INT;  
        SET @TranCounter = @@TRANCOUNT;  
        IF @TranCounter > 0  
            SAVE TRANSACTION ProcedureSave;  
        ELSE  
            BEGIN TRANSACTION;  
        BEGIN TRY  

            /*
            <Your Code>
            */

            IF @TranCounter = 0  
                COMMIT TRANSACTION;  
        END TRY  
        BEGIN CATCH  
            IF @TranCounter = 0  
                ROLLBACK TRANSACTION;  
            ELSE  
                IF XACT_STATE() <> -1  
                    ROLLBACK TRANSACTION ProcedureSave;  

            DECLARE @ErrorMessage NVARCHAR(4000);  
            DECLARE @ErrorSeverity INT;  
            DECLARE @ErrorState INT;  
            SELECT @ErrorMessage = ERROR_MESSAGE();  
            SELECT @ErrorSeverity = ERROR_SEVERITY();  
            SELECT @ErrorState = ERROR_STATE();  

            RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);  
        END CATCH  
    GO

- Ardalan Shahgholi

1

微软认为这是推荐的方法。参考链接 - rschoenbach

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Brian B · Accepted Answer

我相信我现在已经弄清楚了这一切，所以我会回答自己的问题...

如果您需要更多细节，我甚至已经在博客中记录了我的发现：http://geekswithblogs.net/bbiales/archive/2012/03/15/how-to-nest-transactions-nicely---quotbegin-transactionquot-vs-quotsave.aspx 所以我的存储过程以这样的方式开始，如果没有事务，则启动新事务，但如果已经有一个事务正在进行中，则使用保存点：

DECLARE @startingTranCount int
SET @startingTranCount = @@TRANCOUNT

IF @startingTranCount > 0
    SAVE TRANSACTION mySavePointName
ELSE
    BEGIN TRANSACTION
-- …

然后，当准备提交更改时，只有在我们自己开始了事务的情况下才需要提交：

IF @startingTranCount = 0
    COMMIT TRANSACTION

最后，只需回滚您目前所做的更改：

-- Roll back changes...
IF @startingTranCount > 0
    ROLLBACK TRANSACTION MySavePointName
ELSE
    ROLLBACK TRANSACTION