Getting lock timeout exceed exception while deleting data

Help Query Tuning
1

Getting this below exception while deleting data in batches
batch size is 32000
columnstore table with an index created
concurrent threads 2
Threads belongs to 2 different indexes
exception java.sql.SQLTransientConnectionException: (conn=193194101) Leaf Error (node-memsql-leaf-ag1-2.svc-memsql:3306): Lock wait timeout exceeded; try restarting transaction. Table lock owned by connection id 4445, query open idle transaction →

2

Welcome aboard mnr! :wave: Sorry to hear that you are receiving this error message. Can I ask if you are on the managed or self-hosted service and what version are you running?

3

It is self hosted. 7.6.5 version

4

Hi mnr,

The query is timing out getting the table lock (Table lock owned... part of the error) which happens if more then 5000 rows are getting locked on a particular partition (before this point individual rows are locked instead of the entire table).

You could try raising default_columnstore_table_lock_threshold to avoid the table locking (more info about the locking behavior is available here: Columnstore · SingleStore Documentation).

2 Likes
5

Is there a similar property for rowstore tables? I suspect that we’re running into a similar issue at my org, but I think this is happening during deletes on rowstore tables… (self-hosted 7.5.8)

1 Like
6

Hi @erica.anderson,

Rowstore tables only do row-level locking and never escalate to a full table lock like columnstore tables do, so you’re probably hitting a different issue if you are, indeed, seeing the same error when deleting from rowstore tables.

Can you have a look at this reference and see if your workload falls into any of these categories?

Cheers,
Julie

1 Like
7

Hi Julie,

Thanks for the reference - I’m not positive what category our workload falls into. We have a single app that writes to this particular database, which syncs data sequentially using a CDC framework. So I don’t think it’s deadlocking, but it is multi-threaded so it could be possible. Or perhaps we have uncommitted transactions that we haven’t caught within our application code. It’s really hard to tell when the only info we can see about the blocking query is open idle transaction. Is there any way to trace the original transaction/query?

8

Have you tried to check this: MV_BLOCKED_QUERIES · SingleStore Documentation

It has the ID of the blocking query.