Sign inTry Free

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. 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 can UPDATE and DELETE the rows not having an app_name value of App1.

  • In the app_errors table on partition one, other queries may not UPDATE and DELETE the rows having an app_name value of App1.

When a table is locked during a transaction, an error message similar to the following is generated: "Lock wait timeout exceeded; try restarting transaction. Table lock owned by connection id <ID>, query <query info>"

Overriding Default Locking

By default, UPDATE and DELETE queries on a columnstore table use row-level locking when they operate on fewer than 5000 rows in a partition and use partition-level locking when they operate on 5000 rows or more rows in a partition. In the latter case, all of the table’s rows in the partition are locked.

For an UPDATE or a DELETE query, 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.

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.

In the following scenario, the columnstore_table_lock_threshold 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. In this case, a set of UPDATE or DELETE queries that touch every partition would lock the entire table. Setting the threshold value to a value higher than 9480 (say, 12000) would cause the database to use row locking until the number of rows per partition hit 12000.

Last modified: December 1, 2023

Was this article helpful?

On this page

Was this article helpful?