Transação explícita deixada em aberto após try/catch

Existem alguns tipos de erros que não são detectados pela estrutura TRY/ T-SQL CATCH(retirados da página MSDN para TRY...CATCH e ligeiramente editados para maior clareza):

• Erros com gravidade 20 ou superior que interrompem o processamento da tarefa do Mecanismo de Banco de Dados do SQL Server para a sessão. Se ocorrer um erro com gravidade de 20 ou superior e a conexão com o banco de dados não for interrompida, TRY…CATCH manipulará o erro.
• Atenções, como solicitações de interrupção do cliente ou conexões de cliente interrompidas.
• Quando a sessão é encerrada por um administrador do sistema usando a instrução KILL.
• Erros que ocorrem no mesmo nível de execução que a construção TRY…CATCH durante a recompilação em nível de instrução, como erros de resolução de nome de objeto que ocorrem após a compilação devido à resolução de nome adiada.

Para o código de exemplo fornecido na pergunta (ou seja, uma única instrução DML), há uma correção muito simples: remova a transação explícita. Para uma única instrução DML, uma transação explícita é desnecessária, a menos que haja lógica adicional que verifique alguma condição e, opcionalmente, decida desfazer essa alteração por meio de um ROLLBACKfeito no principal TRYe não como parte do CATCH, pois não era um mecanismo de banco de dados erro. Fora desse cenário específico, não consigo ver uma razão (boa, pelo menos) para usar uma transação explícita para uma única instrução DML. Mas eu ainda manteria a estrutura TRY/ CATCHpara tratamento geral de erros.

Quando se trata de várias instruções DML (que é o verdadeiro contexto da pergunta, conforme declarado na redação acima do código de exemplo), obviamente você precisa de uma transação explícita, então aqui estão alguns pensamentos sobre isso:

• Um comentário de @usr na pergunta sugere ignorar completamente o tratamento de erros na camada do banco de dados e, em vez disso, tratá-lo na camada do aplicativo. Embora o código do aplicativo possa manipular as transações e o tratamento de erros, se você não estiver usando um ORM como o Entity Framework exclusivamente (o que significa que você tem procedimentos armazenados que são chamados por trabalhos do SQL Agent e/ou pelo pessoal de suporte via SSMS), esses procedimentos armazenados precisam para incluir a transação e o tratamento de erros porque a camada do aplicativo nem sempre é o iniciador da execução, mas você ainda deseja a transação e o tratamento de erros nessas outras situações. Eu discuto isso mais na seguinte resposta: Somos obrigados a lidar com a transação no código C #, bem como no procedimento armazenado
• As transações não confirmadas são revertidas automaticamente quando a conexão termina. Isso é da perspectiva do SQL Server, não da perspectiva do cliente. Ou seja, a conexão não está mais listada em sys.dm_exec_connections.

• O pool de conexão complica o tratamento de reversão automática, pois, por design, mantém a conexão aberta, mesmo depois que o cliente a "fecha". Quando a conexão permanece aberta para que outra tentativa de "conexão" possa simplesmente reutilizá-la, a sessão em si não é "limpa" até que uma nova conexão seja aberta e um lote de consulta seja executado! Após a primeira execução enviada pelo código do aplicativo em uma "nova" conexão que está realmente reutilizando uma conexão do pool, um processo interno rotulado como sp_reset_connectioné chamado que, entre outras coisas, reverterá uma transação não confirmada nessa sessão que agora é sendo reutilizado. O problema aqui é quando o pool de conexões está sendo usado e há um término do processo (talvez um Timeout de comando) enenhuma nova conexão está sendo solicitada e nenhuma nova consulta está sendo enviada. Nesse caso, a conexão fica apenas no pool e a sessão ainda existe e nenhuma operação de limpeza está sendo solicitada. Mas isso não dura para sempre (embora ainda mais do que você deseja nesses casos). De acordo com a página do MSDN para SQL Server Connection Pooling (ADO.NET) (na seção Removendo conexões ):

  O pooler de conexão remove uma conexão do pool depois que ela fica ociosa por aproximadamente 4 a 8 minutos ou se o pooler detecta que a conexão com o servidor foi interrompida.

