Clustered Columnstore Indexes – part 8 (“Locking”)

Continuation from the previous 7 parts, starting from

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

	drop table dbo.CCTest;

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

create clustered columnstore index CCL_CCTest
	on dbo.CCTest;

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

-- What's being locked
SELECT dm_tran_locks.request_session_id,
       DB_NAME(dm_tran_locks.resource_database_id) AS dbname,
           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, AS index_name,
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 )
		( 1, 'SomeName_', 'SomeLastName_' );

I execute the code to see what is being locked and this what I See:
Screen Shot 2013-07-07 at 17.47.48 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
	insert into dbo.CCTest
		( id, name, lastname )
		( @max + @i, 'SomeName_', 'SomeLastName_' );

	set @i = @i + 1;

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

begin tran
	insert into dbo.CCTest
		( id, name, lastname )
		( 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:
Screen Shot 2013-07-07 at 18.25.27
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 ?
Screen Shot 2013-07-07 at 18.22.16 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.
Screen Shot 2013-07-07 at 19.21.34
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.

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")

5 thoughts on “Clustered Columnstore Indexes – part 8 (“Locking”)

  1. Pingback: Clustered Columnstore Indexes – part 1 (“Intro”) | Nikoport

  2. Jilna

    I find your site very informative . Learnt lot about CCI, As I keep progressing, few things bounce off my head as I am new to this.

    In the conclusion you mentioned in this part, it says that CCI in this version cannot be used with OLTP applications. Which version are you referring to?? I am not clear with that part. Can you clarify that

  3. Chandu

    Hi Niko,

    As explained in the blog about error message “Cannot use the ROW granularity hint on the table “dbo.CCTest” because locking at the specified granularity is inhibited.”, even we are also facing same issue with CCI.

    could you suggest is there any solution to fix the Lock issue?

Leave a Reply

Your email address will not be published. Required fields are marked *