Lock wait timeout exceeded

Hi team,

I have a use case where I need to delete old data. Imagine I have three tables: employee, address, and documents. To delete an employee, I need to store documents and address in history tables then i need to break the dependency (foreign key mappings) from documents and address . Then, I need to move the employee details to a history table before deleting the employees.


So, I have a process model which executes these steps in individual query database nodes, like moving details to the history table using inserts, then breaking dependency using update, then moving employee details to history using inserts, and finally deleting employees using delete.

These tables are just examples; I'm doing a similar process which I cannot fully explain here. The thing is, my production table has 110GB of data, so performing this operation for 3k employees took more than 3.5 hours. So, I planned to process in parallel using startprocess MNI. It is working well, but sometimes I get the error "There was a problem executing the SQL query. SQL Message: Lock wait timeout exceeded; try restarting transaction", then I need to restart the node again. I know we can use exceptions along with a timer, but I need something that, when the query database node gets the red strike , it loops back again to that node because we cannot guess the wait time in PRO it may be larger or smaller. Do you have any suggestions for this, or any new solutions which will help me? if you need additional info let me know.

Thanks in advance!