Lock wait timeout exceeded error on insert queries

Help
1

I’m getting the error Lock wait timeout exceeded; try restarting transaction while trying to run these five INSERT queries concurrently.

INSERT IGNORE INTO insert_table SELECT * FROM source_table WHERE event_date = '2023-7-30' 
INSERT IGNORE INTO insert_table SELECT * FROM source_table WHERE event_date = '2023-7-29' 
INSERT IGNORE INTO insert_table SELECT * FROM source_table WHERE event_date = '2023-7-28' 
INSERT IGNORE INTO insert_table SELECT * FROM source_table WHERE event_date = '2023-7-27' 
INSERT IGNORE INTO insert_table SELECT * FROM source_table WHERE event_date = '2023-7-26'

The result of running these five queries is that only one of them finishes while the other 4 fail and I get the lock wait timeout exception.

I detached the pipeline and I don’t see any other active queries running in the active queries UI in portal.singlestore.com. Each query is trying to insert around 3 million rows. None of the queries have overlapping data with each other. The error occurs both with or without the “IGNORE INTO” clause.

Could I please get some help on understanding why I’m getting this error on just these insert queries? Is this due to a deadlock issue because the queries are locking on a different partition that the other query needs?

Any help is appreciated, thank you.

2

Probably those queries are doing lock escalation to get a partition-level lock because lock escalation happens at 5000 individual row locks. Then the commands start waiting on the lock and hit the lock wait timeout (default 60 sec). There might be some possibility that they concurrently try to escalate locks and get in a cyclic wait; not sure about that.

You can check mv_blocked_queries to see who’s waiting for who.

Consider (a) doing the inserts in smaller batches, potentially under 5000 rows and (b) increasing the lock_wait_timeout. Or, even do the INSERTS serially if that meets your performance needs. Or staggering the INSERTs a little would allow somebody to get the escalated lock first which would not allow any cyclic waiting.