SqlServer2008中事务使用的一些细节 – Hello World – CSDN博客 -Mikel

SqlServer2008中事务使用的一些细节 – Hello World – CSDN博客

2019年3月5日 分类: 数据库

来源: SqlServer2008中事务使用的一些细节 – Hello World – CSDN博客

测试存储过程代码时候碰到了一个事务的细节问题,是在使用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语句中的回滚

———————
作者:数据的流
来源:CSDN
原文:https://blog.csdn.net/shujudeliu/article/details/78783613
版权声明:本文为博主原创文章,转载请附上博文链接!


标签:
本文的评论功能被关闭了.
备案信息冀ICP 0007948