Select for Update in multithreaded application

Greetings

I have a multithreaded application where one thread is responsible for reading from the database and another thread is responsible for inserting or updating the same database. We have a mechanism within the app that prevents overlapping reads of the same row, and this serves us well so far.

We now need to be able to run multiple instances of the application simultaneously, and therefore need a mechanism that allows locking of rows between the different processes to prevent overlapping reads between the different instances.

We had thought about using SELECT FOR UPDATE NOWAIT as the inter-process lock, but have recently discovered that this has a problem in our existing setup, where the lock would be obtained by one thread and released by another thread. The symptom is that the update thread cannot get access to the table to update it as the reader thread has the lock.

Has anyone else run across this issue, and if so, could they shed some light on the matter?

We are running Oracle 9.2.0.7 on AIX5.2, but for legacy reasons are using Oracle7 APIs.

Thank you

Nick Received on Mon Jul 31 2006 - 12:12:21 CDT