sql server中嵌套事务*

时间：2024-09-06 11:35:50

转自 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''

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''

SELECT    'Return Value' = @return_value

GO

结果如下：

未进入父事务前全局@@TRANCOUNT:0

进入父事务后全局@@TRANCOUNT:1

未进入子事务前全局@@TRANCOUNT:1

进入子事务后全局@@TRANCOUNT:2

回滚子事务后全局@@TRANCOUNT:1

回滚父事务后全局@@TRANCOUNT:0

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

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

相关文章