json - 使用来自 OPENJSON 的临时表在 SELECT INTO 上出现 SQL Server 自死锁

Question

我们在开发中遇到了一个奇怪的问题，正在寻找有关临时表上 SELECT INTO 的自死锁问题的解释。

我们有一个例程将一些相当复杂的JSON文档转换成表格形式。我们目前正在使用 OPENJSON 来完成这项工作，它通常效果很好。

此转换发生在触发器的上下文中。当一个或多个行插入到表中时，将生成一个 JSON 文档数组，存储在一个变量中，并传递到下面的例程中。它看起来像这样:

SELECT 
a.[ID],
b.[Some stuff here...]
INTO #MyTempTable
FROM OPENJSON(@MyJSONDocuments)
WITH (
    ID VARCHAR(40),
    nestedDocument NVARCHAR(MAX) AS JSON) a
CROSS APPLY OPENJSON(nestedDocument ,'$') b

当我们在 SSMS 中运行它时，它工作得很好。生成并填充临时表，没问题。当我们将其移至触发器并仅向基础表插入一行时(即 @MyJSONDocuments 是单个文档的数组)，它也能正常工作。当我们插入两行或更多行，并且 @MyJSONDocuments 在数组中包含多个文档时，我们会得到可怕的结果:

Transaction (Process ID x) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

当我们在 SSMS 中将 SELECT INTO 语句包装在 BEGIN TRAN/COMMIT TRAN 中时，我们也会遇到同样的死锁错误。

经过研究，我们发现问题可能是多个线程同时解析 JSON 和锁定临时表的并发问题，因此出现死锁。当我们使用提示 OPTION MAXDOP(1) 时，即强制查询单线程，没有死锁。同样，如果我们先创建临时表，然后创建一个同样有效的 INSERT。

我们有两个可行的解决方案来解决这个问题，但我仍然不清楚为什么它会成为一个问题。我想我的问题是:

1/SELECT INTO 导致临时表自死锁的真正原因是什么？

2/为什么错误只发生在交易上下文中？

3/为什么死锁只发生在 SELECT INTO 而不是发生在常规 INSERT 上？

谢谢大家!

编辑:下面的死锁图

Answer 1

不确定这个问题的完整答案，但我相信这个问题与在事务上下文中并行访问 OPENJSON 结果有关。我有完全相同的问题，通过设置 OPTION (MAXDOP 1) 强制对查询的那一部分执行串行计划来解决。

试试这个:

SELECT 
a.[ID],
b.[Some stuff here...]
INTO #MyTempTable
FROM OPENJSON(@MyJSONDocuments)
WITH (
    ID VARCHAR(40),
    nestedDocument NVARCHAR(MAX) AS JSON) a
CROSS APPLY OPENJSON(nestedDocument ,'$') b
OPTION (MAXDOP 1)