Still not sure, but if you need processing based upon an order or to know if
something is doing something else look at the dbms_lock package. I think
that would help. You could have a known lock and the packages or processes
could do their work correctly.
Jim
"C Chang" <cschang_at_maxinter.net> wrote in message
news:3D587550.1D1C_at_maxinter.net...
> 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 - 22:32:47 CDT
