关于调用方有事务,被调用的SP中也有事务,在嵌套SP中回滚代码的报错处理,好文推荐
对于本文阐述的事务保存点的问题,大部分情况下应该是可行的,但我发现使用.net的System.Transactions.TransactionScope ts = new TransactionScope(TransactionScopeOption.Required, options)内部再调用包含事务处理的SP时,有时还是会有其他报错,为了一了百了的应对各种情况,其实可以在SP中判断全局变量@@TransactionCount的取值,因为如果.net调用方启用了事务去调用SP时,在SP中查看@@TransactionCount的值就是=1的,所以这时候可以在SP中进行判断,如果@@TransactionCount>0,说明调用该SP的调用方那边有启用事务,SP中就不用再启用事务了,但如果@@TransactionCount=0,则说明调用该SP的调用方那边没有启用事务,这时候在SP中就可以启用事务进行处理了,一般的处理如下:
BEGIN TRY
DECLARE @TranStarted BIT = 0;
IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0
--其他操作的SQL
PRINT '=====================SUCCESS==========================='
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END
SELECT 1 AS Result;
END TRY
BEGIN CATCH
PRINT '=====================ERROR==========================='
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
DECLARE @msg NVARCHAR(2048);
DECLARE @line INT;
DECLARE @severity INT;
DECLARE @state INT;
SELECT @msg = ERROR_MESSAGE(),
@line = ERROR_LINE(),
@severity = ERROR_SEVERITY(),
@state = ERROR_STATE();
SET @msg
= 'sp_name发生异常, tradeDate=' + @tradeDate
+ ',companyID=' + CAST(@companyID AS NVARCHAR(50)) + ', line=' + CAST(@line AS NVARCHAR(10)) + ', msg='
+ @msg;
SELECT 0 AS Result,
@msg AS errMsg;
END CATCH;
SQL报错异常:Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.
--首先明确一点,在SQL中开启事务时,Begin Tran时,@@TRANCOUNT会加1,Commit Tran时@@TRANCOUNT会减1,但是当ROLLBACK TRAN时会把@@TranCount直接设置为0,
--对于外部有开启事务的SQL代码来说,如果调用的嵌套SP中也有开启事务的话,进到嵌套SP中的时候,@@tranCount=1(假如只要一层嵌套,
--此处等于1是因为外部调用该SP的地方使用了Begin Tran来开启了一个事务),但是如果在嵌套SP中使用了ROLLBACK TRAN回滚代码时,会把@@tranCount直接设置为0,
--在该嵌套SP中调用结束时就会导致@@tranCount的数量进来和出去时不一致了,SQL就会报错了,所以在嵌套SP中如果需要回滚,应使用回滚事务保存点,而不是ROLLBACK TRAN。
--如果在嵌套SP中如果需要回滚,则应该使用ROLLBACK TRAN pp,
--如果直接使用ROLLBACK TRAN,则执行ROLLBACK TRAN后,会把@@TRANCOUNT直接设置为0,
--这样如果外层调用该SP的地方有Begin Tran的话,会导致SQL报出类似于
--“Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.”的异常,
--因为进入该方法时,外部sql的Begin tran会使@@TranCount加1,但执行此处的ROLLBACK TRAN后,会把@@TRANCOUNT直接设置为0,
--结束调用该SP返回时,事务的数量调用该SP和结束时就不一样了,所以SQL报异常了。
--解决此问题的方法就是在嵌套的SP中如果有事务,则在嵌套SP中,开启事务的地方保存事务点,如save tran pp;
--在Catch中或需要回滚代码的地方加上ROLLBACK TRAN pp;就是回滚当前的事务代码,这个代码不会改变@@TranCount的值。
--然后再写COMMIT TRAN;,这样就会把嵌套的SP中对应的BEGIN TRAN给释放了,即BEGIN TRAN使@@TranCount加1,COMMIT TRAN;使@@TranCount减1;
--这样调用和结束调用嵌套SP时的@@TranCount数量就一致了,就不会报上面的异常了。你没有看错,是在嵌套的SP中回滚时使用回滚事务保存点,
--且需要Commint事务才可以正常运行。也就是说,如果调用的外部代码有起事务,且在嵌套SP中也有事务的话,嵌套SP中的事务不管提交还是回滚,都需要提交事务才可以,不然就会报上面的错误。
--实践证明,调用嵌套SP的地方不止是SQL的sp会有这个问题,C#代码中起一个事务,再来调用有事务处理的嵌套SP,
--在嵌套SP中直接写回滚语句ROLLBACK TRAN也会有相同问题,都可以使用回滚事务点来解决。切记嵌套的SP中使用事务时,不管是否回滚都需要提交事务才可以。
--当然,如果调用方的SP或SQL中没有起事务,被调用的SP中有事务,则使用ROLLBACK TRAN没有任何影响,嵌套事务都有起事务一定要特别注意,确实是实践出真知。
--补充一点,回滚事务保存点ROLLBACK TRAN pp时,只会回滚从save tran pp到ROLLBACK TRAN pp中间的代码处理,其他部分的代码段还是能正常提交上去的
--因此使用回滚事务保存点的sql时,一定要把需要回滚的代码放在save tran pp到ROLLBACK TRAN pp的中间,如果在这个外面就不会回滚了,而会提交上去,因为后面有Commit Tran语句执行。
--另外,经测试,ROLLBACK TRAN pp回滚事务保存点的SQL,在单个SP单独执行时也一样适用,使用此方法回滚事务可能更严谨一点。
---创建测试表 IF EXISTS ( SELECT * FROM sys.tables WHERE name = 'tt' ) DROP TABLE dbo.tt ; CREATE TABLE dbo.tt ( ID INT IDENTITY , Name NVARCHAR (100), TransCount INT ) ; GO --查询结果 SELECT * FROM dbo.tt ---创建子存储过程 IF EXISTS ( SELECT * FROM sys.procedures WHERE name = 'S_proc' ) DROP PROC S_proc ; GO CREATE PROC S_proc AS BEGIN PRINT('s-init-' + CAST(@@TRANCOUNT AS NVARCHAR(10))); BEGIN TRAN; save tran pp; PRINT('s-prev-' + CAST(@@TRANCOUNT AS NVARCHAR(10))); DECLARE @p2 INT = @@TRANCOUNT; INSERT INTO dbo.tt ( Name, TransCount ) SELECT '查询2', @p2 ; --如果此处要回滚,则应该使用ROLLBACK TRAN pp, --如果直接使用ROLLBACK TRAN,则执行ROLLBACK TRAN后,会把@@TRANCOUNT直接设置为0, --这样如果外层调用该SP的地方有Begin Tran的话,会导致SQL报出类似于 --“Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.”的异常, --因为进入该方法时,外部sql的Begin tran会使@@TranCount加1,但执行此处的ROLLBACK TRAN后,会把@@TRANCOUNT直接设置为0, --结束调用该SP返回时,事务的数量调用该SP和结束时就不一样了,所以SQL报异常了。 --解决此问题的方法就是在嵌套的SP中如果有事务,则在嵌套SP中,开启事务的地方保存事务点,如save tran pp; --在Catch中或需要回滚代码的地方加上ROLLBACK TRAN pp;就是回滚当前的事务代码,这个代码不会改变@@TranCount的值。 --然后再写COMMIT TRAN;,这样就会把嵌套的SP中对应的BEGIN TRAN给释放了,即BEGIN TRAN使@@TranCount加1,COMMIT TRAN;使@@TranCount减1; --这样调用和结束调用嵌套SP时的@@TranCount数量就一致了,就不会报上面的异常了。你没有看错,是在嵌套的SP中回滚时使用回滚事务保存点, --且需要Commint事务才可以正常运行。也就是说,如果调用的外部代码有起事务,且在嵌套SP中也有事务的话,嵌套SP中的事务不管提交还是回滚,都需要提交事务才可以,不然就会报上面的错误。 --实践证明,调用嵌套SP的地方不止是SQL的sp会有这个问题,C#代码中起一个事务,再来调用有事务处理的嵌套SP, --在嵌套SP中直接写回滚语句ROLLBACK TRAN也会有相同问题,都可以使用回滚事务点来解决。切记嵌套的SP中使用事务时,不管是否回滚都需要提交事务才可以。 --当然,如果调用方的SP或SQL中没有起事务,被调用的SP中有事务,则使用ROLLBACK TRAN没有任何影响,嵌套事务都有起事务一定要特别注意,确实是实践出真知。
--补充一点,回滚事务保存点ROLLBACK TRAN pp时,只会回滚从save tran pp到ROLLBACK TRAN pp中间的代码处理,其他部分的代码段还是能正常提交上去的
--因此使用回滚事务保存点的sql时,一定要把需要回滚的代码放在save tran pp到ROLLBACK TRAN pp的中间,如果在这个外面就不会回滚了,而会提交上去,因为后面有Commit Tran语句执行。
--另外,经测试,ROLLBACK TRAN pp回滚事务保存点的SQL,在单个SP单独执行时也一样适用,使用此方法回滚事务可能更严谨一点。
--切记此处不能直接写ROLLBACK TRAN;而应该写下面的回滚事务保存点的ROLLBACK TRAN pp; ROLLBACK TRAN pp; PRINT('s-after-rollback-' + CAST(@@TRANCOUNT AS NVARCHAR(10))); --切记此处不管是否回滚,都需要提交事务,否则@@TRANCOUNT不会减1,而起事务时@@TRANCOUNT加1了,就会导致进来和出去的事务数量不一致的错误了。 COMMIT TRAN; PRINT('s-after-' + CAST(@@TRANCOUNT AS NVARCHAR(10))); RETURN ; END ; ---创建主存储过程 IF EXISTS ( SELECT * FROM sys.procedures WHERE name = 'P_proc' ) DROP PROC P_proc ; GO CREATE PROC P_proc AS BEGIN DELETE FROM dbo.tt; PRINT('p-init-' + CAST(@@TRANCOUNT AS NVARCHAR(10))); BEGIN TRAN ; PRINT('p-prev-' + CAST(@@TRANCOUNT AS NVARCHAR(10))); DECLARE @p1 INT = @@TRANCOUNT; INSERT INTO dbo.tt ( Name, TransCount ) SELECT '查询1', @p1 ; PRINT('p-after-insert-' + CAST(@@TRANCOUNT AS NVARCHAR(10))) EXEC dbo.S_proc ; PRINT('p-after-' + CAST(@@TRANCOUNT AS NVARCHAR(10))); COMMIT TRAN; PRINT('p-last-' + CAST(@@TRANCOUNT AS NVARCHAR(10))); RETURN ; END ; GO
--使用了保存点的回滚的测试SP
ALTER PROC S_saveTranBeforeandAfterTest
AS
BEGIN
PRINT('s-init-' + CAST(@@TRANCOUNT AS NVARCHAR(10)));
BEGIN TRAN;
PRINT('s-prev-' + CAST(@@TRANCOUNT AS NVARCHAR(10)));
DECLARE @p2 INT = @@TRANCOUNT;
INSERT INTO dbo.tt ( Name, TransCount ) SELECT '查询2-before save', @p2 ;
save tran pp;
INSERT INTO dbo.tt ( Name, TransCount ) SELECT '查询2-after save', @p2 ;
--补充一点,回滚事务保存点ROLLBACK TRAN pp时,只会回滚从save tran pp到ROLLBACK TRAN pp中间的代码处理,其他部分的代码段还是能正常提交上去的
--因此使用回滚事务保存点的sql时,一定要把需要回滚的代码放在save tran pp到ROLLBACK TRAN pp的中间,如果在这个外面就不会回滚了,而会提交上去,因为后面有Commit Tran语句执行。
--另外,经测试,ROLLBACK TRAN pp回滚事务保存点的SQL,在单个SP单独执行时也一样适用,使用此方法回滚事务可能更严谨一点。
--切记此处不能直接写ROLLBACK TRAN;而应该写下面的回滚事务保存点的ROLLBACK TRAN pp;
ROLLBACK TRAN pp;
PRINT('s-after-rollback-' + CAST(@@TRANCOUNT AS NVARCHAR(10)));
--切记此处不管是否回滚,都需要提交事务,否则@@TRANCOUNT不会减1,而起事务时@@TRANCOUNT加1了,就会导致进来和出去的事务数量不一致的错误了。
COMMIT TRAN;
PRINT('s-after-' + CAST(@@TRANCOUNT AS NVARCHAR(10)));
RETURN ;
END ;