Re: How to avoid this conflict?

From: C Chang <cschang_at_maxinter.net>
Date: Mon, 12 Aug 2002 00:21:24 -0400
Message-ID: <3D5737C4.12D3@maxinter.net>

Jim Kennedy wrote:
>
> use sequences. It will scale much better and allows you to have an
> increasing ID.
> Jim
> "C Chang" <cschang_at_maxinter.net> wrote in message
> news:3D5720E5.4BE0_at_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

Jim:

   Thank for your repond. I think you misunderstood my question. ( or maybe I did not get your point). I have used the sequence in second process. ( I simplified the procedure) However, I tried to avoid the result of the two simutaneous processes creating the records of mytable like this:

	id Ln# value status
	1   1   A      Y
	1   2   B      Y
	1   3   C      X
I want the answer of the two processes as
       
       id Ln# value status
	1   1   A      Y
	1   2   B      Y
	2   1   C      X

Thanks again.

C Chang Received on Sun Aug 11 2002 - 23:21:24 CDT