Join the SingleStore Community Today
Get expert advice, develop skills, and connect with others.

Locking in Columnstores

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.