multi-process cooperation and record locking

From: Steve Shoecraft <dontspamme_at_goaway.fu>
Date: Mon, 17 Jan 2000 21:50:42 GMT
Message-ID: <38848c2c.4668819@news.primenet.com>

        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 - 15:50:42 CST