Locking in Columnstores
On this page
By default, UPDATE
and DELETE
queries lock columnstore tables at the row level.
The following example demonstrates how multi-table filters may lock rows that do not match the filters.
UPDATE stock JOIN product ON stock.qty = 10 AND stock.id = product.id SET ...
This query locks all the rows of the table stock
where stock.
, including the rows where stock.
is not equal to product.
.
Suppose that a database errors_
containing eight partitions has the app_
table that is defined as follows.
CREATE TABLE app_errors (error_id INT,app_name TEXT,error_code TEXT,error_date DATE,SORT KEY (error_date),SHARD KEY (error_id));
The following UPDATE
example demonstrates row-level locking.app_
table on partition one contains 4500 records having an app_
of App1
.
UPDATE app_errors SET error_code = 'ERR-2000'WHERE app_name = 'App1';
While this query is running:
-
In the
app_
table on partition one, other queries canerrors UPDATE
andDELETE
the rows not having anapp_
value ofname App1
. -
In the
app_
table on partition one, other queries may noterrors UPDATE
andDELETE
the rows having anapp_
value ofname App1
.
Overriding Default Locking
By default, UPDATE
and DELETE
queries on a columnstore table use row-level locking when they operate on fewer than default_
rows in a partition and use partition-level locking when they operate on default_
rows or more rows in a partition.
The default for default_
is 0, meaning that a value of 5000 is used.
For an UPDATE
or a DELETE
query, you can override the default threshold of default_
by specifying the OPTION (columnstore_
hint.<value>
indicates the row count threshold for which partition level locking takes effect.
The following UPDATE
example specifies that columnstore_
is 4000.
UPDATE app_errors SET error_code = 'ERR-2000'WHERE app_name = 'App1' OPTION (columnstore_table_lock_threshold = 4000);`
When you specify a higher columnstore_
value, you can get higher concurrency, but more memory may be used for locking.
Last modified: August 22, 2022