sql - 仅提交在 TRANSACTION 中可能回滚的特定更改

Question

这是对原始问题的重大编辑，使其更加简洁并涵盖了现有答案提出的观点......

是否可以在单个事务中对多个表进行多次更改，并且仅回滚部分更改？

在下面的 TSQL 中，我不希望“myLogSP”所做的任何更改都被回滚。但是，如有必要，各个 myBusinessSP 所做的所有更改都应回滚。

BEGIN TRANSACTION  

    EXEC myLogSP

    EXEC @err = myBusinessSPa
    IF (@err <> 0) BEGIN ROLLBACK TRANSACTION RETURN -1 END

    EXEC myLogSP

    EXEC @err = myBusinessSPb
    IF (@err <> 0) BEGIN ROLLBACK TRANSACTION RETURN -1 END

    EXEC myLogSP

    EXEC @err = myBusinessSPc
    IF (@err <> 0) BEGIN ROLLBACK TRANSACTION RETURN -1 END

    EXEC myLogSP

COMMIT TRANSACTION
RETURN 0

顺序很重要，myLogSP 必须在 myBusinessSP 之间和之后发生(myLogSP 接收 myBusinessSP 所做的更改)

同样重要的是，所有 myBusinessSP 都发生在一个事务中以维护数据库完整性，并在必要时允许其所有更改回滚。

就好像我希望 myLogSP 表现得好像它们不是事务的一部分一样。它们恰好在一个内部(因为需要在 myBusinessSP 之间调用)，这只是一个不方便的事实。

编辑:

最终答案是否定的，唯一的选择是重新设计代码。要么使用表变量进行日志记录(因为变量不会回滚)或将业务逻辑重新设计为不需要事务...

Answer 1

使用 SAVEPOINT s ，例如

BEGIN TRANSACTION  

    EXEC myLogSP

    SAVE TRANSACTION savepointA
    EXEC @err = myBusinessSPa
    IF (@err <> 0) BEGIN
        ROLLBACK TRANSACTION savepointA
        COMMIT
        RETURN -1
    END

    EXEC myLogSP

    SAVE TRANSACTION savepointB
    EXEC @err = myBusinessSPb
    IF (@err <> 0) BEGIN
        ROLLBACK TRANSACTION savepointB
        COMMIT
        RETURN -1
    END

    EXEC myLogSP

    SAVE TRANSACTION savepointC
    EXEC @err = myBusinessSPc
    IF (@err <> 0) BEGIN
        ROLLBACK TRANSACTION savepointC
        COMMIT
        RETURN -1
    END

    EXEC myLogSP

COMMIT TRANSACTION

编辑

根据目前提供的信息(以及我对它的理解)，您似乎必须重新设计日志记录 SP，以使用变量或使用文件，或允许它们“事后”运行如下:

BEGIN TRANSACTION  

    SAVE TRANSACTION savepointA
    EXEC @err = myBusinessSPa
    IF (@err <> 0) BEGIN
        ROLLBACK TRANSACTION savepointA
        EXEC myLogSPA -- the call to myBusinessSPa was attempted/failed
        COMMIT
        RETURN -1
    END

    SAVE TRANSACTION savepointB
    EXEC @err = myBusinessSPb
    IF (@err <> 0) BEGIN
        ROLLBACK TRANSACTION savepointB
        EXEC myLogSPA -- the call to myBusinessSPa originally succeeded
        EXEC myLogSPB -- the call to myBusinessSPb was attempted/failed
        COMMIT
        RETURN -1
    END

    SAVE TRANSACTION savepointC
    EXEC @err = myBusinessSPc
    IF (@err <> 0) BEGIN
        ROLLBACK TRANSACTION savepointC
        EXEC myLogSPA -- the call to myBusinessSPa originally succeeded
        EXEC myLogSPB -- the call to myBusinessSPb originally succeeded
        EXEC myLogSPC -- the call to myBusinessSPc was attempted/failed
        COMMIT
        RETURN -1
    END

    EXEC myLogSPA -- the call to myBusinessSPa succeeded
    EXEC myLogSPB -- the call to myBusinessSPb succeeded
    EXEC myLogSPC -- the call to myBusinessSPc succeeded

COMMIT TRANSACTION