sql server 学习笔记 (nested transaction 嵌套事务)

什么时候会用到嵌套事务？

为了代码复用，我们会写许多的储蓄过程，而中间如果需要使用到 transaction 难免就会发生嵌套了.

sql server 并不直接支持嵌套事务. 但它可以用一些招式来实现嵌套效果. 虽然这些招式并不优雅，也容易让了陷入迷雾.

这篇收集了一些资料来说说 sql server 中的嵌套事务.

这篇写了基本的 sql server 对 transaction 的处理方式 https://www.cnblogs.com/kymo/archive/2008/05/14/1194161.html

begin trans @@count +1

commit trans @@count -1，如果 -1 后为 0 那么永久写入磁盘, 不然继续等待下一个 commit.

rollback trans @@count = 0 (rollback + save point name 则不会 update count, count 保持)

所以一个常遇到的 bug 就是, 就是里面的 trans rollabck 后, 外面的 trans 就废掉了, 也不能 rollback 也不能 commit, 因为 count 直接被设置为 0 了。

这就是为什么说 sql server 不直接支持嵌套事务了。

那有什么方法可以实现呢？

这篇给出了很好的示范

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/save-transaction-transact-sql?view=sql-server-ver15

https://stackoverflow.com/questions/9713350/save-transaction-vs-begin-transaction-sql-server-how-to-nest-transactions-nice

在写储蓄过程时，我们需要判断当前的 trans count 来决定如果处理 trans

开始的时候，如果当前没有 count = 0 表示没有 trans

那么储蓄过程需要负责创建 trans

然后也负责 rollback or commit.

如果当前已经有了 trans

那么储蓄过程就不要创建新的 trans 而是做一个 save point

如果没有任何 error 也不需要 commit ，把职责交给外面的人就行了.

那么遇到 error 时就要处理了，首先 rollback to save point. (不会减少 count)

然后 throw 一个 error 到外面.

大致上就是这样，核心概念就是始终只开启一个事务, 然后利用 save point + rollback save point + throw error

储蓄过程大概长这样

GO
IF EXISTS (SELECT name FROM sys.objects
           WHERE name = N'SaveTranExample')
    DROP PROCEDURE SaveTranExample;
GO
CREATE PROCEDURE SaveTranExample
AS

    DECLARE @TranCounter INT;
    SET @TranCounter = @@TRANCOUNT;
    IF @TranCounter > 0
        SAVE TRANSACTION ProcedureSave;
    ELSE
        BEGIN TRANSACTION;
        print(@@TRANCOUNT)
        update Products set name = 'mk100' where Id = 1;
    -- Modify database.  
    BEGIN TRY
        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, -- Message text.  
                   @ErrorSeverity, -- Severity.  
                   @ErrorState -- State.  
                   );
    END CATCH
GO

调用的时候是这样的

select * from Products order by Id;
update Products set name = 'asdds' where Id = 1;
update Products set name = 'asdwf' where Id = 2;

begin tran
update Products set name = 'mk100' where Id = 2;
begin try
    exec SaveTranExample;
    commit;
end try
begin catch
    print('error')
    rollback;
end catch