CODEWITHSUNDEEP
sqlsqlalchemylockingsnowflake-cloud-data-platform
Sergio Lobo
Sergio Lobo

Reputation: 75

Understanding locks and query status in Snowflake (multiple updates to a single table)

While using the python connector for snowflake with queries of the form

UPDATE X.TABLEY SET STATUS = %(status)s, STATUS_DETAILS = %(status_details)s WHERE ID = %(entry_id)s

, sometimes I get the following message:

 (snowflake.connector.errors.ProgrammingError) 000625 (57014): Statement 'X' has locked table 'XX' in transaction 1588294931722 and this lock has not yet been released.

and soon after that

Your statement X' was aborted because the number of waiters for this lock exceeds the 20 statements limit

This usually happens when multiple queries are trying to update a single table. What I don't understand is that when I see the query history in Snowflake, it says the query finished successfully (Succeded Status) but in reality, the Update never happened, because the table did not alter.

So according to https://community.snowflake.com/s/article/how-to-resolve-blocked-queries I used

SELECT SYSTEM$ABORT_TRANSACTION(<transaction_id>);

to release the lock, but still, nothing happened and even with the succeed status the query seems to not have executed at all. So my question is, how does this really work and how can a lock be released without losing the execution of the query (also, what happens to the other 20+ queries that are queued because of the lock, sometimes it seems that when the lock is released the next one takes the lock and have to be aborted as well).

I would appreciate it if you could help me. Thanks!

Upvotes: 4

Views: 6528

Answers (3)

Mauricio Zee
Mauricio Zee

Reputation: 1

At this point, I was wondering if HYBRID TABLES should fix this type of issue at design level, because it provides row locking instead of table locking in snowflake regular tables, please check the documentation link below:

Snowflake - Hybrid Tables

Hope this help.

Upvotes: 0

AndrewM
AndrewM

Reputation: 246

UPDATE, DELETE, and MERGE cannot run concurrently on a single table; they will be serialized as only one can take a lock on a table at at a time. Others will queue up in the "blocked" state until it is their turn to take the lock. There is a limit on the number of queries that can be waiting on a single lock.

If you see an update finish successfully but don't see the updated data in the table, then you are most likely not COMMITting your transactions. Make sure you run COMMIT after an update so that the new data is committed to the table and the lock is released.

Alternatively, you can make sure AUTOCOMMIT is enabled so that DML will commit automatically after completion. You can enable it with ALTER SESSION SET AUTOCOMMIT=TRUE; in any sessions that are going to run an UPDATE.

Upvotes: 2

Rajib Deb
Rajib Deb

Reputation: 1774

Not sure if Sergio got an answer to this. The problem in this case is not with the table. Based on my experience with snowflake below is my understanding.

In snowflake, every table operations also involves a change in the meta table which keeps track of micro partitions, min and max. This meta table supports only 20 concurrent DML statements by default. So if a table is continuously getting updated and getting hit at the same partition, there is a chance that this limit will exceed. In this case, we should look at redesigning the table updation/insertion logic. In one of our use cases, we increased the limit to 50 after speaking to snowflake support team

Upvotes: 3

Related Questions