sql server中嵌套事务*

来源：cnblogs　　作者：zhangfen1991　　时间：2018/11/9 18:22:01　　对本文有异议

转自 https://www.cnblogs.com/guanjie20/archive/2013/02/17/2914488.html

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

消息 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，第 0 行
EXECUTE 后的事务计数指示 BEGIN 和 COMMIT 语句的数目不匹配。上一计数 = 1，当前计数 = 0。
消息 3903，级别 16，状态 1，过程 outertranv2，第 18 行
ROLLBACK 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 后的事务计数指示 BEGIN 和 COMMIT 语句的数目不匹配"之类的错误了，实际上就是在每个嵌套的子过程中标明当前事务点，每个子事务只提交/回滚子事务点，而不是回滚整个事务！

实际开发中还是会出现事务错乱的情况，如在try...catch...中

友情链接：直通硅谷　点职佳　北美留学生论坛

MS SQL Server热门文章

[sql Server]除非另外还指定了TOP 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效

SQL 拼接字符串使用IN查询方法

Microsoft SQL Server 双机热备份，实时同步

SQL Server 2016 + AlwaysOn 无域集群

Microsoft SQL Server 2012安装说明

mssql sqlserver 不固定行转列数据(动态列)

解釋 SQL Server 的狀態值 Runnable、Running、Suspended、Sleeping

sql server中format函数的yyyyMMddHHmmssffff时间格式兼容旧版sql写法

The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SQL去除数据库表中tab、空格、回车符等特殊字符的解决方法

MS SQL Server推荐文章

拯救SQL Server数据库事务日志文件损坏的终极大招

SQL Server实战七：自定义数据类型、标量值、内嵌表值、多语句表值函数的操作

SQL Server实战六：T-SQL、游标、存储过程的操作

SQL Server实战四：查询数据库的数据

SQL Server 2022 AlwaysOn新特性之包含可用性组介绍

当 SQL Server（mssql-jdbc）遇上 BigDecimal → 精度丢失，真坑！