Self-deadlocked process on a table variable

Question

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

    <deadlock>
 <victim-list>
  <victimProcess id="process2ae9f9f7468" />
 </victim-list>
 <process-list>
  <process id="process2ae9f9f7468" taskpriority="0" logused="0" waitresource="OBJECT: 2:-1194094756:0 " waittime="110" ownerId="6978622122" transactionname="GetInitializedIMA" lasttranstarted="2021-09-15T21:46:44.243" XDES="0x2b4d9477be8" lockMode="Sch-S" schedulerid="3" kpid="10868" status="suspended" spid="102" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2021-09-15T21:46:44.113" lastbatchcompleted="2021-09-15T21:46:44.113" lastattention="2021-09-15T21:46:15.777" clientapp=".Net SqlClient Data Provider" hostname="removed" hostpid="15900" loginname="removed" isolationlevel="read committed (2)" xactid="6978622078" currentdb="15" currentdbname="MigrationSubjects" lockTimeout="4294967295" clientoption1="673187936" clientoption2="128056">
   <executionStack>
    <frame procname="unknown" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">unknown</frame>
   </executionStack>
   <inputbuf>
     (@Ids [SubjectRegistry.Consolidation.IdTable] READONLY)
     DELETE [reg].[HistoricalCompanyInfo] FROM [reg].[HistoricalCompanyInfo] t
     INNER JOIN @Ids ids ON ids.Id = t.HistoricalCompanyInfoId
   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <objectlock lockPartition="0" objid="-1194094756" subresource="FULL" dbid="2" objectname="tempdb.dbo.#B8D38F5C" id="lock2ac0942fa00" mode="Sch-M" associatedObjectId="-1194094756">
   <owner-list>
    <owner id="process2ae9f9f7468" mode="Sch-M" />
    <owner id="process2ae9f9f7468" mode="Sch-S" requestType="wait" />
   </owner-list>
   <waiter-list>
    <waiter id="process2ae9f9f7468" mode="Sch-S" requestType="wait" />
   </waiter-list>
  </objectlock>
 </resource-list>
</deadlock>

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?

Answer 1

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.

Answer 2

We have a similar issue with table variables in an update statement. The update statement is:

UPDATE
    Tracking.tInterchangeInMetaData
SET
    StatusTypesId = iis.StatusTypesId
FROM
    @iInterchangesInStatuses iis 
    JOIN Tracking.tInterchangeInMetaData im ON im.InterchangesInId = iis.InterchangesInId               

The deadlock report is:

    <deadlock>
  <victim-list>
    <victimProcess id="process13efc1bfc28" />
  </victim-list>
  <process-list>
    <process id="process13efc1bfc28" taskpriority="0" logused="1560" waitresource="KEY: 15:72057594050838528 (cd59dedaa3da)" waittime="13879" ownerId="35108034" transactionname="user_transaction" lasttranstarted="2022-11-22T07:29:51.297" XDES="0x13f68438460" lockMode="U" schedulerid="3" kpid="5352" status="suspended" spid="166" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-11-22T07:29:51.307" lastbatchcompleted="2022-11-22T07:29:51.293" lastattention="1900-01-01T00:00:00.293" clientapp="Core Microsoft SqlClient Data Provider" hostname="{hostname}" hostpid="1" loginname="xxx" isolationlevel="read committed (2)" xactid="35108034" currentdb="15" currentdbname="operations-prod-link.transactional" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
      <executionStack>
        <frame procname="operations-prod-link.transactional.Tracking.pInterchangesIn_UpdateStatuses" line="10" stmtstart="456" stmtend="1132" sqlhandle="0x03000f005cf8440dd4f47a0055af000001000000000000000000000000000000000000000000000000000000">
UPDATE
        Tracking.tInterchangeInMetaData
    SET
        StatusTypesId = iis.StatusTypesId
    FROM
        @iInterchangesInStatuses iis 
        JOIN Tracking.tInterchangeInMetaData im ON im.InterchangesInId = iis.InterchangesInI    </frame>
      </executionStack>
      <inputbuf>
Proc [Database Id = 15 Object Id = 222623836]   </inputbuf>
    </process>
    <process id="process13f6eb39848" taskpriority="0" logused="3720" waitresource="KEY: 15:72057594050838528 (52457f2eb775)" waittime="108" ownerId="35103686" transactionname="user_transaction" lasttranstarted="2022-11-22T07:29:45.817" XDES="0x13f3cbc0460" lockMode="U" schedulerid="1" kpid="8892" status="suspended" spid="142" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-11-22T07:29:45.847" lastbatchcompleted="2022-11-22T07:29:45.837" lastattention="1900-01-01T00:00:00.837" clientapp="Core Microsoft SqlClient Data Provider" hostname="{hostname}" hostpid="1" loginname="xxx" isolationlevel="read committed (2)" xactid="35103686" currentdb="15" currentdbname="operations-prod-link.transactional" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
      <executionStack>
        <frame procname="operations-prod-link.transactional.Tracking.pInterchangesIn_UpdateStatuses" line="10" stmtstart="456" stmtend="1132" sqlhandle="0x03000f005cf8440dd4f47a0055af000001000000000000000000000000000000000000000000000000000000">
UPDATE
        Tracking.tInterchangeInMetaData
    SET
        StatusTypesId = iis.StatusTypesId
    FROM
        @iInterchangesInStatuses iis 
        JOIN Tracking.tInterchangeInMetaData im ON im.InterchangesInId = iis.InterchangesInI    </frame>
      </executionStack>
      <inputbuf>
Proc [Database Id = 15 Object Id = 222623836]   </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <keylock hobtid="72057594050838528" dbid="15" objectname="operations-prod-link.transactional.Tracking.tInterchangeInMetaData" indexname="PK_tInterchangeInMetaData" id="lock13f67024f80" mode="U" associatedObjectId="72057594050838528">
      <owner-list>
        <owner id="process13f6eb39848" mode="U" />
      </owner-list>
      <waiter-list>
        <waiter id="process13efc1bfc28" mode="U" requestType="wait" />
      </waiter-list>
    </keylock>
    <keylock hobtid="72057594050838528" dbid="15" objectname="operations-prod-link.transactional.Tracking.tInterchangeInMetaData" indexname="PK_tInterchangeInMetaData" id="lock13f8af19300" mode="X" associatedObjectId="72057594050838528">
      <owner-list>
        <owner id="process13efc1bfc28" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="process13f6eb39848" mode="U" requestType="wait" />
      </waiter-list>
    </keylock>
  </resource-list>
</deadlock>

Also we see a lot of LCK_M_U waits.

The odd thing is that if we switch the table variable in the join so that the table variable is not the first table in the from part the deadlock goes away:

UPDATE
    Tracking.tInterchangeInMetaData
SET
    StatusTypesId = iis.StatusTypesId
FROM
    Tracking.tInterchangeInMetaData im 
    JOIN @iInterchangesInStatuses iis ON iis.InterchangesInId = im.InterchangesInId

It is my understanding that the order of tables in an inner join does not matter.

We are using SQL Server 2019 CU18 comp. level 150. We have tried removing CU18 and changing comp. level to 140 and 130 with the same result.

It is a stored procedure with a UDTT as input, and we can also solve the deadlock by copying the rows in the UDTT to a temp table and use that in the update statement, but it's not a good solution as the stored procedure is called very often.