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

Vargas

can you post a deadlock graph from one of the occurrences? – Rosenblatt

What version of SQL Server? I ran into this exact problem yesterday for the first time. This may be related: feedback.azure.com/forums/908035-sql-server/suggestions/… – Shipman

@MartinSmith, I edited the post above – Vargas

@MattG We are using SQL Azure (specifically managed instance) – Vargas

I have an idea about this but can't reproduce the issue. What changes ned to be made here to repro? dbfiddle.uk/… – Rosenblatt

Your fiddle actually causes the deadlock on my SQL Server (Azure Managed Instance) Msg 1205, Level 13, State 0, Line 30 Transaction (Process ID 81) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction. – Vargas

When I remove the Begin Tran/Commit there is no deadlock. – Vargas

Hmm, I was testing on SQL Server 2019 so maybe some issue specific to Azure editions – Rosenblatt

Answer 1

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)

Shipman

I agree that this solves the issue (it is what I am using now, as opposed to creating the temp table separately then inserting into it.) I also think its only when I have theCROSS APPLY that this happens also. I guess I am just looking for the technical reason for why this is happening, and also why it matters whether or not transactions are involved. – Vargas

I agree, this solves the isssue, but instead of having to go query by query, we adapted the parallelism to align it with the number of cores by using: EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE WITH OVERRIDE; GO EXEC sp_configure 'max degree of parallelism', 4; GO RECONFIGURE WITH OVERRIDE; GO – Hymanhymen