SQL Server 2022: GENERATE_SERIES Causes Parallel Deadlocks In A Transaction

Many Times!

These table valued functions of the built-in variety have this problem.

This one is no exception. Well, it does throw an exception. But you know.

That’s not exceptional.

DROP TABLE IF EXISTS
    dbo.select_into;

BEGIN TRAN

SELECT
    id = 
        gs.value
INTO dbo.select_into
FROM GENERATE_SERIES
     (
         START = 1, 
         STOP = 10000000
     ) AS gs
OPTION(MAXDOP 8);

COMMIT;

If you run the above code, you’ll get this error:

Msg 1205, Level 13, State 78, Line 105
Transaction (Process ID 70) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Like the issues I outlined in yesterday’s post, I do hope these get fixed before go-live.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.