Hi,
This is Mahipal from Oracle. We are using JDev11.1.1.7.
Problem Description
We have a ADF web service (SOAP), in which it will check whether record exists in DB based on input params, if exists, it updates the record, if not, it will create the record.
When we invoke this service using multiple request in parallel, we are running into issues.
1.If the record doesn’t exists, it will try to create the record. So, in parallel requests, create is being called multiple times and commit will be successful only for the first time.
For other requests, its throwing DB constraint (we added this to remove the duplicate records creation when service got invoked with multiple requests in parallel, with sequential execution, we are not running into any issues)
violations which we are handling in catch block and returning the existing records’ id. This is working fine and it is making sure that duplicate records are not being created in DB. Though we are returning the existing record’s id,
but in response, instead of returning that id, its actually throwing the exceptions which we have caught in catch block. This exception is being thrown in post processing of the web service method.
Till web service method execution, there is no issue as we have caught that exception in catch, but in post processing (which are being called from ADF framework), the actual exception is still being thrown.
So, how do we handle this scenario? We would like to see the retuned row id in the web service response not the exception.
2.In case of update (running the multiple requests in parallel for an existing record), for the first request its fine. But for the sub sequent requests, we are running into either of below two issues
a.Failed to lock the record, another user holds the lock
b.Another user has changed the row with primary key
Again in catch block we are catching these exceptions and only allowing the update to be done in one request (as per business needs). For other requests, we are retuning the existing record’s id. Here also the same thing is happening as like point 1.
It is throwing the exception is post method processing though we are returning the id from web service method. Tried with pessimistic locking, still the same.
Kindly provide your suggestion in this. Any pointers would be really helpful.
Thanks & Regards,
Mahipal