CODEWITHSUNDEEP
snowflake-cloud-data-platform
Mohita Verma
Mohita Verma

Reputation: 27

Manual lock in Snowflake table

Can we manually lock a table in Snowflake? I have multiple processes running together which will select data from a table.

If table is not locked all processes running together will fetch same record.

Upvotes: 1

Views: 2672

Answers (2)

MassyB
MassyB

Reputation: 1184

Some statements (like the delete) lock the table and they only release the lock when the transaction is either comitted or rolled back. Example

-- create a dummy table
create table my_schema.my_table(id integer);

-- execute this in two different sessions
begin;
delete from my_schema.my_table where UUID_STRING() = 'acquire lock'; --predicate is always false, no row will be deleted.
CALL SYSTEM$WAIT(30); -- do stuff with my_schema.my_table
commit;

-- execute this in another sessions (a third one)
show locks;

The show locks will show that the first transaction to run holds the lock on the table and the second is waiting for it. It stops at the delete statement.

I think with the above you force the two transactions to run in sequence.

Upvotes: 1

Gokhan Atil
Gokhan Atil

Reputation: 10079

No, it's not possible to lock a table explicitly in Snowflake. You may check the transactions page for more information:

https://docs.snowflake.com/en/sql-reference/transactions.html#resource-locking

It seems you have some processes running in parallel, but you want them to run in sequence. If this is the case, you better change your workflow.

Upvotes: 1

Related Questions