3
0
During this post we will discuss about the TRANSACTION LOCKS in snowflake. Recently we have executed an update statement against a table. Ideally the statement should have finished in millisecond. But to our surprise the statement got executed for a longer time without successful. In order to validate the status, we checked into the History view and observe status was BLOCKED.
Query stuck:
update invoice set invc_cd = 'AB' where invc_no = 4;
This indicates that Query is trying to acquire the lock on table, but it seems table was already LOCKED by other statement.
By running below command, we saw below two rows in output.
SHOW LOCKS;
From the below statement we have identified the Transaction Locks query:
There can be the possible chances someone has executed the transaction in below way and not applied COMMIT;
begin;
update invoice set invc_cd = 'PP' where invc_no = 1;
Now we will abort this transaction so that LOCK on the table would be release.
So once we kill the transaction, the Query which was waiting gets complete.
LOCK _TIMEOUT:
Now another question comes, how long the Query will stuck in Waiting Status. Do we have any threshold that after this time Query gets killed automatically or will Snowflake will report error.
Yes ,Snowflake provides LOCK_TIMEOUT parameter
- LOCK_TIMEOUT parameter decides how long the query will wait to acquire the lock on table before timing out and aborting the statement.
- A value of 0 turns off lock waiting i.e. the statement must acquire the lock immediately or abort.
- Default Value: 43200 seconds (12 hours)
- This parameter set Session level, Account level or at the User level. The Session level setting takes precedence over User level and the User level setting takes precedence over the Account level setting
So for this use case, will set this parameter to 20 Seconds:
alter account set LOCK_TIMEOUT = 20
Now try to replicate the above scenario and notice what happens:
begin;
update invoice set invc_cd = 'PP' where invc_no = 1;
Run another statement in new session:
update invoice set invc_cd = 'AB' where invc_no = 4;
Observe the Locks.
After 20 seconds, second Query gets kill automatically:
So we can see only one transaction is HOLDING the LOCK while the second transaction no longer exists.
