Clustered Column Store and Concurrency
The clustered column store index (CCI) has been designed for Data Warehouse scenario which primarily involves
- Write once and read multiple times – CCI is optimized for query performance. It give order of magnitude better query performance by compressing the data in columnar format, processing set of row in batches and by bringing only the columns that are required by the query.
- Bulk data import and trickle data load – Insert Operation
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
Transaction Isolation levels Supported
- Read Uncommitted –While this is ok for most DW queries, and in fact, queries running on PDW appliance access CCI under read uncommitted to avoid blocking with concurrent DML operations. This is how CCI is queried in Analytics Platform System, a re-branding of PDW. Please refer to the http://www.microsoft.com/en-us/server-cloud/products/analytics-platform-system/default.aspx#fb...
- Read Committed – Only lock based implementation of read committed isolation is supported which can get blocked with concurrent DML transactions.
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
Example:
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
Session-1
use AdventureWorksDW2012
go
-- Do a DML transaction on CCI but don't commit
begin tran
insert into T_ACCOUNT (accountdescription )
values ('value-1');
Session-2
-- query the table under read committed in a different session
set transaction isolation level read committed
go
select * from t_account
You will see CCI query is blocked on session-1 as shown using the query below
select
request_session_id as spid,
resource_type as rt,
resource_database_id as rdb,
(case resource_type
WHEN 'OBJECT' then object_name(resource_associated_entity_id)
WHEN 'DATABASE' then ' '
ELSE (select object_name(object_id)
from sys.partitions
where hobt_id=resource_associated_entity_id)
END) as objname,
resource_description as rd,
request_mode as rm,
request_status as rs
from sys.dm_tran_locks
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.
- Snapshot Isolation – It can be enabled for the database containing CCI. Any disk-based table other than CCI can be accessed under Snapshot Isolation but access to CCI is disallowed and it generates the following error
Msg 35371, Level 16, State 1, Line 26
SNAPSHOT isolation level is not supported on a table which has a clustered columnstore index.
- Repeatable Read – Supported in CCI
set transaction isolation level repeatable read
go
begin tran
select * from t_account
go
Here are the locks. Note it takes S lock on all rowgroups as we are doing the full table scan
- Serializable – Supported in CCI
set transaction isolation level serializable
go
begin tran
select * from t_account
go
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
Thanks
Sunil Agarwal