Acquiring Row Locks

Consider the following example table:

CREATE TABLE tabs (c1 INT PRIMARY KEY, c2 INT, c3 INT, KEY(c2));

There are two different ways queries acquire row locks in SingleStore:

1. The query is scanning a secondary key to run the write operation. For example, the query DELETE FROM tabs WHERE c2 = 1 will seek and scan the secondary index on c2 to run the DELETE operation. When the query finds a row where c2 = 1, it will seek into the primary key to acquire the row lock before marking the row as deleted.

2. The query is scanning the primary key to run the write operation. For example, the query DELETE FROM tabs WHERE c1 = 1 will seek and scan the primary key on c1 to run the DELETE operation. Since the query is already scanning the primary key, the index scan itself will acquire row locks to avoid an extra seek into the primary key (as is done when DELETE is scanning the secondary key).

While scanning the primary key, the lock will be acquired before running any filters in the WHERE clause since the index scan operation itself cannot run those filters. The scan runs inside the storage layer, and the filters are run inside the query execution layer. This means even rows that will not be deleted are locked. For example, the query DELETE FROM tabs WHERE c1 = 1 AND c3 = 1 locks rows where c3 is not 1. Although SingleStore releases the row lock if the filter doesn’t match (for example where c3 is not 1), for applications running concurrent DELETE queries, this operation is often not fast enough to avoid deadlocks. For example, the queries DELETE FROM tabs WHERE c1 = 1 AND c3 = 1 and DELETE FROM tabs WHERE c1 = 1 AND c3 = 2 will never deadlock if c1 is not the primary key.

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.

For columnstore tables, there is no difference in locking when filtering on key or non-key columns. For rowstore tables, filtering on key and non-key columns follow different code paths, which may affect the locks acquired.

Extra Deadlocks in SingleStore Due to Sharding

In a single box database, concurrent write operations on a table that scan the same index in a specific direction (either forward or reverse) cannot deadlock. For example, consider a transaction A that gets a lock on row(n) and is waiting for a lock on row(n+1). Now, consider another transaction B that gets a lock on row(n+1) and is waiting for a lock on row(n). If both the transactions scan the index in the same order, they will see the rows in the same order, and this deadlock ordering is impossible.

In SingleStore, the index is partitioned up amongst shards that are spread across a cluster of leaf nodes. Even if the threads are scanning the index in the same order on each partition, they can still deadlock. For example, consider two DELETE commands that run at the same time, starting on two different partitions (say partitions 0 and 1). Let’s say, one of the two DELETE operations runs first on partition 0 and the second runs first on partition 1. Now, they may hit the lock acquisition deadlock in the example above.

Single partition DELETE operations don’t have this issue. You can achieve a single partition DELETE operation by using a shard key filter in the WHERE clause.

If the deadlocks are too frequent, see the ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction topic.