I am getting a strange issue when I am using Cross apply OpenJson in a transaction which is writing to #temp.
when I run below Code,
begin tran
drop table if exists #temp
SELECT ROW_NUMBER() over (PARTITION by StatementAnswerId order by (select 1) ) as row_num,* INTO #temp FROM [Submissions].[StatementAnswer] with(NOLOCK) CROSS APPLY OPENJSON(StatementJson) with (questionId varchar(200) '$.questionId' ) where StatementJson<> '' and StatementJson is not NULL
I get this deadlock Msg 1205, Level 13, State 78, Line 11 Transaction (Process ID 68) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
but no error, When I avoid openJson block,
begin tran
drop table if exists #temp
SELECT ROW_NUMBER() over (PARTITION by StatementAnswerId order by (select 1) ) as row_num,* INTO #temp FROM [Submissions].[StatementAnswer] with(NOLOCK)
any clue, why openJson together with writing to #temp leads to Deadlock?
