Deadlock occuring in Clustered Columnstore index

Question

We are using clustered columnstore index in our transaction table holding order fulfillments. This table is regularly updated by different sessions. But, every session is specific to order job number and so, they are not trying to update same row at the same time. But, we are facing deadlock issues due to below scenarios between sessions.

• Row group locking & Page lock
• Row group locking & Row group locking

This is not specific to a stored procedure. It is due to multiple stored procedures updating this table, sequentially one by one, as part of order fulfillment.

The sample schema of the table is very simple:

CREATE TABLE OrderFulfillments
(
    OrderJobNumber           INT NOT NULL,
    FulfilledIndividualID    BIGINT NOT NULL,
    IsIndividualSuppressed   BIT NOT NULL,
    SuppressionReason        VARCHAR(100) NULL
)

I have given sample deadlock graph for your reference. Please let me know, what approach can I take to avoid this deadlock situation. We need clustered Columnstore index in this table, as we are doing aggregation operations to see how many times an Individual been fulfilled already. without columnstore index, it might be slower.

Answer 1

In my case, the deadlock scenario was due to lock escalations happening, as some of the fulfillments were very big and in 10,000s or in 100k ranges and it was causing lock escalation to happen to rowgroup level and in some cases, page level.

I solved this issue by having a temporary table at the very beginning of transactions and work on updates on the temporary table and finally inserting the temporary table related fulfillments information in to this OrderFulfillments. This OrderFulfillments is also being used by temporary table to see how many times the individual is already fulfilled. but, it is shared lock on the top and not exclusive locks.

By going for temporary table, every session is working on their own copy and concurrency issues are resolved.