一个存储过程有3000多行语句我在里面建了三个事务
BEGIN TRANSACTION FeeClac1statement1
.语句
.语句
.语句
.语句
statement1IF(@@ERROR <> 0)
BEGIN
ROLLBACK TRANSACTION FeeClac1
RETURN -1
ENDBEGIN TRANSACTION FeeClac2statement2
.语句
.语句
.语句
.语句
statement2IF(@@ERROR <> 0)
BEGIN
ROLLBACK TRANSACTION FeeClac2
ROLLBACK TRANSACTION FeeClac1
RETURN -1
END
BEGIN TRANSACTION FeeClac3statement3
.语句
.语句
.语句
.语句
statement3IF(@@ERROR <> 0)
BEGIN
ROLLBACK TRANSACTION FeeClac3
ROLLBACK TRANSACTION FeeClac2
ROLLBACK TRANSACTION FeeClac1
RETURN -1
END
我的意图是:
在statement1语句群执行失败时,回滚事务FeeClac1,结束存储过程;
若statement1语句群执行成功,当statement2语句群执行失败时,则回滚事务FeeClac1和事务FeeClac2,结束存储过程;
若statement1和statement2语句群都执行成功,而statement3语句群执行失败时,则回滚事务FeeClac1和事务FeeClac2和事务FeeClac3,结束存储过程。
如果statement1和statement2和statement3语句群都执行成功,就提交三个事务(事务FeeClac1和事务FeeClac2和事务FeeClac3)。请问上面写的对不对?
不对的话,应该怎么写??
要不要加 COMMIT TRANSACTION ?
谢谢!!
BEGIN TRANSACTION FeeClac1statement1
.语句
.语句
.语句
.语句
statement1IF(@@ERROR <> 0)
BEGIN
ROLLBACK TRANSACTION FeeClac1
RETURN -1
ENDBEGIN TRANSACTION FeeClac2statement2
.语句
.语句
.语句
.语句
statement2IF(@@ERROR <> 0)
BEGIN
ROLLBACK TRANSACTION FeeClac2
ROLLBACK TRANSACTION FeeClac1
RETURN -1
END
BEGIN TRANSACTION FeeClac3statement3
.语句
.语句
.语句
.语句
statement3IF(@@ERROR <> 0)
BEGIN
ROLLBACK TRANSACTION FeeClac3
ROLLBACK TRANSACTION FeeClac2
ROLLBACK TRANSACTION FeeClac1
RETURN -1
END
我的意图是:
在statement1语句群执行失败时,回滚事务FeeClac1,结束存储过程;
若statement1语句群执行成功,当statement2语句群执行失败时,则回滚事务FeeClac1和事务FeeClac2,结束存储过程;
若statement1和statement2语句群都执行成功,而statement3语句群执行失败时,则回滚事务FeeClac1和事务FeeClac2和事务FeeClac3,结束存储过程。
如果statement1和statement2和statement3语句群都执行成功,就提交三个事务(事务FeeClac1和事务FeeClac2和事务FeeClac3)。请问上面写的对不对?
不对的话,应该怎么写??
要不要加 COMMIT TRANSACTION ?
谢谢!!
--格式应该类似:set xact_abort onbegin transtatement1
...
statement2
...
statement3commit tran
--这样三组语句要么全部执行,要么不执行。需要设置Set Xact_abort on ,保证事务整体回滚。--如果系统出现严重错误,即使做了判断:IF(@@ERROR <> 0),也不会执行你的代码
问题在事务回滚这里
开始时@@TRANCOUNT=0
结束时@@TRANCOUNT=4我在最后加了一个COMMIT TRANSACTION
然后执行
没报错
create procedure aaaas statement
.语句
.语句
.语句
.语句
statement
BEGIN TRANSACTION FeeClac1statement1
.语句
.语句
.语句
.语句
statement1IF(@@ERROR <> 0)
BEGIN
ROLLBACK TRANSACTION FeeClac1
RETURN -1
ENDBEGIN TRANSACTION FeeClac2statement2
.语句
.语句
.语句
.语句
statement2IF(@@ERROR <> 0)
BEGIN
ROLLBACK TRANSACTION FeeClac2
ROLLBACK TRANSACTION FeeClac1
RETURN -1
END
BEGIN TRANSACTION FeeClac3statement3
.语句
.语句
.语句
.语句
statement3IF(@@ERROR <> 0)
BEGIN
ROLLBACK TRANSACTION FeeClac3
ROLLBACK TRANSACTION FeeClac2
ROLLBACK TRANSACTION FeeClac1
RETURN -1
ENDCOMMIT TRANSACTION
commit transaction FeeClac3
commit transaction FeeClac2
commit transaction FeeClac1
commit transaction FeeClac2
commit transaction FeeClac1
把COMMIT TRANSACTION去掉
...
statement2
...
statement3commit tran
我懂您的意思
但是statement1,statement2,statement3里的语句有2000多行,而且有很多张结果表(存储过程就是计算数据插入结果表)
我以前像您也那样写过,因为语句和所用的表太多,所以执行时间太长了。
为了缩短点执行时间,所以我才想试着把一个事务分成了三个事务的。
CREATE TABLE TestTran (Cola INT PRIMARY KEY, Colb CHAR(3))
GO
BEGIN TRANSACTION OuterTran -- @@TRANCOUNT set to 1.
GO
INSERT INTO TestTran VALUES (1, 'aaa')
GO
BEGIN TRANSACTION Inner1 -- @@TRANCOUNT set to 2.
GO
INSERT INTO TestTran VALUES (2, 'bbb')
GO
BEGIN TRANSACTION Inner2 -- @@TRANCOUNT set to 3.
GO
INSERT INTO TestTran VALUES (3, 'ccc')
GO
COMMIT TRANSACTION Inner2 -- Decrements @@TRANCOUNT to 2.
-- Nothing committed.
GO
COMMIT TRANSACTION Inner1 -- Decrements @@TRANCOUNT to 1.
-- Nothing committed.
GO
COMMIT TRANSACTION OuterTran -- Decrements @@TRANCOUNT to 0.
-- Commits outer transaction OuterTran.
GO
commit transaction FeeClac3
commit transaction FeeClac2
commit transaction FeeClac1谢谢大家帮我解决问题我的确这样加过,但是报错了--错误信息226我最初写的就是这样可是出错了,让我头疼
create procedure aaaas statement
.语句
.语句
.语句
.语句
statement
BEGIN TRANSACTION FeeClac1statement1
.语句
.语句
.语句
.语句
statement1IF(@@ERROR <> 0)
BEGIN
ROLLBACK TRANSACTION FeeClac1
RETURN -1
ENDBEGIN TRANSACTION FeeClac2statement2
.语句
.语句
.语句
.语句
statement2IF(@@ERROR <> 0)
BEGIN
ROLLBACK TRANSACTION FeeClac2
ROLLBACK TRANSACTION FeeClac1
RETURN -1
END
BEGIN TRANSACTION FeeClac3statement3
.语句
.语句
.语句
.语句
statement3IF(@@ERROR <> 0)
BEGIN
ROLLBACK TRANSACTION FeeClac3
ROLLBACK TRANSACTION FeeClac2
ROLLBACK TRANSACTION FeeClac1
RETURN -1
ENDcommit transaction FeeClac3
commit transaction FeeClac2
commit transaction FeeClac1 --commit transaction FeeClac1
--commit transaction FeeClac2
--commit transaction FeeClac3
--这样我也试过的
.语句
.语句
.语句
.语句
statementif
exists (condition)BEGIN TRANSACTION FeeClac1statement1
.语句
.语句
.语句
.语句
statement1IF(@@ERROR <> 0)
BEGIN
ROLLBACK TRANSACTION FeeClac1
RETURN -1
ENDend
BEGIN TRANSACTION FeeClac2statement2
.语句
.语句
.语句
.语句
statement2IF(@@ERROR <> 0)
BEGIN
ROLLBACK TRANSACTION FeeClac2
ROLLBACK TRANSACTION FeeClac1
RETURN -1
END
BEGIN TRANSACTION FeeClac3statement3
.语句
.语句
.语句
.语句
statement3IF(@@ERROR <> 0)
BEGIN
ROLLBACK TRANSACTION FeeClac3
ROLLBACK TRANSACTION FeeClac2
ROLLBACK TRANSACTION FeeClac1
RETURN -1
ENDCOMMIT TRANSACTION又改了一点
加了个IF
END
应该这么改呢?