Skip to main content

SingleStoreDB v7.8

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.

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.

In this section: