我有几个存储过程作如下定义:
create procedure test1
@ret int output
as
set @ret=-1
begin tran in_test
insert.....
if @@error<>0
roolback tran in_test
else
begin
set @ret=0
commit tran in_test
end
------------------------------
create procedure test2
as
declare @outvalue
set @outvalue=0
begin tran out_test
exec test1 @ret=@outvalue
if ....
else ....
.......
exec ... -------其他类似test1结构的存储过程,其中有的还嵌套存储过程
....
if @outvalue=0
commit tran out_test
else
roolback tran out_test
----------------------------------------------------
目的:
我用@outvalue来传递是否正常结束,一旦有一个存储过程执行过程发生问题,则全部回滚
条件:
1.回答时全部@outvalue值变化的逻辑关系不考虑,仅考虑事务
2.由于test1要单独使用,因此必须使用事务
3.不要解释错误内容,我知道
问题:
当存储过程test1发生回滚时,会报错
服务器: 消息 6401,级别 16,状态 1,过程 test1,行 xx
无法回滚 in_test。没有找到任何该名称的事务或保存点。
服务器: 消息 266,级别 16,状态 2,过程 test1,行 xxx
EXECUTE 后的事务计数指出缺少了 COMMIT 或 ROLLBACK TRANSACTION 语句。原计数 = 1,当前计数 = 2。
请问如何控制事务使其在回滚后保证EXECUTE 后的事务计数正常
---------------------------------------------------------------
如果要让事务回滚到指定的位置需要在事务中设定Save Point point_name.
---------------------------------------------------------------
是否可以设置一个保存点
create procedure test2
as
declare @outvalue
set @outvalue=0
begin tran out_test
SAVE TRANSACTION @POINT1
exec test1 @ret=@outvalue
if @ERROR<>0
ROLLBACK TRANSACTION @POINT1
RETURN @ERROR
else ....
.......
exec ... -------其他类似test1结构的存储过程,其中有的还嵌套存储过程
....
if @outvalue=0
commit tran out_test
else
roolback tran out_test
---------------------------------------------------------------
忘记提醒您:
嵌套存储过程中,要非常注意ROLLBACK TRANSACTION
最好加一个语句
IF @@TRANCOUNT>O
RILLBACK TRANSACTION
---------------------------------------------------------------
标准方案,但是没有测试,试一试。
定义被调用存储过程
create procedure test1
@ret int output
as
/增加部分,处理事务嵌套/
declare
@trncnt int /事务记数/
select @trncnt = @@trancount
if @trncnt = 0 /判断事务记数,根据情况确定使用保存点或者新建一个事务/
begin tran p1
else
save tran p1
/增加部分结束/
set @ret=-1
begin tran in_test
insert.....
if @@error<>0
begin
set @ret = -1 /返回错误/
rollback tran p1 /此处修改/
else
begin
set @ret=0
commit tran
return
调用的存储过程
/这里如果没有再被嵌套,就不用改了,否则可以类似上边的处理方式/
create procedure test2
as
declare @outvalue
set @outvalue=0
begin tran out_test
exec test1 @ret=@outvalue
if ....
else ....
.......
exec ... -------其他类似test1结构的存储过程,其中有的还嵌套存储过程
....
if @outvalue=0
commit tran out_test
else
rollback tran out_test
---------------------------------------------------------------
ROLLBACK TRANSACTION 语句的 transaction_name 参数引用一组命名的嵌套事务的内层事务是非法的,transaction_name 只能引用最外部事务的事务名。如果在一组嵌套事务的任意级别执行使用外部事务名称的 ROLLBACK TRANSACTION transaction_name 语句,那么所有的嵌套事务都将回滚。如果在一组嵌套事务的任意级别执行没有 transaction_name 参数的 ROLLBACK WORK 或 ROLLBACK TRANSACTION 语句,那么它将回滚所有嵌套事务,包括最外部事务。
这是联机丛书上说的,就是说你应该在ROLLBACK的时候只能指定一个ROLLBACK TRANSACTION TRANS_NAME 而在内层无法使用ROLLBACK TRANSACTION TRANS_NAME 。这就是我的理解。而你再第一个存储过程中ROLLBACK TRANSACTION TRANSNAME 就会报错!