Re: How to avoid this conflict?

I don't understand the criteria for assigning line numbers (ln# ?)? Looks arbitrary.
Jim
"C Chang" <cschang_at_maxinter.net> wrote in message news:3D5737C4.12D3_at_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