By default, UPDATE and DELETE queries lock columnstore tables at the row level.

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)
  );

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.

Overriding Default Locking

By default, UPDATE and 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.

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.

Was this Article Helpful?

Thanks For Your Feedback!