CODEWITHSUNDEEP
snowflake-cloud-data-platform
leed38
leed38

Reputation: 326

how are concurrent queries handled in snowflake?

For example, if I have a task that's inserting rows into a table while another task is truncating the same table, what happens?

I'm asking because I have a task that runs every minute which inserts rows into a table and then a lambda that reads and truncates the same table that runs every minute. I know snow tasks and event bridge don't run at every minute on the dot so I haven't really run into this issue yet but I'm thinking it'll happen eventually.

How does snowflake handle this?

Upvotes: 1

Views: 2626

Answers (2)

Eric Lin
Eric Lin

Reputation: 1510

It is the same concept in other SQL engines, that lock on resources will be placed.

In the Snowflake world, INSERT will have PARTITION level locking, because most of the INSERT statements write only new partitions.

Please see the below doc: https://docs.snowflake.com/en/sql-reference/transactions.html#resource-locking

If the INSERT query is submitted before the TRUNCATE, then the TRUNCATE will have to wait until the INSERT query finishes. They can't be operated at the same time on the same resource.

See the screenshot below, the first query was the INSERT, which was HOLDING the PARTITION level lock, while the second query was the TRUNCATE, which was in the WAITING state:

enter image description here

Upvotes: 1

NickW
NickW

Reputation: 9778

The table will be locked by the first transaction that runs and subsequent transactions will be queued until the preceding transaction(s) complete.

BTW (and this may be the point of your question) having two processes like this operate independently doesn’t seem like a good design - as the lambda process seems to be logically dependent on the task.

Upvotes: 0

Related Questions