We came across an behaviour that I cannot explain. For context: SQL Server 2022 (v16.00.1135). A stored procedure has the following code:

begin try
    begin transaction
      drop table if exists #reports;
      drop table if exists #details;
        select
          reportId
        , reportName
        , details
      into #reports
      from openjson(@parameter) with (
          reportId int '$.reportId'
        , reportName nvarchar(100) '$.reportName'
        , details nvarchar(max) '$.reportDetails' as json
      )

      select
          #reports.reportId
        , details.detailId
        , details.text -- (*)
      into #details
      from #reports
      outer apply openjson(#reports.details) with (
          detailId nvarchar(50) '$.detailId'
        , text nvarchar(max) '$.text' -- (**)
      ) as details
      
      -- acutal data processing goes here
      select * from #reports
      select * from #details
      -- further create/update/... records in tables
    commit transaction
  end try
    begin catch
      -- process error ...
      rollback transaction;
  end catch;

An example of data is:

    declare @parameter nvarchar(max) = '[
      {
        "reportId": 1,
        "reportName": "repN",
        "reportDetails": [
          {"detailId": "9", "text": "aaa"},
          {"detailId": "8", "text": "bbb"}
        ]
      },
      {
        "reportId": 2,
        "reportName": "repII",
        "reportDetails": []
      }
    ]'

The result is:

reportId    reportName  details
1   repN    "[ ..."
2   repII   "[]"


reportId    detailId    text
1   a   9
1   b   8
2   (null)  (null)

The above is as expected.

This is a bit simplified code -- in real code I have 25 columns in "reports" and about 30 in details.

In certain conditions I receive the following error message:

        2 Procedure ...name... . 
Error 1205, Severity 13, State 78, Line 114, 
Message: [Transaction (Process ID 51) was deadlocked on lock | 
communication buffer resources with another process and has been 
chosen as the deadlock victim. Rerun the transaction.]          

Now what I figured out is:

1. The problem raised when @parameter array was relatively large (41 reports and overall 47). Using 1-2-3 does not create an issue

2. I narrowed down the problem to one column(!) see in code marked with (*) and (**). If I make column text nvarchar(200) it all works ok.

3. Trying to play around, we found another workaround:

    declare @td as table (
         reportId int
       , detailId int
       , text nvarchar(max)
    )
    insert into @td
    select
        #reports.reportId
      , details.detailId
      , details.text
    from #reports
    outer apply openjson(#reports.details) with (
        detailId nvarchar(50) '$.detailId'
      , text nvarchar(max) '$.text'
    ) as details

These are all workarounds so far, not a solution. Restarting SQL Server does not help.

My obvious question is -- does anybody observe or have an idea why this might happen?

Thanks.

another upd: settting maxdop to 1 helps , but this does not explain the issues

We came across an behaviour that I cannot explain. For context: SQL Server 2022 (v16.00.1135). A stored procedure has the following code:

begin try
    begin transaction
      drop table if exists #reports;
      drop table if exists #details;
        select
          reportId
        , reportName
        , details
      into #reports
      from openjson(@parameter) with (
          reportId int '$.reportId'
        , reportName nvarchar(100) '$.reportName'
        , details nvarchar(max) '$.reportDetails' as json
      )

      select
          #reports.reportId
        , details.detailId
        , details.text -- (*)
      into #details
      from #reports
      outer apply openjson(#reports.details) with (
          detailId nvarchar(50) '$.detailId'
        , text nvarchar(max) '$.text' -- (**)
      ) as details
      
      -- acutal data processing goes here
      select * from #reports
      select * from #details
      -- further create/update/... records in tables
    commit transaction
  end try
    begin catch
      -- process error ...
      rollback transaction;
  end catch;

An example of data is:

    declare @parameter nvarchar(max) = '[
      {
        "reportId": 1,
        "reportName": "repN",
        "reportDetails": [
          {"detailId": "9", "text": "aaa"},
          {"detailId": "8", "text": "bbb"}
        ]
      },
      {
        "reportId": 2,
        "reportName": "repII",
        "reportDetails": []
      }
    ]'

The result is:

reportId    reportName  details
1   repN    "[ ..."
2   repII   "[]"


reportId    detailId    text
1   a   9
1   b   8
2   (null)  (null)

The above is as expected.

This is a bit simplified code -- in real code I have 25 columns in "reports" and about 30 in details.

In certain conditions I receive the following error message:

        2 Procedure ...name... . 
Error 1205, Severity 13, State 78, Line 114, 
Message: [Transaction (Process ID 51) was deadlocked on lock | 
communication buffer resources with another process and has been 
chosen as the deadlock victim. Rerun the transaction.]          

Now what I figured out is:

1. The problem raised when @parameter array was relatively large (41 reports and overall 47). Using 1-2-3 does not create an issue

2. I narrowed down the problem to one column(!) see in code marked with (*) and (**). If I make column text nvarchar(200) it all works ok.

3. Trying to play around, we found another workaround:

    declare @td as table (
         reportId int
       , detailId int
       , text nvarchar(max)
    )
    insert into @td
    select
        #reports.reportId
      , details.detailId
      , details.text
    from #reports
    outer apply openjson(#reports.details) with (
        detailId nvarchar(50) '$.detailId'
      , text nvarchar(max) '$.text'
    ) as details

These are all workarounds so far, not a solution. Restarting SQL Server does not help.

My obvious question is -- does anybody observe or have an idea why this might happen?

Thanks.

another upd: settting maxdop to 1 helps , but this does not explain the issues

• sql
• sql-server
• t-sql
• transactions

• 1 SQL Server 16.00.1135, if it helps. – rezdm Commented Mar 14 at 13:28
• 1 Pretty old version, i'm guessing the error might be fixed in newer versions. Does it help adding: OPTION(MAXDOP 1) to the query? – siggemannen Commented Mar 14 at 13:53
• 1 I, too, would recommend updating your instance to the most recent version (16.0.4185.3 at time of writing, CU18), to ensure this isn't a previously fixed bug. You've only installed the GDR fixes, so you're technically RTM+GDR. – Thom A Commented Mar 14 at 14:12
• 1 Have you reviewed the deadlock report? See the deadlocks guide for a query to extract the deadlock details from the system_health trace and add the XML deadlock report to your question. – Dan Guzman Commented Mar 14 at 14:25
• 1 Can you post the underlying XML - not just the image? Though we can already see that this is a session deadlocking itself with an intra query parallel deadlock so updating CU could well resolve it – Martin Smith Commented Mar 14 at 15:53

2 Answers 2

Since the deadlock involves only one session ("server process id") with different "execution context id"s this is an "intra-query parallelism deadlock". These are always product issues. If it repros on the latest CU, please open a support case, and work around it for the time being.

See eg: https://dba.stackexchange/questions/216086/solving-intra-parallel-query-deadlocks

Thanks everybody.
Indeed lock graph and changing maxdop value to 1 indicates that this is an internal issue of SQL server. I mark this as "closed".

Thanks everybody once again.