Clustered Column Store Index: Concurrency and Isolation Levels

Published Mar 23 2019 01:27 PM 627 Views
Microsoft
First published on MSDN on Jul 26, 2014

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


%3CLINGO-SUB%20id%3D%22lingo-sub-384334%22%20slang%3D%22en-US%22%3EClustered%20Column%20Store%20Index%3A%20Concurrency%20and%20Isolation%20Levels%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-384334%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3E%20First%20published%20on%20MSDN%20on%20Jul%2026%2C%202014%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CH2%20id%3D%22toc-hId-1680720613%22%20id%3D%22toc-hId-1707385667%22%3EClustered%20Column%20Store%20and%20Concurrency%3C%2FH2%3E%3CBR%20%2F%3E%3CP%3EThe%20clustered%20column%20store%20index%20(CCI)%20has%20been%20designed%20for%20Data%20Warehouse%20scenario%20which%20primarily%20involves%3C%2FP%3E%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EWrite%20once%20and%20read%20multiple%20times%20%E2%80%93%20CCI%20is%20optimized%20for%20query%20performance.%20It%20give%20order%20of%20magnitude%20better%20query%20performance%20by%20compressing%20the%20data%20in%20columnar%20format%2C%20processing%20set%20of%20row%20in%20batches%20and%20by%20bringing%20only%20the%20columns%20that%20are%20required%20by%20the%20query.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EBulk%20data%20import%20and%20trickle%20data%20load%20%E2%80%93%20Insert%20Operation%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3CP%3EWhile%20it%20supports%20UPDATE%2FDELETE%20operations%20but%20it%20is%20not%20optimized%20for%20large%20number%20of%20these%20operation.%20In%20fact%2C%20concurrent%20DELETE%2FUPDATE%20can%20cause%20blocking%20in%20some%20cases%20and%20can%20lead%20to%20multiple%20delta%20row-groups.To%20understand%20the%20concurrency%20model%2C%20there%20is%20a%20new%20lock%20resource%2C%20called%20ROWGROUP.%20Let%20us%20see%20how%20locks%20are%20taken%20for%20different%20scenarios.%20I%20will%20walk%20through%20concurrency%20using%20a%20series%20of%20blogs%20starting%20with%20transaction%20isolation%20levels%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CH2%20id%3D%22toc-hId--871436348%22%20id%3D%22toc-hId--844771294%22%3ETransaction%20Isolation%20levels%20Supported%3C%2FH2%3E%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3E%3CSTRONG%3ERead%20Uncommitted%20%3C%2FSTRONG%3E%20%E2%80%93While%20this%20is%20ok%20for%20most%20DW%20queries%2C%20and%20in%20fact%2C%20queries%20running%20on%20PDW%20appliance%20access%20CCI%20under%20read%20uncommitted%20to%20avoid%20blocking%20with%20concurrent%20DML%20operations.%20This%20is%20how%20CCI%20is%20queried%20in%20Analytics%20Platform%20System%2C%20a%20re-branding%20of%20PDW.%20Please%20refer%20to%20the%20%3CSTRONG%3E%20%3C%2FSTRONG%3E%20%3CA%20href%3D%22http%3A%2F%2Fwww.microsoft.com%2Fen-us%2Fserver-cloud%2Fproducts%2Fanalytics-platform-system%2Fdefault.aspx%23fbid%3DCRIMcFvfkD2%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20http%3A%2F%2Fwww.microsoft.com%2Fen-us%2Fserver-cloud%2Fproducts%2Fanalytics-platform-system%2Fdefault.aspx%23fbid%3DCRIMcFvfkD2%3C%2FA%3E%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3E%3CSTRONG%3ERead%20Committed%20%3C%2FSTRONG%3E%20%E2%80%93%20Only%20lock%20based%20implementation%20of%20read%20committed%20isolation%20is%20supported%20which%20can%20get%20blocked%20with%20concurrent%20DML%20transactions.%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3CP%3EIf%20RCSI%20is%20enabled%20on%20the%20database%20containing%20one%20or%20more%20tables%20with%20CCI%2C%20all%20tables%20other%20than%20CCI%20can%20be%20accessed%20with%20non-blocking%20semantics%20under%20read%20committed%20isolation%20level%20but%20not%20for%20CCI%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3E%20Example%3A%20%3C%2FSTRONG%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eselect%20is_read_committed_snapshot_on%2C%20snapshot_isolation_state_desc%2Csnapshot_isolation_state%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Efrom%20sys.databases%20where%20name%3D'AdventureWorksDW2012'%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F97768i8474F98A52F434F8%22%20%2F%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECREATE%20TABLE%20%5Bdbo%5D.%5BT_ACCOUNT%5D(%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%5Baccountkey%5D%20%5Bint%5D%20IDENTITY(1%2C1)%20NOT%20NULL%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%5Baccountdescription%5D%20%5Bnvarchar%5D(50)%20NULL%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E)%20ON%20%5BPRIMARY%5D%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20create%20a%20CCI%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECREATE%20CLUSTERED%20COLUMNSTORE%20INDEX%20ACCOUNT_CCI%20ON%20T_ACCOUNT%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESession-1%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Euse%20AdventureWorksDW2012%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Ego%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20Do%20a%20DML%20transaction%20on%20CCI%20but%20don't%20commit%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Ebegin%20tran%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Einsert%20into%20T_ACCOUNT%20(accountdescription%20)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Evalues%20('value-1')%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESession-2%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20query%20the%20table%20under%20read%20committed%20in%20a%20different%20session%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eset%20transaction%20isolation%20level%20read%20committed%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Ego%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eselect%20*%20from%20t_account%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EYou%20will%20see%20CCI%20query%20is%20blocked%20on%20session-1%20as%20shown%20using%20the%20query%20below%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eselect%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Erequest_session_id%20as%20spid%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eresource_type%20as%20rt%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eresource_database_id%20as%20rdb%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E(case%20resource_type%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EWHEN%20'OBJECT'%20then%20object_name(resource_associated_entity_id)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EWHEN%20'DATABASE'%20then%20'%20'%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EELSE%20(select%20object_name(object_id)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Efrom%20sys.partitions%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Ewhere%20hobt_id%3Dresource_associated_entity_id)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEND)%20as%20objname%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eresource_description%20as%20rd%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Erequest_mode%20as%20rm%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Erequest_status%20as%20rs%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Efrom%20sys.dm_tran_locks%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F97769i0315A02DBB90FCFD%22%20%2F%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEven%20though%20the%20database%20is%20using%20default%20non-blocking%20read%20committed%20isolation%20level%20using%20row%20versioning%2C%20the%20CCI%20is%20accessed%20using%20lock%20based%20implementation%20of%20read%20committed.%3C%2FP%3E%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3E%3CSTRONG%3ESnapshot%20Isolation%20%3C%2FSTRONG%3E%20%E2%80%93%20It%20can%20be%20enabled%20for%20the%20database%20containing%20CCI.%20Any%20disk-based%20table%20other%20than%20CCI%20can%20be%20accessed%20under%20Snapshot%20Isolation%26nbsp%3B%20but%20access%20to%20CCI%20is%20disallowed%20and%20it%20generates%20the%20following%20error%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3CP%3EMsg%2035371%2C%20Level%2016%2C%20State%201%2C%20Line%2026%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESNAPSHOT%20isolation%20level%20is%20not%20supported%20on%20a%20table%20which%20has%20a%20clustered%20columnstore%20index.%3C%2FP%3E%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3E%3CSTRONG%3ERepeatable%20Read%20%3C%2FSTRONG%3E%20%E2%80%93%20Supported%20in%20CCI%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3CP%3Eset%20transaction%20isolation%20level%20repeatable%20read%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Ego%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Ebegin%20tran%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eselect%20*%20from%20t_account%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Ego%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EHere%20are%20the%20locks.%20Note%20it%20takes%20S%20lock%20on%20all%20rowgroups%20as%20we%20are%20doing%20the%20full%20table%20scan%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F97770iDA84AC69874F7776%22%20%2F%3E%3C%2FP%3E%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3E%3CSTRONG%3ESerializable%20%E2%80%93%20%3C%2FSTRONG%3E%20Supported%20in%20CCI%20%3CSTRONG%3E%20%3C%2FSTRONG%3E%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3CP%3Eset%20transaction%20isolation%20level%20serializable%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Ego%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Ebegin%20tran%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eselect%20*%20from%20t_account%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Ego%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EHere%20are%20the%20locks.%20Note%20it%20takes%20S%20lock%20at%20the%20table%20level%20to%20guarantee%20serializable%20Isolation%20level%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F97771i47BDCD79E630E57B%22%20%2F%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EIn%20the%20next%20blog%2C%20I%20will%20discuss%20locks%20taken%20when%20inserting%20rows%20into%20CCI%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EThanks%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESunil%20Agarwal%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%0A%20%0A%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-384334%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Jul%2026%2C%202014%20Clustered%20Column%20Store%20and%20ConcurrencyThe%20clustered%20column%20store%20index%20(CCI)%20has%20been%20designed%20for%20Data%20Warehouse%20scenario%20which%20primarily%20involvesWrite%20once%20and%20read%20multiple%20times%20%E2%80%93%20CCI%20is%20optimized%20for%20query%20performance.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-384334%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESQLServerStorageEngine%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Mar 23 2019 01:27 PM
Updated by: