MSSQL server中嵌套事务

评分 0, 满分 5 星(49 次阅读)
Loading...
添加评论

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

消息 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   来源 www.pms.cc

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

假设有一张表,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 ; 来源 www.pms.cc

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 语句的数目不匹配"之类的错误了,实际上就是在每个嵌套的子过程中标明当前事务点,每个子事务 只提交/回滚 子事务点,而不是回滚整个事务!

来源:cnblogs.com, 原文链接

声明: 本文采用 BY-NC-SA 协议进行授权. 转载请注明转自: MSSQL server中嵌套事务
 
评分 4.1, 满分 5 星
Loading...