How to Prevent Partitioned Columnstore Deadlocks on SELECT

Question

I have three Clustered Columnstore Index (CCI) tables in SQL Server 2016. All of these CCIs are in the same partitioning scheme, based on Tenant ID. Lately, and inconsistently, I am getting deadlocks on simple select statements from joins to these tables. Example query that deadlocks:

SELECT  TOP 33 r.tenantid
FROM    Table_r r
        INNER JOIN Table_cm cm ON r.MyKey=cm.MyKey 
        INNER JOIN Table_pe pe ON r.MyKey=pe.MyKey 
WHERE   r.TenantId = 69
        AND pe.TenantId = 69
        AND cm.TenantId = 69

Error message:

Transaction (Process ID 56) was deadlocked on generic waitable object resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Clues:

• If the query uses another index besides the CCI it does not deadlock.
• If I remove two of the three tenantid filters it does not deadlock.
• If I SELECT top 32 or lower it does not deadlock.
• If I add OPTION (MAXDOP 1) it does not deadlock.
• I can repro this in my scrambled PROD replica, PROD READ-ONLY Secondary, and PROD itself.
• I cannot repro this behavior in DEV or INT.
• It still deadlocks if I add WITH(NOLOCK) to all 3 table joins
• The query deadlocks itself. It will deadlock when there are no other active processes.
• Query plans without parallelism do not deadlock

Deadlock xml here

Our PROD Version:

Microsoft SQL Server 2016 (SP2-CU5) (KB4475776) - 13.0.5264.1 (X64) Jan 10 2019 18:51:38 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 (Build 9600: ) (Hypervisor)

How do I prevent deadlocks on this query?

Answer 1

Since you're on SQL Server 2016, it's worth mentioning that there is at least one public bug fix for parallel deadlocks involving columnstore indexes:

FIX: A deadlock occurs when you run a parallel query on a clustered columnstore index in SQL Server 2016 and 2017

(thanks to Denis Rubashkin for providing the link initially)

This was released as part of SP1 CU7. If you're not up to that CU, you should give that a shot. This fix would also be included in SP2 (any of the CUs).

In general, the two approaches for fixing intra-query parallelism deadlocks:

• avoid parallelism (by tuning the query so that it doesn't go parallel, using a MAXDOP hint, etc.) - this is covered in the other answer by Thomas Costers
• apply the latest service pack / cumulative updates to SQL Server

Answer 2

Have you checked following blog on Intra-Query Parallel Thread Deadlocks

The SyncPoint resource indicates the use of an exchange event if I'm not mistaking.
Looking at the participants of your deadlock you can see that they are all coming from the same spid(55) and batch(0), but are using different threads. This indicates that they are all part of the same parallel query and is confirmed by the fact that you don't receive any deadlocks whenever you run the query with MAXDOP 1. In the case of Intra-Query Parallel Thread Deadlocks, the threads of a single query will end up deadlocking each other waiting for synchronisation objects, SyncPoints in your case.

Last time when I witnessed this kind of behaviour, I was able to further optimise the query and thus preventing the query from using a parallel execution plan. I suspect you did the same thing by limiting your result set to 32 records or by using a different index.
Another option would be to add MAXDOP 1 to your query, though not a great fan of this option.

But before fiddling around with those two options, first check if you are on the latest SP/CU.