Clustered Columnstore Indexes – part 8 (“Locking”)

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

In this part we shall play with Locking :)
Lets setup our table with all defaults and with a Clustered Columnstore Index:

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

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

create clustered columnstore index CCL_CCTest
	on dbo.CCTest;
GO

I will be using following code to see what is being locked:

-- What's being locked
SELECT dm_tran_locks.request_session_id,
       dm_tran_locks.resource_database_id,
       DB_NAME(dm_tran_locks.resource_database_id) AS dbname,
       CASE
           WHEN resource_type = 'object'
               THEN OBJECT_NAME(dm_tran_locks.resource_associated_entity_id)
           ELSE OBJECT_NAME(partitions.OBJECT_ID)
       END AS ObjectName,
       partitions.index_id,
       indexes.name AS index_name,
       dm_tran_locks.resource_type,
       dm_tran_locks.resource_description,
       dm_tran_locks.resource_associated_entity_id,
       dm_tran_locks.request_mode,
       dm_tran_locks.request_status
FROM sys.dm_tran_locks
LEFT JOIN sys.partitions ON partitions.hobt_id = dm_tran_locks.resource_associated_entity_id
JOIN sys.indexes ON indexes.OBJECT_ID = partitions.OBJECT_ID AND indexes.index_id = partitions.index_id
WHERE resource_associated_entity_id > 0
  AND resource_database_id = DB_ID()
ORDER BY request_session_id, resource_associated_entity_id 

Lets open a new transaction and insert 1 row into our empty table, without committing the transaction:

begin tran
	insert into dbo.CCTest
		( id, name, lastname )
		values
		( 1, 'SomeName_', 'SomeLastName_' );

I execute the code to see what is being locked and this what I See:
Oh, I can see an IX (Intent Exclusive) lock which is placed on a whole Row Group, in this case Delta Store.
To continue I issue ROLLBACK on the insertion operation.
Note: This operation would block any other insert into this table (unless it is a Bulk Insert, which sometimes would go a different way by creating a new Delta Store and invoking Tuple Mover to make it a compressed Segment). This make one more case for Bulk Insert usage for Clustered Columnstore Indexes, because it is definitely a nice way to avoid blocking.

Lets try it from the different angle, lets insert enough rows to fill out a complete Delta Store – 1048576 rows.

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

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

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

Now lets try to insert one more row from a different window.

begin tran
	insert into dbo.CCTest
		( id, name, lastname )
		values
		( 1, 'SomeName_', 'SomeLastName_' );

What is the result for this operation? Lets check sys.column_store_row_groups DMV and lets see the results of our locking code:

Now it looks like we have a 2 Row Groups - 1 Segment and 1 Delta Store which is closed. This is an expected behavior.
But what about the acquired locks ?
Wow, we are actually blocking both of the Row Groups, which means that even Tuple Mover won't be able to help us - since the IX lock is preventing any further action.
Lets commit the transaction at this point. So we can have a closed and an open Delta Stores. At this point should we issue any more insert commands - they will block only an open Delta Store.

Thats all fine because we are working with the inserts, but what about deletes ?

begin tran

delete from dbo.CCTest
	where id = 104;

For an open Delta Store or for a closed Segment we have an X (Exclusive) lock.

Note: don't forget to commit the transaction.
Note 2: the same type of lock is occurring when executing any UPDATE situation, which makes a lot of sense, since Update is a DELETE + INSERT operation as it was mentioned in the previous posts.

I confess, I did not wanted to give up, and so I tried this operation:

begin tran

update dbo.CCTest with(ROWLOCK)
	set name = 'Updated Name'
	where id = 150;

Only to discover the error message:
Msg 651, Level 16, State 1, Line 11
Cannot use the ROW granularity hint on the table "dbo.CCTest" because locking at the specified granularity is inhibited.
Note: PAGLOCK does not have any effect either, what somehow for a Delta Store could have had some limited success actually.
Note 2: I have also tried using some trace flags in order to force locks to be as minimal as possible, but with no apparent success.

I suspect that there might be some new Trace Flags that might help controlling Locking issues for the Clustered Columnstore Indexes, I guess time will tell when they will appear. ;)

Lock escalation
It seems that at this point we have a quite clear locking situation. But what can be done with existing lock control mechanisms in order to make sure that the locks are as granular as possible ? Having a partitioning would not help, because any given partition would have one or more Row Groups, so this way we can't get any more granularity.

LOCKRES:
As you might have already noticed I have not given up very easily and so I decided to see what else I can discover while playing, and so I decided to execute some really weird & unsupported command:

select top 5 %%lockres%%
from    dbo.CCTest

This is what I have got out of it:
Msg 3625, Level 16, State 203, Line 21
'SE CSI: Virtual Column Support' is not yet implemented.

Ok SQL Server: now I am kind of starting to consider to hold my breath. According to this error message you are not finished with all the Clustered Columnstore Indexes locking implementation - now that is nice :) I am looking forward to the CTP2 and of course to the RTM. :)

Basic conclusions: It definitively looks like Clustered Columnstore Indexes in this version are not to be used for a majority of the OLTP applications, since a Segment or Delta Store lock because of just one data modification operation is something that would kill any online application.

to be continued with Clustered Columnstore Indexes – part 9 ("CTP1 Observations")