Stored procedure Error Lock wait timeout exceeded; try restarting transaction.

Hello everyone,

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. 

Thanks

Rahul Gundu