我们在写事务时经常遇到的问题如下:
消息 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 语句的数目不匹配"之类的错误了,实际上就是在每个嵌套的子过程中标明当前事务点,每个子事务 只提交/回滚 子事务点,而不是回滚整个事务!