Re: multi-process cooperation and record locking

select...for update between multiple processes will work as long as each select returns a different set of rows.

In article <38859372.6531730_at_news.primenet.com>,   dontspamme_at_goaway.fu (Steve Shoecraft) wrote:
>
> I found out why the cursor open is incorrect. It seems that
> when process A opens the cursor, it is selecting ALL of the records
> (checked = 'N') for update (locking them). Ok, so now I know that
> this is happening, it still leaves to the problem at hand: how do I
> have process A select (for update) 1 record, and process B select (for
> update) a different record?
>
> On Mon, 17 Jan 2000 21:50:42 GMT, dontspamme_at_goaway.fu (Steve
> Shoecraft) wrote:
>
> >
> > I am writing an application in C which access an Oracle 8.0.5
> >database (linux) using ProC (embedded SQL). I am having a problem
> >with multiple processes and record locking.
> >
> > There are usually 8 instances of the program running at any
> >given time. The program uses a cursor to access a table to get a
> >piece of data and must be able to lock this record while it is
> >processing (no other processes can lock/read the record while the
> >process has it locked). Here's what a code snippit looks like:
> >
> > EXEC SQL DECLARE unchecked_cursor CURSOR FOR
> > SELECT group_id,remote_id,checked
> > FROM messages
> > WHERE group_id = :group_id AND
> > checked = 'N'
> > FOR UPDATE;
> >
> > The problem with this statement is that all the other
> >processes 'hang' while this process has this record locked. For
> >instance, lets say process A executes this SQL statement. Process A
> >gets the record and goes about it's business. Process B comes along
> >and tries to run this same statement, and hangs.
> >
> > The problem is, process A CANNOT update this record before it
> >has completed processing. Once done, process A will update the
record
> >and mark it checked = 'Y'. So, I can't update the record and
complete
> >the transaction right away, and hence process B hangs waiting for
> >process A to complete and release the record lock :-(
> >
> > If I set the NOWAIT option, all the other processes will error
> >out saying that the resource is busy.
> >
> > What I WANT to happen is: process A selects a record (for
> >update), and goes about it's business. Processes B selects a
> >DIFFERENT record (not waiting for process A to complete), and goes
> >about it's business.... Process C, D, E, etc. all do the same.
> >
> > How would I go about doing this?
> >
> > TIA,
> >
> >- Steve
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Jan 18 2000 - 11:27:03 CST