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:
SELECT TOP 10000
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).
Geoff Patterson commented
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
Ran into this with a client using JSON:
DECLARE @n NVARCHAR(MAX) = '[1, 2]'
FROM OPENJSON(@n) AS oj
DROP TABLE #p
Paul White commented
Works with other streaming UDF sources as well e.g.
FROM sys.dm_os_buffer_descriptors AS DOBD;