Locking in Columnstores
On this page
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., including the rows where
stock. is not equal to
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));
UPDATE example demonstrates row-level locking.
app_ table on partition one contains 4500 records having an
UPDATE app_errors SET error_code = 'ERR-2000'WHERE app_name = 'App1';
While this query is running:
app_table on partition one, other queries can
DELETEthe rows not having an
app_table on partition one, other queries may not
DELETEthe rows having an
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.
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.
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