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
Received on Sun Aug 11 2002 - 21:54:11 CDT
