Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

Parallel SELECT INTO from sys.messages causes intra-query deadlock

Loading data into a new table from sys.messages using parallel SELECT INTO causes consistent self-deadlocks. Testing on a machine with default parallelism settings and 4 logical cores. This occurs in SQL Server 2014, 2016 and 2017. Here's a reproduction:

BEGIN TRANSACTION;

SELECT TOP 10000
m.message_id, m.[text]
INTO SomeNewTable
FROM sys.messages m

For some reason, removing the "BEGIN TRANSACTION" reduces the frequency of deadlocks caused by this query. I've attached a deadlock graph from the issue.

It's possible that bulk loading from sources like sys.messages is simply not a supported scenario - but, if so, this should be documented (or potentially just prevented with an error message).

10 votes
Sign in
(thinking…)
Sign in with: oidc
Signed in as (Sign out)

We’ll send you updates on this idea

Joshua Darnell shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

3 comments

Sign in
(thinking…)
Sign in with: oidc
Signed in as (Sign out)
Submitting...
  • Geoff Patterson commented  ·   ·  Flag as inappropriate

    We hit a similar problem with OPENJSON, Erik. Both your reproduction and ours appear to be fixed in the current private preview of vNext. Paul's reproduction still hits the intra-query deadlock, however.

  • Erik Darling commented  ·   ·  Flag as inappropriate

    Ran into this with a client using JSON:

    BEGIN TRAN

    DECLARE @n NVARCHAR(MAX) = '[1, 2]'

    SELECT *
    INTO #p
    FROM OPENJSON(@n) AS oj
    OPTION(USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'))

    COMMIT

    DROP TABLE #p

  • Paul White commented  ·   ·  Flag as inappropriate

    Works with other streaming UDF sources as well e.g.

    BEGIN TRANSACTION;

    SELECT
    DOBD.[file_id],
    DOBD.page_id,
    DOBD.page_level,
    DOBD.allocation_unit_id,
    DOBD.page_type,
    DOBD.row_count
    INTO SomeNewTable
    FROM sys.dm_os_buffer_descriptors AS DOBD;

Feedback and Knowledge Base