Solved

JdbcQueryExecutor - Can't execute querynet.snowflake.client.jdbc.SnowflakeSQLException

  • 25 December 2023
  • 1 reply
  • 41 views
Pradeep
Rank
Userlevel 3
Badge +1

How to solve the below error in SDC logs faced by pipeline(s)?

JdbcQueryExecutor - Can't execute querynet.snowflake.client.jdbc.SnowflakeSQLException: Execution error in store procedure PROC_AUTO_MERGE:Statement '01b0b234-0504-da19-0032-6b8704c17d96' has locked table 'TEST_TABLE' in transaction 1701430332198000000 and this lock has not yet been released.Your statement '01b0b234-0504-d944-0032-6b8704c16e32' was aborted because the number of waiters for this lock exceeds the 20 statements limit. At Statement.execute, line 155 position 17

 

icon

Best answer by Pradeep 25 December 2023, 06:09

View original

1 reply

Pradeep
Rank
Userlevel 3
Badge +1

It is likely that the pipeline is executing a stored procedure proc_auto_merge which is locking the table 'TEST_TABLE' due to an on going transaction. It appears that snowflake is maintaining a limited number of waiters for the lock over table in concern and in this case it is 'TEST_TABLE'. Since there are multiple queries which are trying to acquire lock over the same table 'TEST_TABLE', limit of 20 statements is getting exceeded.

 

These queries are possibly run due to the scheduled parallel jobs at the same time. In general, once the queued transactions are complete, jobs should succeed upon retry. Please try any of the below approaches to solve the issue.

  1. Enable pipeline retry option for the underlying pipeline. Retry number depends on your use case of how many jobs or pipelines trying to acquire lock over the same table.
  2. Limit the number of jobs to be triggered at the same time or schedule them to run consecutively.

Reply


Terms & Conditions