Locking in Columnstores
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. Consider the following query,
UPDATE stock JOIN product ON stock.qty = 10 AND stock.id = product.id SET ...
This query locks all the rows of the table
stock.qty = 10, including the rows where
stock.id is not equal to
product.id. Alternatively, use a single table filter to trim the number of rows locked.
Suppose that a database
errors_db containing eight partitions has the
app_errors table that is defined as follows.
CREATE TABLE app_errors ( error_id INT, app_name TEXT, error_code TEXT, error_date DATE, KEY (error_date) USING CLUSTERED COLUMNSTORE, SHARD KEY (error_id) );
UPDATE example demonstrates row-level locking. Assume the
app_errors 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:
- In the
app_errorstable on partition one, other queries can
DELETEthe rows not having an
- In the
app_errorstable on partition one, other queries may not
DELETEthe rows having an
Overriding Default Locking
DELETE queries use row-level locking when they operate on fewer than 5000 rows in a columnstore table and use partition-level locking when they operate on 5000 or more rows in a columnstore table. In the latter case, all of the table’s rows in the partition are locked.
When you write an
UPDATE or a
DELETE, you can override the default threshold of 5000 by specifying the
OPTION (columnstore_table_lock_threshold = <value>) hint. The
<value> indicates the row count threshold for which partition level locking takes effect.
UPDATE example specifies that
columnstore_table_lock_threshold 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_table_lock_threshold value, you can get higher concurrency, but more memory may be used for locking.