# 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,

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

***

Modified at: December 1, 2023

Source: [/cloud/create-a-database/columnstore/locking-in-columnstores/](https://docs.singlestore.com/cloud/create-a-database/columnstore/locking-in-columnstores/)

(An index of the documentation is available at /llms.txt)