Snowflake update statements locks the entire table and queues other update statements

Question

We have a use case where we need to execute multiple update statements(each updating different set of rows) on the same table in snowflake without any latency caused by queuing time of the update queries. Currently, a single update statements takes about 1 min to execute and all the other concurrent update statements are locked (about 40 concurrent update statements) and are queued, so the total time including the wait time is around 1 hour but the expected time is around 2 mins( assuming all update statements execute at the same time and the size of warehouse supports 40 queries at the same time without any queueing). What is the best solution to avoid this lock time? We've considered the following two options :-

1. Make changes in the application code to batch all the update statements and execute as one query - Not possible for our use case.
2. Have a separate table for each client (each update statement, updates rows for different clients in the table) - This way, all the update statements will be executing in separate table and there won't be any locks.

Is the second approach the best way to go or is there any other workaround that would help us reduce the latency of the queueing time?

Answer 1

The scenario is expected to happen since Snowflake locks table during update.

Option 1 is ideal to scale in data model. But since you can't make it happen, you can go by option 2.

You can also put all the updates in one staging table and do upsert in bulk - Delete and Insert instead of update. Check if you can afford the delay.

But if you ask me, snowflake should not be used for atomic update. It has to be an upsert (delete and insert and that too in bulk). Atomic updates will have limitations. Try going for a row based store like Citus or MySQL if your usecase allows this.