Batchmode on rowstore: deadlock

  • Are there known issues with Batchmode on rowstore in CU11?

    One specific complex query is getting "Transaction (Process ID 65) was deadlocked on generic waitable object resources with another process and has been chosen as the deadlock victim. Rerun the transaction."

    The deadlock sofar triggers when the optimizer decides to go for BatchModeOnRowStoreUsed="true"

    OPTION(USE HINT('DISALLOW_BATCH_MODE')) or leaving a part of the selected data out doesn't result in the deadlock.

    With batchmode it also starts with another table and it is a non-parallel operation (the other operators are parallel)

    There isn't any other activity going on.

    I don't post the query because it's gotten an alternative version and sofar is has been the only case for the deadlock to appear.

  • Sounds like a parallelism deadlock. They're generally rare, but absolutely do happen. Usually, but not always, there are tuning opportunities that can make them go away; changes to the code, new or adjusted indexes, that sort of thing. You could also look at the cost threshold for parallelism to adjust that. Use a max dop hint for the query, use the hint you're already using, look to see if there are other hints such as a hash join that change the behavior.

    Experiment with them all to see what works best in your given situation, but it really is usually just query tuning fundamentals that fixes it.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Thanks for the keyword "parallelism deadlock" Grant. Lead to intra-query parallelism with more information on the internet.

    As by your recommendation solved with a query hint.

     

  • Thanks for reporting back what worked.

     

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply