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,

SQL

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.

SQL

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.

SQL

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

SQL

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?

Verification instructions

Note: You must install cosign to verify the authenticity of the SingleStore file.

Use the following steps to verify the authenticity of singlestoredb-server, singlestoredb-toolbox, singlestoredb-studio, and singlestore-client SingleStore files that have been downloaded.

You may perform the following steps on any computer that can run cosign, such as the main deployment host of the cluster.

(Optional) Run the following command to view the associated signature files.
Shell
```
curl undefined
```
Download the signature file from the SingleStore release server.
- Option 1: Click the Download Signature button next to the SingleStore file.
- Option 2: Copy and paste the following URL into the address bar of your browser and save the signature file.
- Option 3: Run the following command to download the signature file.
  Shell
```
curl -O undefined
```

After the signature file has been downloaded, run the following command to verify the authenticity of the SingleStore file.

Shell

echo -n undefined |
    cosign verify-blob --certificate-oidc-issuer https://oidc.eks.us-east-1.amazonaws.com/id/CCDCDBA1379A5596AB5B2E46DCA385BC \
      --certificate-identity https://kubernetes.io/namespaces/freya-production/serviceaccounts/job-worker \
      --bundle undefined \
      --new-bundle-format -

Verified OK