Re: multi-process cooperation and record locking

A copy of this was sent to dontspamme_at_goaway.fu (Steve Shoecraft) (if that email address didn't require changing) On Mon, 17 Jan 2000 21:50:42 GMT, you 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?
>

Have you investigated using the Advanced Queues (built exactly for this type of operation) instead of using a table?

Given the way databases work -- this is a sticky problem to solve in general. AQ (messaging software in the database) lets multiple consumers feed off of a single queue in a non-blocking fashion (and multiple producers feed the queue in a non-blocking fashion). It makes it so that each message can be processed at least once and at most once. Instead of people inserting into a table -- they will "enqueue" a message. Your 8 processes will "dequeue" a message, process it and then commit.

> TIA,
>
>- Steve

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Jan 18 2000 - 08:14:04 CST