We are facing one issue from last week. Anyone has any idea, why this issue is coming back again and again.
What we are doing.
We are doing a ETL process. We transforming Data from Live tables to Archiving tables under the same data source. There are several steps in process, We will find the case whether it can be archived or not, If yes, we will transform the data from Live tables to Archiving tables and we will delete the from Live tables using stored procedure. Procedure contains series of Delete statements.
We are running from last month everyday, we are processing 3000 cases everyday & 40 cases are executing in parallel at a point of time. Everything was smooth. Suddenly the batch was failing to do the process, especially the stored procedure is unable to execute.
Sometimes we are getting, Lock wait timeout exceeded; try restarting transaction. Sometimes we are getting, Communications link failure The last packet successfully received from the server was 10,800,001 milliseconds ago. The last packet sent successfully to the server was 10,800,001 milliseconds ago.
Anyone has any idea about the issue? Please share your thoughts.
Discussion posts and replies are publicly visible
I am able to reproduce this issue in Appian 19.4 version as well. Did any one figured out how to fix this issue.
Every time, when the batch runs it takes 45 minutes - 1 hour to delete the records from 50+ tables in Live. Every time, after the completion of batch, we are reorganising the index's for those tables. After reorg, i haven't faced any issue.
Thank you Rahul for your response. In our case we have two scenarios.
1. Batch process which updates or inserts multiple tables but there are not deletes.
2. On demand process which pulls data from various tables and inserts the data in various tables.
In both the scenarios do you suggest the above as solution or will it work only in case of deleting the data.
Thanks and Regards,
We are on Appian Version 20.1. We are using Appian Cloud Db instance and also started facing this issue.
We have heavy DB ETL process in stored procedures and most of them are executed simultaneously.
Could you please elaborate what exactly did you do to reorganize indexes ? Was it a simple ANALYZE TABLE command or something more ?
© 2022 Appian. All rights reserved.