主页 / dba / 问题 / 300740
Accepted
Martin Karouš
Asked: 2021-10-07 06:06:08 +0800 CST

表变量上的自死锁进程

  • 772

我们正在为我们的一位客户运行 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

知道什么会导致这些奇怪的死锁以及如何绕过它们吗？

sql-server deadlock

1 个回答

  1. Best Answer

    最后，我们刚刚在我们的问题上取得了突破。感谢 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 从正确的对象返回正确的消息。

    希望它可以帮助遇到同样问题的人。

    • 5

相关问题