SQL & PL/SQL

Hi All,


In one of my application , I am using SELECT ..... FOR UPDATE id, flag on ORDER and VOUCHER table. Because during this process , we are doing some insertion and updation on both the table, so we do not want other user to change these rows. thats the reason i have placed FOR UPDATE.

In our Business , there are 40 to 50 applications are using these ORDER and VOUCHER table, those all r different environment Like Mainframe , JAVA, .Net,Crysatl Reports, So they will be using these tables very frequently for reporting and some other purpose. These all applications are developed 10 years ago , also I do not know the functionality and i do not have control for other application.

Now the issues is due to this SELECT FOR UPDATE usage in one of the application , our other application is getting failed , and some time blocking / dead lock issues is ORA - 00060 detected.

Also it is very difficult for me to redesign the application. But I am sure that due to this SELECT FOR UPDATE on one of my applciation causing this issues , this JOB is running 9hrs per day.... So because of that all other application detecting blocking / dead lock issues.

I believe that , If we fix the issues in this application , I can rectify the issues. Or do we have any generic solution for this.

Now Can anyone please suggest me ,

1) How can I handle this Exception/Situation
2) Is there any generic Solution to handle this issue across all application in all environment.
3) I do not want my other application to wait / getting failed due to this FOR UPDATE in one of the application. Its impacting my business in a large.

My Ideas :
--------------

1) can any one suggest me that can we remove SELECT FOR UPDATE cursor , and can we take those rows viirtually and can process . so that we can avoid lock .... but I am not sure .. is it possible. or can we use some staging / temp table . Is this will impact performance ???

2) or can we use priortize or Serialization concepts, But i do not have the control over all other applciation , So i dont think its possible. Or can i do it in data base level using Stack/Queue FIFO method... I am not sure how to go ahead ....

Please help / suggest me .

Regards,
Maran A