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
When a table is locked during a transaction, an error message similar to the following is generated: "Lock wait timeout exceeded; try restarting transaction.
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.
In the following scenario, the
columnstore_ value is set to a higher value in order to avoid locking:
If a database has 455000 rows and 48 partitions (for this example, we'll assume there is no Shard key and the data is evenly divided among the partitions), each partition would have about 9480 rows, which is higher than the default threshold of 5000.
DELETE queries that touch every partition would lock the entire table.
Last modified: December 1, 2023