Locking in Columnstores
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. 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
where 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, SORT KEY (error_date), SHARD KEY (error_id) );
The following UPDATE
example demonstrates row-level locking. Assume the app_errors
table on partition one contains 4500 records having an app_name
of App1
.
UPDATE app_errors SET error_code = 'ERR-2000' WHERE app_name = 'App1';
While this query is running:
In the
app_errors
table on partition one, other queries canUPDATE
andDELETE
the rows not having anapp_name
value ofApp1
.In the
app_errors
table on partition one, other queries may notUPDATE
andDELETE
the rows having anapp_name
value ofApp1
.
Overriding Default Locking
By default, UPDATE
and DELETE
queries on a columnstore table use row-level locking when they operate on fewer than default_columnstore_table_lock_threshold
rows in a partition and use partition-level locking when they operate on default_columnstore_table_lock_threshold
rows or more rows in a partition. In the latter case, all of the table’s rows in the partition are locked.
The default for default_columnstore_table_lock_threshold
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_columnstore_table_lock_threshold
by specifying the OPTION (columnstore_table_lock_threshold = <value>)
hint. The <value>
indicates the row count threshold for which partition level locking takes effect.
The following 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.