Início / dba / Perguntas / 290443
Accepted
youcantryreachingme
Asked: 2021-04-26 15:31:20 +0800 CST

Por que parte da transação é confirmada quando o procedimento armazenado falha?

  • 772

Eu tenho um procedimento armazenado que começa declarando algumas variáveis ​​e depois contém begin tran;Depois disso, ele executa algumas validações nos argumentos fornecidos (e incrementa uma contagem de erros cada vez que um argumento fornecido falha na validação). Se não houver contagem de erros, procede-se a realizar 7 inserções. Na sequência disso, temcommit tran;

Recentemente eu adicionei uma 8ª inserção à lista. Uma conversão de tipo implícita significava que alguns dados inseridos seriam truncados se inseridos. Isso gerou um erro na tela do SSMS, mas descobri que as primeiras 7 inserções foram confirmadas enquanto a 8ª obviamente não foi concluída.

Eu aprecio que eu poderia incluir um try ... catchbloco para lidar com erros, mas se um explícito begin tran;não tornar todo o bloco de trabalho autônomo até o commit, então qual é o objetivo? O que eu perdi?

Entendo que talvez eu pudesse ter envolvido minha chamada de procedimento em uma transação nesse nível - mas alguém pode explicar o que está acontecendo e por que begin tranparece desrespeitado quando incluído no corpo do procedimento? Se chamar o procedimento inicia uma transação implícita, então uma etapa com falha no proc não deveria reverter todas as alterações efetuadas pelo proc - mesmo sem incluir explicitamente begin tranno corpo do proc?

sql-server stored-procedures

2 respostas

  1. Best Answer

    As transações não revertem automaticamente em caso de erro - não é para isso que elas foram projetadas. Eles são projetados para dar a você a capacidade de reverter. No entanto, você ainda precisa fazer algo para que isso aconteça.

    Como você mencionou, você pode fazer isso acontecer através do TRY...CATCH, o que lhe dá mais controle sobre se e como você pode reverter.

    Parece que você está esperando o comportamento de SET XACT_ABORT ON, que pode ser definido em seu procedimento armazenado, mas não é o comportamento padrão. A descrição da configuração XACT_ABORTon vs off dos documentos é:

    Quando SET XACT_ABORT estiver ON, se uma instrução Transact-SQL gerar um erro em tempo de execução, toda a transação será encerrada e revertida.

    Quando SET XACT_ABORT está OFF, em alguns casos, apenas a instrução Transact-SQL que gerou o erro é revertida e a transação continua o processamento. Dependendo da gravidade do erro, toda a transação pode ser revertida mesmo quando SET XACT_ABORT estiver OFF. OFF é a configuração padrão em uma instrução T-SQL, enquanto ON é a configuração padrão em um gatilho.

    Quando SET XACT_ABORTis ON, chamar um procedimento com falha retorna mensagens no SSMS como:

    Msg 8152, Level 16, State 14, Procedure spProcName, Line 298 [Batch Start Line 5]
String or binary data would be truncated.

Completion time: 2021-04-26T10:51:00.8420902+10:00

    A primeira linha está em vermelho no SSMS.

    Quando SET XACT_ABORTis OFF, chamar um procedimento com falha inclui uma mensagem adicional:

    Msg 8152, Level 16, State 14, Procedure spProcName, Line 298 [Batch Start Line 5]
String or binary data would be truncated.
The statement has been terminated.

Completion time: 2021-04-26T10:53:52.5951780+10:00

    Em particular, quando você vê a mensagem a seguir, significa que as instruções antes da instrução com falha dentro do procedimento foram confirmadas:

    The statement has been terminated.
    • 16

  2. Se chamar o procedimento inicia uma transação implícita, então uma etapa com falha no proc não deve reverter todas as alterações efetuadas pelo proc

    Não. Mesmo que você tenha SET IMPLICIT_TRANSACTIONS ON (e provavelmente não tem, e não deveria), executar/chamar um procedimento armazenado não inicia uma transação. Se bem me lembro, é assim que o PostgreSQL funciona (qualquer erro dentro de uma função reverterá automaticamente toda a função), mas não o SQL Server.

    Adicionando à resposta do AMtwo , você também pode usar TRY...CATCHcom a ROLLBACK TRAN;no CATCHbloco para controlar esse comportamento. Essa é a abordagem que prefiro em vez de usar SET XACT_ABORT ON;(exceto ao fazer transações distribuídas em servidores vinculados a outras instâncias do SQL Server, caso em que SET XACT_ABORT ON;é necessário). Por favor, veja minha resposta para a seguinte pergunta (também aqui no DBA.SE) para detalhes:

    Como reverter quando 3 procedimentos armazenados são iniciados a partir de um procedimento armazenado

    • 3

relate perguntas