我們在寫事務(wù)時經(jīng)常遇到的問題如下：
消息 266，級別 16，狀態(tài) 2，過程 sp1，第 0 行 EXECUTE 后的事務(wù)計數(shù)指示 BEGIN 和 COMMIT 語句的數(shù)目不匹配。上一計數(shù) = 1，當前計數(shù) = 0。 消息 3903，級別 16，狀態(tài) 1，過程 sp2，第 15 行 ROLLBACK TRANSACTION 請求沒有對應(yīng)的 BEGIN TRANSACTION。
如果這只是一個單獨的事務(wù)引起的，那么很好解決，我們只要檢查下是否遺漏了匹配的BEGIN tran 和 COMMIT tran即可，但是如果2個存儲過程都是用事務(wù)寫的，那么就即使每個存儲過程的事務(wù)寫法都正常，也會報這個錯誤，
這是因為只要子事務(wù)里有回滾語句：如ROLLBACK 那么全局的@@TRANCOUNT被直接置為0了，導致父事務(wù)提交時發(fā)現(xiàn) @@TRANCOUNT=0 報錯 ，sql server會認為當前不存在任何事務(wù)，在父存儲過程中任何的COMMIT TRAN或
ROLLBACK 語句都會找不到它對應(yīng)的 BEGIN TRAN
下面我們用一個實例來看下：
假設(shè)有一張表，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
我們常規(guī)的寫一個插入的子存儲過程如下：
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 --內(nèi)層事務(wù)存儲過程，演示如何處理才能在嵌套的事務(wù)存儲過程中正確處理事務(wù) 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 --業(yè)務(wù)邏輯開始 INSERT dbo.test ( ID, UserID, Name) VALUES ( @ID, @UserID, @Name ) --業(yè)務(wù)邏輯結(jié)束 IF @@error <> 0 BEGIN ROLLBACK RETURN 0; END COMMIT
SET XACT_ABORT OFF;
RETURN 1 ;
END
調(diào)用的父存儲過程如下：
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
我們執(zhí)行父存儲過程：
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
第一次提交正常，再次執(zhí)行就會出現(xiàn)如下錯誤：
消息 266，級別 16，狀態(tài) 2，過程 innertranv1，第 0 行 EXECUTE 后的事務(wù)計數(shù)指示 BEGIN 和 COMMIT 語句的數(shù)目不匹配。上一計數(shù) = 1，當前計數(shù) = 0。 消息 3903，級別 16，狀態(tài) 1，過程 outertranv2，第 18 行 ROLLBACK TRANSACTION 請求沒有對應(yīng)的 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 --內(nèi)層事務(wù)存儲過程，演示如何處理才能在嵌套的事務(wù)存儲過程中正確處理事務(wù) 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 '未進入子事務(wù)前全局@@TRANCOUNT'+CAST(@TRANCOUNT AS VARCHAR(50)) BEGIN TRAN tran1 --開始事務(wù) SAVE TRAN tranpoint --保存事務(wù)點 SET @TRANCOUNT=(select @@TRANCOUNT) PRINT '進入子事務(wù)后全局@@TRANCOUNT'+CAST(@TRANCOUNT AS VARCHAR(50)) IF(EXISTS(SELECT TOP 1 * FROM dbo.test WHERE ID=@ID)) BEGIN ROLLBACK TRAN tranpoint ; --回滾保存點的事務(wù) COMMIT TRAN tran1 ; --提示當前事務(wù) SET @TRANCOUNT=(select @@TRANCOUNT) PRINT '回滾子事務(wù)后全局@@TRANCOUNT'+CAST(@TRANCOUNT AS VARCHAR(50)) RETURN 0 ; END --業(yè)務(wù)邏輯開始 INSERT dbo.test ( ID, UserID, Name) VALUES ( @ID, @UserID, @Name ) --業(yè)務(wù)邏輯結(jié)束 IF @@error <> 0 BEGIN ROLLBACK TRAN tranpoint ; --回滾保存點的事務(wù) COMMIT TRAN tran1 ; --提示當前事務(wù) SET @TRANCOUNT=(select @@TRANCOUNT) PRINT '回滾子事務(wù)后全局@@TRANCOUNTT'+CAST(@TRANCOUNT AS VARCHAR(50)) RETURN 0; END COMMIT TRAN tran1 ; SET XACT_ABORT OFF; SET @TRANCOUNT=(select @@TRANCOUNT) PRINT '提交子事務(wù)后全局@@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 '未進入父事務(wù)前全局@@TRANCOUNT:'+CAST(@TRANCOUNT AS VARCHAR(50)) BEGIN TRAN SET @TRANCOUNT=(select @@TRANCOUNT) PRINT '進入父事務(wù)后全局@@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 '回滾父事務(wù)后全局@@TRANCOUNT:'+CAST(@TRANCOUNT AS VARCHAR(50)) RETURN ; END COMMIT TRAN SET @TRANCOUNT=(select @@TRANCOUNT) PRINT '提交父事務(wù)后全局@@TRANCOUNT:'+CAST(@TRANCOUNT AS VARCHAR(50))
調(diào)用父存儲過程：
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
結(jié)果如下：
未進入父事務(wù)前全局@@TRANCOUNT:0 進入父事務(wù)后全局@@TRANCOUNT:1 未進入子事務(wù)前全局@@TRANCOUNT:1 進入子事務(wù)后全局@@TRANCOUNT:2 回滾子事務(wù)后全局@@TRANCOUNT:1 回滾父事務(wù)后全局@@TRANCOUNT:0
不會再報"EXECUTE 后的事務(wù)計數(shù)指示 BEGIN 和 COMMIT 語句的數(shù)目不匹配"之類的錯誤了，實際上就是在每個嵌套的子過程中標明當前事務(wù)點，每個子事務(wù) 只提交/回滾 子事務(wù)點，而不是回滾整個事務(wù)！
浙公網(wǎng)安備 33010602011771號