• Cancelar a execução de um procedimento armazenado não é apenas uma questão de o código do aplicativo obter um CommandTimeout ou chamar um "cancelar" explícito. O SQL Server Management Studio (SSMS), ou qualquer IDE, também é um código cliente. E é possível cancelar um procedimento armazenado em execução no SSMS. E fazer isso terá o mesmo efeito de pular o CATCHbloco. Mas cancelar uma execução no SSMS ainda mantém você na mesma sessão, então a transação ainda está ativa até que você chame manualmente COMMITou ROLLBACK, ou até que você feche a guia de consulta (momento em que ele informará que há uma transação não confirmada e perguntará se você quer cometê-lo ou não; meu teste mostra que responder "não" reverterá).

Então, o que fazer com o código do aplicativo que está usando o pool de conexões? Eu não sou um grande fã de usar arquivos SET XACT_ABORT ON;. O que eu tentaria primeiro é:

• As conexões podem definir o tamanho máximo do pool de conexões por meio da palavra- Max Pool Sizechave da string de conexão. Se o pool for muito grande, é menos provável que qualquer conexão específica seja reutilizada rapidamente. O tamanho do pool padrão parece ser 100. Definir o valor para ser um pouco menor do que é atualmente (mas não muito baixo para não fazer uso efetivo do pool de conexões) ajudará a garantir que as conexões sejam reutilizadas mais rapidamente significará a limpeza processo será chamado mais rapidamente, o que reverterá qualquer transação aberta.

• No código do aplicativo onde você está executando a consulta/procedimento armazenado, no catchbloco você pode chamar SqlConnection.ClearPool que deve fechar a conexão real no nível do SQL Server que, por sua vez, deve permitir a reversão automática de transações não confirmadas. Você pode até ser direcionado com isso verificando a exceção para ver se é um tempo limite de comando ou um dos outros poucos cenários que encerrariam o processo ao ignorar o CATCHbloco T-SQL e, em caso afirmativo , chame ClearPool.

• Tecnicamente, você pode simplesmente desabilitar o pool de conexões totalmente especificando Pooling=false;na string de conexão. No entanto, não acredito que isso seja necessário e não o recomendo, a menos que seja absolutamente necessário OU tenha um aplicativo que não gere muitas conexões em primeiro lugar.

Independentemente do trabalho feito dentro da cláusula TRY, quais cenários possíveis poderiam fazer com que uma consulta como essa deixasse uma transação aberta?

O cenário comum que faz com que a transação permaneça aberta é um tempo limite de comando. Um tempo limite de comando ocorre no cliente, não no servidor. Quando uma consulta é executada por mais tempo do que o especificado CommandTimeout, a API do cliente envia um comando de atenção ao servidor para cancelar o lote. Isso evita que qualquer código subsequente no lote, incluindo o CATCHbloco, seja executado. A transação é deixada em aberto como resultado, a menos que SET XACT_ABORT ONseja especificado.

SET XACT_ABORT ONA configuração faz com que a transação seja revertida após um erro T-SQL, então sugiro que você crie o hábito disso para todas as transações explícitas. Abaixo está um exemplo de padrão geral que eu recomendo. Use THROW;no CATCHbloco no SQL Server 2012 e posterior, em vez da RAISERRORfeiúra.

SET XACT_ABORT ON;

BEGIN TRY

    -- Generate a constraint violation error.
    BEGIN TRANSACTION;

    DELETE FROM Production.Product
    WHERE ProductID = 980;

    COMMIT TRANSACTION;

END TRY

BEGIN CATCH

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    DECLARE
         @ErrorNumber int
        ,@ErrorMessage nvarchar(2048)
        ,@ErrorSeverity int
        ,@ErrorState int
        ,@ErrorLine int;

    SELECT
         @ErrorNumber =ERROR_NUMBER()
        ,@ErrorMessage =ERROR_MESSAGE()
        ,@ErrorSeverity = ERROR_SEVERITY()
        ,@ErrorState =ERROR_STATE()
        ,@ErrorLine =ERROR_LINE();

    RAISERROR('Error %d caught at line %d: %s'
        ,@ErrorSeverity
        ,@ErrorState
        ,@ErrorNumber
        ,@ErrorLine
        ,@ErrorMessage);

END CATCH;

Esteja ciente de que erros de compilação no mesmo escopo não são afetados SET XACT_ABORTe não podem ser detectados.

Cenários possíveis:

• Análise (sintaxe e semântica)

• Compilação (referência de objeto inválida, o erro atingirá o bloco catch se a instrução for armazenada em cache e sinalizada, mas não na primeira vez)

• Tempo Limite do Comando