SqlServer2008中事务使用的一些细节

测试存储过程代码时候碰到了一个事务的细节问题,是在使用if else语句时候,报错begin tran和commit tran不配对,以前使用时候并没怎么留意这些小细节,所以出现了一些想当然的错误

存储过程如下:

Alter PROCEDURE [dbo].[proc_test] 
	@id int,
	@result	INT=-1 OUTPUT,
	@msg AS varchar(50) OUTPUT
AS
BEGIN
	BEGIN TRAN
	BEGIN TRY
		DECLARE @num INT=0
		SELECT  @num=num from temp_num where id=@id
		IF (@num=0)
		BEGIN
			insert into temp_num values(floor(rand()*200)-100);--插入-100到100的随机数
			SET @msg='处理成功'
			SET @result=1
		END
		ELSE
		BEGIN
			SET @msg='处理失败'
			SET @result=0
			RAISERROR(@msg,16,1)
		END
	COMMIT TRAN
	END TRY
	BEGIN CATCH
		SET @msg=ERROR_MESSAGE()
		SET @result=0
		ROLLBACK TRAN
	END CATCH
	
	RETURN	@result
END

现在因为业务需求,增加了一个else if分支,调整后如下:

Alter PROCEDURE [dbo].[proc_test] 
	@id int,
	@result	INT=-1 OUTPUT,
	@msg AS varchar(50) OUTPUT
AS
BEGIN
	BEGIN TRAN
	BEGIN TRY
		DECLARE @num INT=0
		SELECT  @num=num from temp_num where id=@id
		IF (@num=0)
		BEGIN
			insert into temp_num values(floor(rand()*200)-100);--插入-100到100的随机数
			SET @msg='处理成功'
			SET @result=1
		END
		ELSE IF(@num<0)
		BEGIN
			update temp_num set num=-1*num where id=@id;--将负数变正
			ROLLBACK TRAN
		END
		ELSE
		BEGIN
			SET @msg='处理失败'
			SET @result=0
			RAISERROR(@msg,16,1)
		END
	COMMIT TRAN
	END TRY
	BEGIN CATCH
		SET @msg=ERROR_MESSAGE()
		SET @result=0
		ROLLBACK TRAN
	END CATCH
	
	RETURN	@result
END
那么问题来了,该存储过程执行到新增的else if分支就直接报错了“ROLLBACK TRANSACTION 请求没有对应的 BEGIN TRANSACTION。”

ELSE IF(@num<0)
BEGIN
update temp_num set num=-1*num where id=@id;--将负数变正
ROLLBACK TRAN
END

写c#代码时候

public int TestMethod()

{

if(a>0)

{

a=a+1;

}

else if(a<0)

{

return 0;

}

else

{

a=a-1;

}

return 1;

}

c#中这么写,没毛病,编译器也不会报错,但是想当然的把begin tran、commit tran/rollback tran当成return这样的用就有问题了,sqlserver语法检查通不过!

begin tran 和 commit tran、rollback tran的配对,在碰到if else时候,如果你在分支里写了commit tran、rollback tran,那么每个分支都需要提交或者回滚,不能部分写分支里,部分写if else语句结束之后

所以调整后的存储过程

Alter PROCEDURE [dbo].[proc_test] 
	@id int,
	@result	INT=-1 OUTPUT,
	@msg AS varchar(50) OUTPUT
AS
BEGIN
	BEGIN TRAN
	BEGIN TRY
		DECLARE @num INT=0
		SELECT  @num=num from temp_num where id=@id
		IF (@num=0)
		BEGIN
			insert into temp_num values(floor(rand()*200)-100);--插入-100到100的随机数
			SET @msg='处理成功'
			SET @result=1
			COMMIT TRAN
		END
		ELSE IF(@num<0)
		BEGIN
			update temp_num set num=-1*num where id=@id;--将负数变正
			COMMIT TRAN
		END
		ELSE
		BEGIN
			ROLLBACK TRAN
			SET @msg='处理失败'
			SET @result=0
			RAISERROR(@msg,16,1)
		END
	
	END TRY
	BEGIN CATCH
		SET @msg=ERROR_MESSAGE()
		SET @result=0
		ROLLBACK TRAN
	END CATCH
	
	RETURN	@result
END

也就是说,如果你在分支里使用了commit、rollback,那么每个分支里都必须提交或者回滚,并且if else语句之后不能再次提交或者回滚。这样写起来比较麻烦,其实最好的做法是提前定义一个变量,if else中对变量赋值,结尾判断值再进行提交或者回滚操作。

此外,begin tran写在begin if之内或者之外都不影响catch语句中的回滚













没有更多推荐了,返回首页