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…)
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
(thinking…)
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