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).

8 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    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
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      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