Re: How to avoid this conflict?

Jim Kennedy wrote:
>
> 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
> > Thanks again.
> >
> > C Chang

Jim:

   I apologize first. Maybe I should be clear more about this. Under normal condition, if only second insert process run, then new record becomes as 1 3 C X, then next becomes 1 4 F X and so on. However, if the first process run before the second one, then as soon as the status of group id = 1 trun into 'Y', then any other new record should start its own id and Ln# starts with 1 and so on. But sometimes, one user sees two records of id=1 and run the first process, while second user insert a new record. As result it causes the group id=1 having an inconsistant state. And I tried to come out a scheme to avoid this? Thanks your input.

C Chang Received on Mon Aug 12 2002 - 21:56:16 CDT