表變數上的自死鎖程序

我們正在為我們的一位客戶執行 SQL Server 2019 CU12。前段時間我們開始遇到有線死鎖，即單個程序在訪問表變數時會自行死鎖。

死鎖報告範例

   <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>

表類型定義如下：

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

知道什麼會導致這些奇怪的死鎖以及如何繞過它們嗎？

最後，我們剛剛在我們的問題上取得了突破。感謝 Erik Darling 的提示。

首先，我將嘗試澄清一下整個申請過程，以便為您提供上下文。有一個相當複雜的應用程式碼作為單個數據庫事務執行。它使用幾種不同的使用者定義表類型作為表變數。有些類型非常簡單，只有一列（如問題中所示），其中一些包含 10+ 各種類型的列。

應用程序使用這些變數將此數據作為 sp_executesql 的參數傳遞。應用程序（.Net）在事務中一個接一個地處理幾個這樣的命令……

應用程序中使用的稍微簡化的應用程式碼範例：

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

問題中提到的死鎖發生在事務中間某處的這些命令之一中……

我們嘗試將兼容級別從 150 降低到 140，但情況發生了變化。我們開始收到更方便的錯誤消息“ String or binary data will be truncated in table… ”，而不是死鎖，但來自更接近事務結束的完全不同的命令。當我們切換回 150 時，我們開始再次收到自死鎖錯誤和上一個命令。

我們還嘗試將兼容性級別保持在 150，並通過以下方式關閉延遲編譯

ALTER DATABASE SCOPED CONFIGURATION SET DEFERRED_COMPILATION_TV = OFF;

僵局也恢復了。兼容性級別的唯一更改更改了返回的錯誤消息。

上面提到的截斷問題是關於應用程序嘗試插入比表類型列定義允許的更長的 nvarchar 字元串。當我們更新表類型定義以適應更長的字元串時，無論兼容級別設置如何，事務中的所有命令都可以順利處理。

我試圖在 .Net 應用程序之外的一個人工範例上模擬這種行為，但沒有成功。因此，我無法為此行為傳遞任何確切的複制步驟。我所做的每一次嘗試都會導致“方便的”“字元串或二進制數據將被截斷…… ”錯誤。但是，該問題的一般機制似乎如上所述。

只是快速重新蓋帽：

• 我們使用的是 SQL 2019 CU12，沒有開啟最新功能。
• 根本原因是違反了表變數定義。
• 150兼容模式下的SQL 2019由於未知原因沒有返回正確的錯誤資訊，並且在事務內完全不同的對像上陷入自死鎖。
• 兼容級別 140 從正確的對象返回正確的消息。

希望它可以幫助遇到同樣問題的人。

引用自：https://dba.stackexchange.com/questions/300740