A query is in BLOCKED status in Snowflake

You happen to see one of your queries has a BLOCKED status on the History page in the Snowflake web interface. It simply means that the query is attempting to acquire a lock on a table that is already locked by another transaction

Account administrators can view all locks, transactions, and session 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 name that it is attempting to lock. 
• Also, 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. (This is the conflicting transaction)
  
• Now navigate to the History page, 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

DataFreak

posted on 24 Oct 18