Processo em auto-deadlock em uma variável de tabela

Finalmente, acabamos de fazer um avanço com o nosso problema. Obrigado Erik Darling pela dica.

Primeiro, tentarei esclarecer um pouco todo o processo de inscrição para dar o contexto. Existe um código de aplicativo bastante complexo sendo executado como uma única transação de banco de dados. Ele usa vários tipos de tabela definidos pelo usuário como variáveis ​​de tabela. Alguns dos tipos são muito simples com apenas uma coluna (como a mostrada na pergunta), alguns deles contêm mais de 10 colunas de vários tipos.

O aplicativo usa essas variáveis ​​para passar esses dados como parâmetro do sp_executesql. Vários comandos como esse são processados ​​um a um dentro de uma transação pela aplicação (.Net)…

Um exemplo de um código de aplicativo ligeiramente simplificado usado no aplicativo:

SqlConnection connection;
DateTable table;
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = SQLCommand;
var parameter = new SqlParameter
{
SqlDbType = SqlDbType.Structured,
Value = table,
ParameterName = "@data",
TypeName = "[TableType]"
};
command.Parameters.Add(parameter);
command.ExecuteNonQuery();
}

E o deadlock mencionado na pergunta ocorreu em um desses comandos em algum lugar no meio da transação…

Tentamos diminuir o nível de compatibilidade de 150 para 140 e a situação mudou. Em vez de deadlock começamos a receber uma mensagem de erro mais conveniente de “ String ou dados binários seriam truncados na tabela… ”, mas de um comando completamente diferente muito mais próximo do final da transação. Quando voltamos para 150, começamos a receber um erro de auto-deadlock e um comando anterior novamente.

Também tentamos manter o nível de compatibilidade em 150 e apenas desligar a compilação adiada por

ALTER DATABASE SCOPED CONFIGURATION SET DEFERRED_COMPILATION_TV = OFF;

O impasse também foi retornado. A única alteração do nível de compatibilidade alterou a mensagem de erro retornada.

O problema de truncamento mencionado acima foi que o aplicativo tentou inserir uma string nvarchar mais longa do que a definição de coluna do tipo de tabela permitida. Quando atualizamos a definição do tipo de tabela para acomodar strings mais longas, todos os comandos na transação foram processados ​​sem problemas, independentemente do nível de compatibilidade definido.

Tentei simular esse comportamento em um exemplo artificial fora do aplicativo .Net, mas sem sucesso. Portanto, não consigo passar nenhuma etapa de replicação exata para esse comportamento. Cada tentativa que fiz resultou no erro “conveniente” “ String ou dados binários seriam truncados… ”. No entanto, a mecânica geral do problema parece ser como sugerido acima.

Apenas uma rápida recapitulação:

• Estamos no SQL 2019 CU12 sem os recursos mais recentes ativados.
• A causa raiz foi uma violação da definição da variável da tabela.
• O SQL 2019 no modo de compatibilidade 150 por um motivo desconhecido não retorna a mensagem de erro correta e cai em um impasse automático em um objeto completamente diferente dentro da transação.
• O nível de compatibilidade 140 retorna a mensagem correta do objeto correto.

Espero que ajude alguém que encontre o mesmo problema.