question

sabina avatar image
sabina asked

Deadlock while using openJson in a transaction writing to temp

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?

transactiondeadlock
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.