How to avoid this conflict?

From: C Chang <cschang_at_maxinter.net>
Date: Sun, 11 Aug 2002 22:43:49 -0400
Message-ID: <3D5720E5.4BE0@maxinter.net>

Assuming my record in mytable as:

	id	Ln#	value	status
	1	1	A	X
	1	2	B	X

When one procedure is updating the status that uses a trigger:
   	update mytable
   	set status ='Y'
	where id = 1
	and status ='X';

in the meantime, the other procedure is inserting a new record using Procedure A

	i_id     NUMBER;
   	i_ln#    NUMBER;
     CURSOR c_my IS
      	select MAX(id), MAX(Ln#)
	from mytable
	where status ='X';
     BEGIN
	
	open c_my;
	fetch c_my into i_id, i_ln#;
	IF c_my%FOUND THEN
	   insert into mytable 
	   values (i_id, i_ln#+1, 'C', 'X');
	ELSE
	   insert into mytable 
	   values (i_id+1, 1, 'C', 'X');
	END IF;
        close c_my;

     END;

Because during the updating of first procedure, I do not want the second procedure to select the same id
and insert a new record under the same id, making the status of id = 1 inconsistant. I know when updating the record in Oracle, those records will be locked for other processes. Does that mean that Oracle is generating a lock status for the record? How can this lock status be shared with other processes globally? How is the lock associated with the specific records? Can anyone share an idea to solve this conflict? Thanks.

C Chang Received on Sun Aug 11 2002 - 21:43:49 CDT