Para finalizar a sequência de posts anteriores sobre como tratar erros no SQL Server, este post tem como objetivo mostrar melhores soluções com maiores possibilidades de tratamentos.
Como as transações no SQL Server possuem até três status quando executadas, o que iremos ver neste último é como construir uma solução que evite situações de lock ou tratamento inadequado no banco de dados.
XACT_ABORT E TRATAMENTO DE ERROS
A função do XACT_ABORT é fechar qualquer transação que tenha escapado ou que por algum motivo, não haja uma cláusula que a finalize. Por essa razão, é de extrema importância que toda procedure do seu banco tenha essa função na sua sintaxe.
Um breve exemplo:
Como vimos, temos duas transações esperando por um commit ou rollback.
Agora, perceba que nem sempre o throw fechará uma transação, veja este novo exemplo.
Ainda que tenham sido executadas no mesmo ‘bloco’ o throw não encerrou a segunda execução e mais, não podemos simplesmente adicioná-lo neste por já termos um no primeiro. Somente se utilizássemos todo o bloco de try..catch.
Este exemplo serve para corroborar o uso do XACT_ABORT.
Por padrão o XACT_ABORT é habilitado, mas como em algumas situações o comportamento da query pode ser imprevisível, devemos utilizá-lo. Veja o segundo resultado circulado em azul.
Como o SQL Server herdou algumas funções de versão legado e uma delas trata exatamente sobre o que o XACT_ABORT faz, devemos sempre declarar na criação da procedure.
E mesmo quando há erro com try..catch vimos situações como no exemplo da procedure com erro de compilação que ele não trata e isso significa que toda a batch da procedure seria executada.
Cuidado ao utilizar raiserror em transações pois tanto o xact_abort quanto o rollback não se comportam de forma correta.
VERIFICANDO STATUS DE TRANSAÇÃO – TRATANDO ERRO
O SQL Server entende que existem três formas de lidar com uma determinada transação:
- Open
- Open and commitable
- Transaction is doomed
A única transação que não permite rollback ou commit e que se perde ‘para sempre’ é a transaction is doomed.
Veja a execução desta query e repare o resultado, a query deixa uma transação aberta. Perceba também que o xact_abort também está com status off.
O que ocorre nesta query é que o primeiro bloco quando executado, retorna erro por conta da divisão por 0.
Como existe um try..catch o SQL Server avança direto para o tratamento e não finaliza a transação. Ao chegar no tratamento ele colhe o status da transação, printa e conta quantas transações abertas existem.
Mesmo com o if criando uma condição lógica para ele aplicar o rollback na transação, não é possível pois o rollback não encontra a transação correspondente, já que a transação está no bloco try.
Ainda que habilite o xact_abort a transação continuará aberta, mas com status de perdida
Como as transações se comportam de forma diferente e sendo que algumas delas são tratadas como transações perdidas (doomed) a melhor forma de evitar esse tipo de problema e fechar a transação é utilizando throw.
Ainda que não seja possível voltar uma transação perdida ou desfazer uma alteração após o status de doomed, lembre-se que a ideia aqui é não deixar nenhuma operação aberta para não causar lock na tabela e impedir o uso.
MELHORANDO A CRIAÇÃO DA PROCEDURE
Nesta procedure de teste que irei mostrar no exemplo, vamos fazer um insert simples mas inverso. Sempre que houver um insert A,B, terá um B,A.
Veja que já começamos utilizando o XACT_ABORT para abortar qualquer transação que não tenha sido devidamente fechada.
Nota: o return ao final não tem grandes utilidades. É uma segurança a mais caso haja uso de alguma procedure legado que tenha sido criada antes da utilização do try..catch.
Resultado final da procedure executada.
Como podemos ver, o tratamento está bem genérico, informando apenas que houve um erro na transação por violação do campo com primary key.
E se quiséssemos um tratamento com maior detalhe?!
Respondendo a pergunta acima, podemos criar uma transação com tratamento de erros para executar a primeira procedure e uma outra para executar o comando de insert. Veja que o resultado será mais polido ao final.
Analisando o erro da execução acima e comparando com o primeiro exemplo, o que temos aqui é um resultado de erro mais refinado, por que?!
Cito alguns motivos:
- Temos na primeira linha o nome da procedure que acusou o erro.
- Temos o motivo pelo qual a procedure acusou o erro.
- O tipo de erro retornado: ‘duplicate primary key’
- Os valores que foram passados no insert duplicados.
Isso tudo porque aninhamos a procedure EXEC_INSERT dentro de uma transação e como o tratamento de erro é feito para transações, temos um retorno mais apurado.
UTILIZANDO A PROCEDURE ERROR_HANDLER_SP
No tópico anterior e nos exemplos mostrados entendemos como tratar ou obter erros do banco de dados com informações mais precisas a respeito do que houve de errado na execução da transação na procedure.
Ainda que o tratamento acima tenha se mostrado efetivo, o que obtemos não foi uma visão correta dos erros que podem ocorrer ou da procedure exata que o erro aconteceu. Quando temos rotinas muito complexas, essa falta de precisão pode ser um problema sério no debug do código.
Assim sendo, vamos refinar ainda mais o tratamento de erro utilizando a procedure error_handler_sp.
Nota: essa procedure não é uma procedure de sistema do banco, mas sim, criada pelo usuário.
Veja a procedure criada abaixo:
E abaixo, um pequeno exemplo do uso da procedure handler_sp.
Este foi o terceiro post da série sobre tratamento de erros utilizando SQL.
Pretendo em outro momento retornar neste assunto com novas abordagens e conteúdos mais avançados sobre para completar a série.
Como podemos ver, o SQL Server possui bons recursos de implementação a esse respeito e cerca de 90-95% dos casos podem ser simplesmente tratados com a procedure handler_sp.
Não se esqueçam e observem bem a partir de agora, como suas transações serão construídas para não permanecerem em aberto devido comportamentos ou erros inesperados.
Refinem suas procedures com esse recurso como forma de melhorar seus códigos e dar um tratamento adequado quando algo não sair na forma que se espera.
Espero que tenham gostado e que possa ajudá-los em seus códigos de agora em diante!
Saúde!
ManageBI 