Clustered Column Store Index: Concurrency and Isolation Levels

  • Artikel

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 https://www.microsoft.com/en-us/server-cloud/products/analytics-platform-system/default.aspx#fbid=CRIMcFvfkD2
  •  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

Comments

  • Anonymous
    July 29, 2014
    Is it just my impression, or your "create a CCI" is creating a B-Tree clustered index, and not a column store clustered index?

  • Anonymous
    July 29, 2014
    You are right... It was a cut/paste error from my scripts... I fixed it... thanks!