主页 / dba / 问题 / 31279
Accepted
Sky
Asked: 2013-01-02 19:59:11 +0800 CST

SQL Server 作业中的事务和 Try-catch

  • 772

我们在 SQL Server 作业的每个步骤中都有 DML 操作。为了确保在出现问题时回滚更新/插入，我已将每个步骤的数据修改包装在TRY CATCH和TRANSACTION块中：

BEGIN TRY
    BEGIN TRANSACTION

        [[INSERT/update statements]] ...

    IF @@TRANCOUNT > 0
    BEGIN
        COMMIT TRANSACTION
        PRINT 'Successful.'
    END

END TRY

BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage

    IF @@TRANCOUNT > 0
    BEGIN
        ROLLBACK TRANSACTION
        PRINT 'Unsuccessful.'
    END
END CATCH

它是否确保在发生错误时回滚数据操作？还是应该考虑其他因素？

会有更好的方法（使用配置等）吗？

谢谢你。

sql-server sql-server-2008-r2

2 个回答

  1. Best Answer

    我宁愿推荐一种模式，比如异常处理和嵌套事务中的模式：

    create procedure [usp_my_procedure_name]
as
begin
    set nocount on;
    declare @trancount int;
    set @trancount = @@trancount;
    begin try
        if @trancount = 0
            begin transaction
        else
            save transaction usp_my_procedure_name;

        -- Do the actual work here

lbexit:
        if @trancount = 0   
            commit;
    end try
    begin catch
        declare @error int, @message varchar(4000), @xstate int;
        select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
        if @xstate = -1
            rollback;
        if @xstate = 1 and @trancount = 0
            rollback
        if @xstate = 1 and @trancount > 0
            rollback transaction usp_my_procedure_name;

        raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
    end catch   
end

    此模式检查XACT_STATE()catch 块中的 以防止不可提交的事务：

    不可提交事务和 XACT_STATE
    如果 TRY 块中产生的错误导致当前事务的状态无效，则该事务被归类为不可提交事务。当错误发生在 TRY 块内时，通常在 TRY 块外结束事务的错误会导致事务进入不可提交状态。不可提交的事务只能执行读取操作或 ROLLBACK TRANSACTION。该事务不能执行任何会生成写操作或 COMMIT TRANSACTION 的 Transact-SQL 语句。如果事务已被归类为不可提交事务，则 XACT_STATE 函数返回值 -1。批处理完成后，数据库引擎会回滚任何活动的不可提交事务。如果事务进入不可提交状态时没有发送错误消息，批处理完成后，将向客户端应用程序发送一条错误消息。这表示检测到并回滚了一个不可提交的事务。

    您的代码正在检查@@TRANCOUNT它不能为 0 的地方，它混合使用信息性 PRINT 消息和 SELECT 结果集来传达成功，它不处理可恢复的错误。理想情况下，异常应该传播到客户端，在这种情况下传播到代理作业（即，您的捕获应该重新引发）。

    • 8

  2. 你所拥有的对我来说看起来不错。我建议您在回滚事务后对这些信息进行一些处理，例如将其写入日志。

    • 2

相关问题