Re: multi-process cooperation and record locking

        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
Received on Mon Jan 17 2000 - 16:13:19 CST