• sql server中嵌套事务


    我们在写事务时经常遇到的问题如下:

    消息 266,级别 16,状态 2,过程 sp1,第 0 行
    EXECUTE 后的事务计数指示 BEGIN 和 COMMIT 语句的数目不匹配。上一计数 = 1,当前计数 = 0。
    消息 3903,级别 16,状态 1,过程 sp2,第 15 行
    ROLLBACK TRANSACTION 请求没有对应的 BEGIN TRANSACTION。
    

     如果这只是一个单独的事务引起的,那么很好解决,我们只要检查下是否遗漏了匹配的BEGIN tran 和 COMMIT tran即可,但是如果2个存储过程都是用事务写的,那么就即使每个存储过程的事务写法都正常,也会报这个错误,

    这是因为只要子事务里有回滚语句:如ROLLBACK      那么全局的@@TRANCOUNT被直接置为0了,导致父事务提交时发现 @@TRANCOUNT=0  报错 ,sql server会认为当前不存在任何事务,在父存储过程中任何的COMMIT TRAN或

    ROLLBACK 语句都会找不到它对应的 BEGIN TRAN   

    下面我们用一个实例来看下:

    假设有一张表,ID为非自增主键

    USE [TestDB]
    GO
    
    /****** Object:  Table [dbo].[test]    Script Date: 02/17/2013 15:44:35 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    SET ANSI_PADDING ON
    GO
    
    CREATE TABLE [dbo].[test](
        [ID] [bigint] NOT NULL,
        [UserID] [bigint] NULL,
        [Name] [varchar](50) NULL,
     CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 
    (
        [ID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    SET ANSI_PADDING OFF
    GO

     我们常规的写一个插入的子存储过程如下:

    USE [TestDB]
    GO
    /****** Object:  StoredProcedure [dbo].[innertranv1]    Script Date: 02/17/2013 15:46:46 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    --内层事务存储过程,演示如何处理才能在嵌套的事务存储过程中正确处理事务
    ALTER PROCEDURE [dbo].[innertranv1]
        @ID BIGINT ,
        @UserID BIGINT ,
        @Name VARCHAR(50)
    AS 
        BEGIN
            SET XACT_ABORT ON    
            BEGIN TRAN    
         
            IF(EXISTS(SELECT TOP 1 * FROM dbo.test WHERE ID=@ID))    
            BEGIN
                    ROLLBACK        
                    RETURN 0 ;  
            END
            
            --业务逻辑开始
            
            INSERT  dbo.test
                    ( ID, UserID, Name)
            VALUES  ( @ID, 
                      @UserID,
                      @Name  
                      )
            --业务逻辑结束
            
            IF @@error <> 0 
                BEGIN  
                    ROLLBACK                                   
                    RETURN 0;  
                END        
      
            COMMIT   
         SET XACT_ABORT OFF;   
         RETURN 1 ; 

    END

    调用的父存储过程如下:

    USE [TestDB]
    GO
    /****** Object:  StoredProcedure [dbo].[outertranv2]    Script Date: 02/17/2013 16:09:09 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    -- =============================================
    -- Author:        <Author,,Name>
    -- Create date: <Create Date,,>
    -- Description:    <外层存储过程>
    -- =============================================
    ALTER PROCEDURE [dbo].[outertranv2]
        @ID BIGINT ,
        @UserID BIGINT ,
        @Name VARCHAR(50)
    AS 
            
        BEGIN TRAN     
        DECLARE @result INT
        EXEC @result = innertranv1 @ID =@ID, @UserID =@UserID, @Name = @Name
        IF ( @result <= 0 ) 
            BEGIN
                ROLLBACK TRAN  ;                 
                RETURN ;
            END 
        COMMIT TRAN        

    我们执行父存储过程:

    USE [TestDB]
    GO
    
    DECLARE    @return_value int
    
    EXEC    @return_value = [dbo].[outertranv2]
            @ID = 0,
            @UserID = 0,
            @Name = N'0'
    
    SELECT    'Return Value' = @return_value
    
    GO

    第一次提交正常,再次执行就会出现如下错误:

    消息 266,级别 16,状态 2,过程 innertranv1,第 0EXECUTE 后的事务计数指示 BEGINCOMMIT 语句的数目不匹配。上一计数 = 1,当前计数 = 0。
    消息 3903,级别 16,状态 1,过程 outertranv2,第 18ROLLBACK TRANSACTION 请求没有对应的 BEGIN TRANSACTION

    如何解决?我们修改子存储过程如下:

    USE [TestDB]
    GO
    /****** Object:  StoredProcedure [dbo].[innertran]    Script Date: 02/17/2013 16:26:26 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    --内层事务存储过程,演示如何处理才能在嵌套的事务存储过程中正确处理事务
    ALTER PROCEDURE [dbo].[innertran]
        @ID BIGINT ,
        @UserID BIGINT ,
        @Name VARCHAR(50)
    AS 
        BEGIN
            DECLARE @TRANCOUNT int=(select @@TRANCOUNT)
        
            SET XACT_ABORT ON
            SET @TRANCOUNT=(select @@TRANCOUNT)
            PRINT '未进入子事务前全局@@TRANCOUNT'+CAST(@TRANCOUNT AS VARCHAR(50))    
            BEGIN TRAN tran1      --开始事务 
            SAVE TRAN tranpoint   --保存事务点
            SET @TRANCOUNT=(select @@TRANCOUNT)
            PRINT '进入子事务后全局@@TRANCOUNT'+CAST(@TRANCOUNT AS VARCHAR(50))    
           
            IF(EXISTS(SELECT TOP 1 * FROM dbo.test WHERE ID=@ID))    
            BEGIN
                    ROLLBACK TRAN tranpoint ;   --回滚保存点的事务   
                    COMMIT TRAN tran1 ;            --提示当前事务
                    SET @TRANCOUNT=(select @@TRANCOUNT)  
                    PRINT '回滚子事务后全局@@TRANCOUNT'+CAST(@TRANCOUNT AS VARCHAR(50))     
                              
                    RETURN 0 ;  
            END
            
            --业务逻辑开始
            
            INSERT  dbo.test
                    ( ID, UserID, Name)
            VALUES  ( @ID, 
                      @UserID,
                      @Name  
                      )
            --业务逻辑结束
            
            IF @@error <> 0 
                BEGIN  
                    ROLLBACK TRAN tranpoint ; --回滚保存点的事务   
                    COMMIT TRAN tran1 ;          --提示当前事务  
                    SET @TRANCOUNT=(select @@TRANCOUNT)  
                    PRINT '回滚子事务后全局@@TRANCOUNTT'+CAST(@TRANCOUNT AS VARCHAR(50))     
                                             
                    RETURN 0;  
                END        
      
            COMMIT TRAN tran1 ;    
            SET XACT_ABORT OFF;    
            SET @TRANCOUNT=(select @@TRANCOUNT)  
            PRINT '提交子事务后全局@@TRANCOUNT'+CAST(@TRANCOUNT AS VARCHAR(50))
               
            RETURN 1 ;
    
        END

    父过程如下:  

    USE [TestDB]
    GO
    /****** Object:  StoredProcedure [dbo].[outertran]    Script Date: 02/17/2013 16:27:13 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:        <Author,,Name>
    -- Create date: <Create Date,,>
    -- Description:    <外层存储过程>
    -- =============================================
    ALTER PROCEDURE [dbo].[outertran]
        @ID BIGINT,
        @UserID BIGINT,
        @Name VARCHAR(50)
    AS 
        DECLARE @TRANCOUNT int=(select @@TRANCOUNT)
        PRINT '未进入父事务前全局@@TRANCOUNT:'+CAST(@TRANCOUNT AS VARCHAR(50))
        
            
        BEGIN TRAN 
        SET @TRANCOUNT=(select @@TRANCOUNT) 
        PRINT '进入父事务后全局@@TRANCOUNT:'+CAST(@TRANCOUNT AS VARCHAR(50))    
            
        DECLARE @result INT
    
        EXEC @result = innertran @ID = @ID, @UserID = @UserID, @Name =@Name
    
        IF ( @result <= 0 ) 
            BEGIN
                ROLLBACK TRAN  ;
                SET @TRANCOUNT=(select @@TRANCOUNT) 
                PRINT '回滚父事务后全局@@TRANCOUNT:'+CAST(@TRANCOUNT AS VARCHAR(50)) 
                            
                RETURN ;
            END 
        COMMIT TRAN 
        SET @TRANCOUNT=(select @@TRANCOUNT) 
        PRINT '提交父事务后全局@@TRANCOUNT:'+CAST(@TRANCOUNT AS VARCHAR(50))

     调用父存储过程:

    USE [TestDB]
    GO
    
    DECLARE    @return_value int
    
    EXEC    @return_value = [dbo].[outertran]
            @ID = 0,
            @UserID = 0,
            @Name = N'0'
    
    SELECT    'Return Value' = @return_value
    
    GO

    结果如下:

    未进入父事务前全局@@TRANCOUNT:0
    进入父事务后全局@@TRANCOUNT:1
    未进入子事务前全局@@TRANCOUNT:1
    进入子事务后全局@@TRANCOUNT:2
    回滚子事务后全局@@TRANCOUNT:1
    回滚父事务后全局@@TRANCOUNT:0

    不会再报"EXECUTE 后的事务计数指示 BEGINCOMMIT 语句的数目不匹配"之类的错误了,实际上就是在每个嵌套的子过程中标明当前事务点,每个子事务 只提交/回滚 子事务点,而不是回滚整个事务!

  • 相关阅读:
    Objective-C Collection was mutated while being enumerated crash
    C++ assert断言
    Objective-C 禁用NSMenu中的系统services菜单项
    django----命令
    django----admin源码流程
    django----admin
    django----利用Form 实现两次密码输入是否一样 ( 局部钩子和全局钩子 )
    django----基于Form组件实现的增删改和基于ModelForm实现的增删改
    java----面试题
    课外知识----ini
  • 原文地址:https://www.cnblogs.com/guanjie20/p/2914488.html
Copyright © 2020-2023  润新知