SQL Server Self Deadlock on SELECT INTO with temp table from OPENJSON

Question

We had a weird issue in development and are looking for an explanation on a self-deadlock issue with a SELECT INTO on a temp table.

We have a routine to transform some fairly complicated JSON documents into tabular form. We are currently accomplishing this using OPENJSON, which in general works just great.

This transformation happens in the context of a trigger. When one or more rows are inserted into the table, an array of JSON documents is generated, stored in a single variable, and passed into the below routine. It looks something like this:

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

When we run this in SSMS, it works just fine. The temp table gets generated and populated, no issue. When we move it to the trigger and insert just a single row to the underlying table (i.e. @MyJSONDocuments is an array of a single document), it also works just fine. When we insert two or more rows, and @MyJSONDocuments contains multiple documents in the array, we get the dreaded:

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.

When we wrap the SELECT INTO statement in a BEGIN TRAN / COMMIT TRAN in SSMS, we also get the same deadlock error.

After some research, we found that the problem may be concurrency issues from multiple threads that are parsing the JSON and locking the temp table simultaneously, hence the deadlock. When we use the hint OPTION MAXDOP(1) i.e. force the query single-threaded there are no deadlocks. Likewise, if we create the temp table first, and then subsequently make an INSERT that also works.

We have two viable solutions to the problem, but I am still unclear why it is an issue. I guess my questions are:

1/ What is the real reason why the SELECT INTO causes a self-deadlock on the temp table?

2/ Why does the error only occur in the context of a transaction?

3/ Why does the deadlock only occur with a SELECT INTO and not on the regular INSERT?

Thanks everybody!

Edit: Deadlock graph below

user_3609362 · Answer

Unsure on the full answer to this question, but I believe the issue is related to parallel access of the OPENJSON results in the context of the transaction. I have the exact same issue and it's resolved by setting OPTION (MAXDOP 1) to force a serial plan on that one part of the query.

Try this:

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)

SQL Server Self Deadlock on SELECT INTO with temp table from OPENJSON

1 Answers