With Talend executing parallel merge statements on a single table in snowflake with 32 threads, the process is failing showing error as "statement X' was aborted because the number of waiters for this lock exceeds the 20 statements limit".
Now my question is how can I efficiently use the parallel threads in Talend so that it should not cross this limit in snowflake?
Will 20 thread always create a maximum of 20 locked transaction (1 holding, 19 waiting) at a particular timestamp or can it go beyond 20?
Are the locks in snowflake directly proportional to the number of DMLs (in these case merge statements) at any particular timestamp?
How much time snowflake waits for the waiting locks to wait to get the other holding lock to finish before it automatically aborts? Since the others DMLs are waiting to get the exclusive lock, which means they are being idle so will snowflake abort the transactions after 4 hours of it's start, if the holding transactions didn't complete in 4 hours?
if you really think you need more waiters than 20 you can always write to the Snowflake support and ask for the value to be increased.
As far as the transaction limits go - to have snowflake abort anything you need to set the timeout: https://docs.snowflake.com/en/sql-reference/parameters.html#lock-timeout https://docs.snowflake.com/en/sql-reference/parameters.html#statement-timeout-in-seconds https://docs.snowflake.com/en/sql-reference/parameters.html#statement-queued-timeout-in-seconds