What system settings need verified for optimal use of a renewable resource during a peak processing spike??

[Kenneth Chrapczynski]

Hello CAWA DE Champions!

We have an issue where we submit roughly 600 jobs concurrently withing a 5 minute period that execute "snowsql" and a 1/3 of them fail due to a table lock.

We tried using a renewable resource and this appeared to cause espresso to freeze up. In addition to these jobs running there are a total of 4,000 jobs running in this 5 minute window cycling through all of the "states". The database is being worked hard between midnight and 6 a.m.

I'm sorry we do not have the history to provide for the "freezing" situation. We ended up adding retries upon failure and this seemed to handle the issue.

But, this scenario seems a perfect candidate for a renewable resource.

What software settings (memory, reswait etc.) do we need to optimize to make sure all of the "states" and queuing the backlog of jobs can be handled? Is there anything else we should consider?

FYI - I have also reached out to the support team and dba's within our organization to verify the snowsql database is setup optimally.

Thanks in advanced for your attention to this issue!!

Kenneth Chrapczynski

Here is the error the jobs are encountering:

|   "SQL ERROR": "Statement 'nnnnnnnnnnnnnn' has locked table 'Txxxxxxxxxxxxxxxxxx_LOG_F' in transaction nnnnnnnnnnnnnnn and this lock has not yet been released.\nYour statement 'nnnnnnnnnnnnnnnnnnnnnnnnn' was aborted because the number of waiters for this lock exceeds the 40 statements limit.", |
|   "SQLCODE": 625,  

------------------------------
DARDEN Corporation
Orlando, FL
------------------------------

[Ravi Kiran Kunduri]

We need to check if there is any memory threshold or any other parameter like database connections that is reaching threshold because of which you are seeing this behavior. 

It needs further investigation. May be you can raise a ticket for the same.

-------------------------------------------