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 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.

UPDATE app_errors SET error_code = 'ERR-2000'
  WHERE app_name = 'App1' OPTION (columnstore_table_lock_threshold = 4000);`