I am running a merge query in snowflake but whenever I run that query after 10-15 seconds it shows the status of the query as "Blocked".
So my question is if I let this query run will it complete? or is the query that has been blocked and it wont complete ever.
My current understanding is that when a query shows blocked status then it means the resource (table) is blocked and we cant run another query on that same table until this query completes. TIA :)
CodePudding user response：
That's correct but there might be other scenarios due to which the query is blocked.
On the History page in the Snowflake web interface, you could notice that one of your queries has a BLOCKED status. The status indicates that the query is attempting to acquire a lock on a table or partition that is already locked by another transaction.
Account administrators (ACCOUNTADMIN role) can view all locks, transactions, and sessions with:
SHOW LOCKS IN ACCOUNT; This command displays all locked objects, as well as all queries waiting for locks. You should see your blocked queries have a status of WAITING, along with the table that it is attempting to lock. Look for the transaction that has a HOLDING status, with a lock on the target table. Note the session and transaction IDs for that lock.
You can view the query history of that session on the History page by filtering on the session ID.
If the session is still available, you can execute a COMMIT or ROLLBACK statement to end the transaction and release the locks. If the session is no longer available, you can release your unintended lock by using the transaction ID from the SHOW LOCKS command to execute:
SELECT SYSTEM$ABORT_TRANSACTION(<transaction_id>);
Account administrators can execute this statement on any user's transactions.
CodePudding user response：
The query obviously can't run until it is unblocked. Whether the query will eventually be run or be cancelled (once the table is unblocked) is based on the STATEMENT_QUEUED_TIMEOUT_IN_SECONDS parameter. This link provides a reasonable explanation: link