Início / dba / Perguntas / 31279
Accepted
Sky
Asked: 2013-01-02 19:59:11 +0800 CST

Transação e Try-catch no trabalho do SQL Server

  • 772

Temos operações DML em cada etapa de um trabalho do SQL Server. Para garantir que a atualização/inserção seja revertida caso algo dê errado, envolvi as modificações de dados de cada etapaTRY CATCH e TRANSACTIONbloqueei:

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

Ele garante que as manipulações de dados serão revertidas em caso de erro(s)? Ou outras considerações devem ser levadas em conta?

Haveria alguma maneira melhor de fazer isso (usando configurações, etc)?

Obrigada.

sql-server sql-server-2008-r2

2 respostas

  1. Best Answer

    Prefiro recomendar um padrão como o de Exception Handling and Nested Transactions :

    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

    Este padrão verifica o XACT_STATE()bloco catch para proteger contra transações não confirmáveis :

    Transações não confirmáveis ​​e XACT_STATE
    Se um erro gerado em um bloco TRY invalidar o estado da transação atual, a transação é classificada como uma transação não confirmável. Um erro que normalmente encerra uma transação fora de um bloco TRY faz com que uma transação entre em um estado não confirmável quando o erro ocorre dentro de um bloco TRY. Uma transação não confirmável pode executar apenas operações de leitura ou uma ROLLBACK TRANSACTION. A transação não pode executar nenhuma instrução Transact-SQL que geraria uma operação de gravação ou uma COMMIT TRANSACTION. A função XACT_STATE retorna um valor de -1 se uma transação foi classificada como uma transação não confirmável. Quando um lote é concluído, o Mecanismo de Banco de Dados reverte todas as transações ativas não confirmáveis. Se nenhuma mensagem de erro foi enviada quando a transação entrou em um estado não confirmável, quando o lote terminar, uma mensagem de erro será enviada ao aplicativo cliente. Isso indica que uma transação não confirmável foi detectada e revertida.

    Seu código está verificando @@TRANCOUNTem lugares onde não pode ser 0, ele usa uma mistura de mensagens informativas PRINT e conjuntos de resultados SELECT para comunicar o sucesso, ele não manipula erros que são recuperáveis. Idealmente, as exceções devem ser propagadas para o cliente, neste caso, para o trabalho do Agente (ou seja, sua captura deve aumentar novamente).

    • 8

  2. O que você tem parece bom para mim. Eu sugeriria fazer algo com as informações, é claro, depois de reverter a transação, por exemplo, gravá-la em um log.

    • 2

relate perguntas