大家一般是怎么办trans的提交和回滚的啊有标准的办法么

2012-05-20

大家一般是怎么处理trans的提交和回滚的啊。有标准的办法么？下面我写的这个感觉太十三了吧？!CREATE PROCEDU

大家一般是怎么处理trans的提交和回滚的啊。有标准的办法么？
下面我写的这个感觉太十三了吧？!

CREATE PROCEDURE [dbo].aspnet_Vstock_Order_UserReset
@UserId uniqueidentifier,
@fMoney decimal(38,5),
@ErrorStr varchar(100) out
AS
BEGIN

declare @iError int

select @ErrorStr = ''
select @iError = 0

set xact_abort on
begin tran

delete from dbo.aspnet_Vstock_BuyOrder where UserId = @UserId
select @iError = @iError + @@Error
delete from dbo.aspnet_Vstock_Deal where UserId = @UserId
select @iError = @iError + @@Error
delete from dbo.aspnet_Vstock_DealHistory where UserId = @UserId
select @iError = @iError + @@Error
delete from dbo.aspnet_Vstock_SellOrder where UserId = @UserId
select @iError = @iError + @@Error
Update dbo.aspnet_Users set Vstock_TotalMoney = @fMoney where UserId = @UserId
select @iError = @iError + @@Error

if @iError = 0
begin
commit
print '--------001@\r\n'
end
else
begin
rollback
print '--------001*\r\n'
end

RETURN 0
END
GO

[解决办法]

SQL code

create proc pr_Test as begin     begin tran     begin try                      ......           commit tran     end try     begin catch           rollback tran     end catch     end
[解决办法]
我一般只在分布式环境中使用set xact_abort on.
在非分布式环境我一般用事务保存点.在2005里都用try...catch.
SQL codeCREATE PROCEDURE [dbo].aspnet_Vstock_Order_UserReset    @UserId UNIQUEIDENTIFIER,    @fMoney DECIMAL(38, 5),    @ErrorStr VARCHAR(100) OUTASBEGIN    SET NOCOUNT ON;    DECLARE @trancount INT    SELECT @ErrorStr = '',  @trancount = @@TRANCOUNT    --外部应用程序无外层事务时才启动事务,否则只使用事务保存点    IF @trancount = 0         BEGIN TRAN    ELSE        SAVE TRAN Tran1    BEGIN TRY        DELETE         FROM   dbo.aspnet_Vstock_BuyOrder        WHERE  UserId = @UserId                DELETE         FROM   dbo.aspnet_Vstock_Deal        WHERE  UserId = @UserId                        DELETE         FROM   dbo.aspnet_Vstock_DealHistory        WHERE  UserId = @UserId                        DELETE         FROM   dbo.aspnet_Vstock_SellOrder        WHERE  UserId = @UserId                        UPDATE dbo.aspnet_Users        SET    Vstock_TotalMoney = @fMoney        WHERE  UserId = @UserId        --提交事务.若有外层事务,则提交操作交给外层事务.        IF @trancount = 0  COMMIT    END TRY    BEGIN CATCH        --若无外层事务,则回滚前面创建的事务        IF @trancount = 0              ROLLBACK        ELSE IF XACT_STATE()<>-1            --若有外层事务,只则回滚到上一个事务保存点            ROLLBACK TRAN tran1        SELECT @ErrorStr = ERROR_MESSAGE()        RAISERROR(@ErrorStr, 16, 1)    END CATCH    RETURN 0ENDGO

热点排行

SQL Server

大家一般是怎么办trans的提交和回滚的啊有标准的办法么