Clustered Column Store Index: Concurrency with INSERT Operations

  • Article

Clustered Column Store: Insert Operations

As described in the blog  https://blogs.msdn.com/b/sqlserverstorageengine/archive/2014/07/27/clustered-column-store-index-concurrency-and-isolation-level.aspx , the clustered column store index has been optimized for typical DW scenario supporting nightly or trickle data load with fast query performance. Multiple inserts can load the data in parallel concurrently while DW queries are being run in read uncommitted transaction isolation level.

This blog describes locking behavior when data is inserted concurrently. For the scenarios below, we will use the following table

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_CI ON T_ACCOUNT

 

Insert Operations

Let us insert 1 row and see the locks taken. Note, we did not commit the transaction

begin tran

       insert into T_ACCOUNT (accountdescription ) values ('row-1');

 Here are the locks. Note, the new row is inserted into delta rowgroup which is organized as a btree in traditional row storage format. There is a new resource ROWGROUP in the context of CCI. The current transaction has taken IX lock on the ROWGROUP

 

Now, let us insert another row in another session as follows and look at the lock

begin tran

       insert into T_ACCOUNT (accountdescription ) values ('row-2');

Note, that the second transaction in session-55, also inserted the row into the same rowgroup. In other words, concurrent inserts can load the data into same rowgroup without blocking each other.

 

 In summary, the insert into CCI does not block other concurrent inserts and concurrent inserts load data into the same delta rowgorup. In the next blog, we will look into BulkLoad Operations

Thanks

Sunil Agarwal