The clustered column store index (CCI) has been designed for Data Warehouse scenario which primarily involves
While it supports UPDATE/DELETE operations but it is not optimized for large number of these operation. In fact, concurrent DELETE/UPDATE can cause blocking in some cases and can lead to multiple delta row-groups.To understand the concurrency model, there is a new lock resource, called ROWGROUP. Let us see how locks are taken for different scenarios. I will walk through concurrency using a series of blogs starting with transaction isolation levels
If RCSI is enabled on the database containing one or more tables with CCI, all tables other than CCI can be accessed with non-blocking semantics under read committed isolation level but not for CCI
select is_read_committed_snapshot_on, snapshot_isolation_state_desc,snapshot_isolation_state
from sys.databases where name='AdventureWorksDW2012'
CREATE TABLE [dbo].[T_ACCOUNT](
[accountkey] [int] IDENTITY(1,1) NOT NULL,
[accountdescription] [nvarchar](50) NULL
) ON [PRIMARY]
-- create a CCI
CREATE CLUSTERED COLUMNSTORE INDEX ACCOUNT_CCI ON T_ACCOUNT
-- Do a DML transaction on CCI but don't commit
insert into T_ACCOUNT (accountdescription )
-- query the table under read committed in a different session
set transaction isolation level read committed
select * from t_account
You will see CCI query is blocked on session-1 as shown using the query below
request_session_id as spid,
resource_type as rt,
resource_database_id as rdb,
WHEN 'OBJECT' then object_name(resource_associated_entity_id)
WHEN 'DATABASE' then ' '
ELSE (select object_name(object_id)
END) as objname,
resource_description as rd,
request_mode as rm,
request_status as rs
Even though the database is using default non-blocking read committed isolation level using row versioning, the CCI is accessed using lock based implementation of read committed.
Msg 35371, Level 16, State 1, Line 26
SNAPSHOT isolation level is not supported on a table which has a clustered columnstore index.
set transaction isolation level repeatable read
select * from t_account
Here are the locks. Note it takes S lock on all rowgroups as we are doing the full table scan
set transaction isolation level serializable
Here are the locks. Note it takes S lock at the table level to guarantee serializable Isolation level
In the next blog, I will discuss locks taken when inserting rows into CCI
Is it just my impression, or your "create a CCI" is creating a B-Tree clustered index, and not a column store clustered index?
You are right... It was a cut/paste error from my scripts... I fixed it... thanks!