Clustered Column Store: Insert Operations
As described in the blog http://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 INDEX ACCOUNT_CI ON T_ACCOUNT (ACCOUNTKEY)
Let us insert 1 row and see the locks taken. Note, we did not commit the transaction
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
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