sql server - Deadlock occuring in Clustered Columnstore index -

we using clustered columnstore index in our transaction table holding order fulfillments. table regularly updated different sessions. but, every session specific order job number , so, not trying update same row @ same time. but, facing deadlock issues due below scenarios between sessions.

• row group locking & page lock
• row group locking & row group locking

this not specific stored procedure. due multiple stored procedures updating table, sequentially 1 one, part of order fulfillment.

the sample schema of table 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 reference. please let me know, approach can take avoid deadlock situation. need clustered columnstore index in table, doing aggregation operations see how many times individual been fulfilled already. without columnstore index, might slower.

you assume nolock same no locking...that incorrect.

nolock equivalent readuncommitted.

• readuncommitted , nolock hints apply data locks.

all queries, including readuncommitted , nolock hints, acquire sch-s (schema stability) locks during compilation , execution. because of this, queries blocked when concurrent transaction holds sch-m (schema modification) lock on table.

for example, data definition language (ddl) operation acquires sch-m lock before modifies schema information of table.

any concurrent queries, including running readuncommitted or nolock hints, blocked when attempting acquire sch-s lock. conversely, query holding sch-s lock blocks concurrent transaction attempts acquire sch-m lock.

readuncommitted , nolock cannot specified tables modified insert, update, or delete operations. sql server query optimizer ignores readuncommitted , nolock hints in clause apply target table of update or delete statement.

you can minimize locking contention while protecting transactions dirty reads of uncommitted data modifications using either of following:

• read committed isolation level read_committed_snapshot database option set on.

• snapshot isolation level. more information isolation levels, see set transaction isolation level (transact-sql).

https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table

• understand how indexes structured can cause blocking if say, select statement requires entire page update modifying concurrently.

• limit variables upon testing.

• consider splitting dml sections. may find optimal range performing concurrent modifications of table data.