sql server中巢狀事務*

zhangfen1991發表於2018-11-09
SQLServer巢狀

轉自 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…中

相關文章