Hello Folks,
Database - Oracle 11g.
There is a process in which multiple Oracle procedures are running in parallel. First step of each of the procedure is to update the start time and status of that procedure in a control table. That control table is basically used to view the run details.
Its happening randomly, but whats happening is - while the job has triggered all procedures to run in parallel, there are occasions where one of the procedure has failed to run. Log file for that incomplete run says - ORA-00060: deadlock detected while waiting for resource. And its random, for some weeks all run fine and but sometimes one of them fails to run.
Upon investigation by reading through traces, its found that the control table where I update the status and start time of the run for that procedure isn't completed and its just trying to get lock on that table to perform the DML. Because its the same control table which is being updated by other procedures which are running in parallel I do get the point behind it.
However, isn't there any way to ensure that table is always available for all the procedures during their run. There are separate rows in control table for each procedure which gets updated.
Any help, advice would be really appreciable.
Cheers!