Re: Select for Update in multithreaded application

"Nick Selwyn" <nick.selwyn_at_fmr.com> wrote:
> 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.

Why? Do you realize that having multiple connections communicating with each other behind the DBMS's back is an excellent way to circumvent ACIDity and create corruption?

> We have a mechanism within the
> app that prevents overlapping reads of the same row, and this serves us
> well so far.

What does overlap mean in this context? You have one thread that reads. How could it overlap with itself?

> 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.

Yeah, locks are like that. You could make an indicator column in the table that says what machine/process curently holds a virtual lock on that row (but of course it would do no good without committing, and if for other reasons this isn't the right place to do the commit you would just be digging the hole deaper), and then both threads know they are the same process so would know they can consider themselves to share the virtual lock. I have no idea why you would need to do this, but if you did want to you could.

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

Either you are doing something very silly, or you are doing something very clever but haven't explained to us why you need to resort to such cleverness.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB