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

세션 1

세션 2

세션3

use AdventureWorksDW2012

go

-- Do a DML transaction on CCI but don't commit

begin tran

    insert into T_ACCOUNT (accountdescription )

    values ('value-1');

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

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

go

begin tran

select * from t_account

set transaction isolation level serializable

go

begin tran

    select * from t_account

go