Clustered Columnstore Indexes – part 7 (“Transaction Isolation”)

Continuation from the previous 6 parts, starting from https://www.nikoport.com/2013/07/05/clustered-columnstore-indexes-part-1-intro/

In this part we shall be dedicating to the Transactional support for Clustered Columnstore Indexes:

Isolation Levels:
As I have already mentioned in part 2, Isolation support for Clustered Columnstore Indexes is quite impressive, almost every Isolation Level of SQL Server is supported: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ & SERIALIZABLE are all in. READ_COMMITED_SNAPSHOT is supported as well.

Lets try to verify the announced support for all of those isolation levels in the next script, which is inserting 50.000 rows using each of the supported isolation levels:

IF OBJECT_ID('dbo.CCTest', 'U') IS NOT NULL
	drop table dbo.CCTest;

create table dbo.CCTest(
	id int not null,-- primary key clustered,
	name varchar(50) not null,
	lastname varchar(50) not null );
GO

create clustered columnstore index CCL_CCTest
	on dbo.CCTest;
GO


-- Read Uncommited:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

declare @i as int;
declare @max as int;
select @max = isnull(max(id),0) from dbo.CCTest;
set @i = 1;

begin tran
while @i <= 50000
begin
	insert into dbo.CCTest
		( id, name, lastname )
		values
		( @max + @i, 'SomeName_', 'SomeLastName_' );

	set @i = @i + 1;
end;
commit;

-- Read Commited:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

select @max = isnull(max(id),0) from dbo.CCTest;
set @i = 1;

begin tran
while @i <= 50000
begin
	insert into dbo.CCTest
		( id, name, lastname )
		values
		( @max + @i, 'SomeName_', 'SomeLastName_' );

	set @i = @i + 1;
end;
commit;

-- Repeatable Read:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

select @max = isnull(max(id),0) from dbo.CCTest;
set @i = 1;

begin tran
while @i <= 50000
begin
	insert into dbo.CCTest
		( id, name, lastname )
		values
		( @max + @i, 'SomeName_', 'SomeLastName_' );

	set @i = @i + 1;
end;
commit;

-- Serialiazable:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

select @max = isnull(max(id),0) from dbo.CCTest;
set @i = 1;

begin tran
while @i <= 50000
begin
	insert into dbo.CCTest
		( id, name, lastname )
		values
		( @max + @i, 'SomeName_', 'SomeLastName_' );

	set @i = @i + 1;
end;
commit;

This script works without any problem.
Lets check now on the READ_COMMITED_SNAPSHOT isolation level, which is set on the database level:

ALTER DATABASE ColumnStore SET READ_COMMITTED_SNAPSHOT ON;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

declare @i as int;
declare @max as int;
select @max = isnull(max(id),0) from dbo.CCTest;
set @i = 1;

begin tran
while @i <= 50000
begin
	insert into dbo.CCTest
		( id, name, lastname )
		values
		( @max + @i, 'SomeName_', 'SomeLastName_' );

	set @i = @i + 1;
end;
commit;

ALTER DATABASE ColumnStore SET READ_COMMITTED_SNAPSHOT OFF;

Works fine :) All 250.000 rows are successfully inserted into dbo.CCTest table and we are having just one Row Group - a Delta Store which is still open.

And what about the unsupported SNAPSHOT isolation level?

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

declare @i as int;
declare @max as int;
select @max = isnull(max(id),0) from dbo.CCTest;
set @i = 1;

begin tran
while @i <= 50000
begin
	insert into dbo.CCTest
		( id, name, lastname )
		values
		( @max + @i, 'SomeName_', 'SomeLastName_' );

	set @i = @i + 1;
end;
commit;

Msg 35371, Level 16, State 1, Line 120
SNAPSHOT isolation level is not supported on a table which has a clustered columnstore index.
Ok, confirmed that it is not supported. I can only guess the reasons behind it, and as soon as find them - this post will get updated.

To be updated. :)

to be continued with Clustered Columnstore Indexes – part 8 ("Locking")