Self-deadlocked process on a table variable

We are running SQL Server 2019 CU12 for one of our customers. Some time ago we started getting a wired deadlocks whereby a single process deadlocks itself on access to a table variable.

Example Deadlock Report

 
  
 
 
  
   
    unknown
   
   
     (@Ids [SubjectRegistry.Consolidation.IdTable] READONLY)
     DELETE [reg].[HistoricalCompanyInfo] FROM [reg].[HistoricalCompanyInfo] t
     INNER JOIN @Ids ids ON ids.Id = t.HistoricalCompanyInfoId
   
  
 
 
  
   
    
    
   
   
    
   
  
 

The table type definition is as follows:

CREATE TYPE [dbo].[SubjectRegistry.Consolidation.IdTable] AS TABLE(
    [Id] [bigint] NOT NULL, PRIMARY KEY CLUSTERED ([Id] ASC) WITH (IGNORE_DUP_KEY = OFF)
)
GO

Any idea what could cause these strange deadlocks and how to bypass them?

Finally, we have just made a breakthrough with our issue. Thanks Erik Darling for a hint.

First, I will try to clarify a bit the entire application process to give you the context. There is a quite complex application code running as a single database transaction. It uses several different User-Defined Table Types as table variables. Some of the types are very simple with just a one column (as the one shown in the question), some of them contains 10+ columns of various types.

The application uses these variables to pass this data as a parameter of the sp_executesql. Several commands like this are processed one-by-one within a transaction by the application (.Net) …

An Example of a slightly simplified app code used in the application :

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();
}

And the deadlock mentioned in the question occurred in one of these commands somewhere in the middle of the transaction…

We tried to switch the compatibility level down from 150 to 140 and the situation has changed. Instead of deadlock we started receiving more convenient error message of “String or binary data would be truncated in table…”, but from a completely different command much closer to the end of the transaction. When we switched back to 150 we started receiving self-deadlock error an a previous command again.

We also tried to keep the compatibility level on 150 and just turn off the deferred compilation by

ALTER DATABASE SCOPED CONFIGURATION SET DEFERRED_COMPILATION_TV = OFF;

The deadlock was returned as well. The only change of the compatibility level changed the error message returned.

The truncation issue mentioned above was about that the application tried to insert a longer nvarchar string than the table type column definition allowed. When we updated the table type definition to accommodate longer strings, all commands in the transaction were processed smoothly regardless of the compatibility level set.

I tried to simulate this behavior on an artificial example outside of the .Net application but without a success. So, I am not able to pass any exact replication steps for this behavior. Every attempt I made resulted in the “convenient” “String or binary data would be truncated …” error. However, the general mechanics of the issue seems to be as hinted above.

Just quick re-cap:

• We are on SQL 2019 CU12 with no latest features turned on.

• The root-cause was a violation of the table variable definition.

• The SQL 2019 in 150 compatibility mode for an unknown reason does not return the correct error message and falls into a self-deadlock on a completely different object within the transaction.

• The compatibility level 140 returns the correct message from the correct object.

Hopefully it helps someone who encounter the same issue.

StackExchange Database Administrators Q#300740, answer score: 6