Re: Lock timeout on oracle 8i

Rudy Susanto wrote:

> Hi,
>
> Thanks for your replies.
>
> I consider to buy Tom Kyte's book and i will read it through the rest
> of the book. Thanks for your suggestions.
>
> Since march 2002, we supports other database beside sqlserver, so i
> have to learn oracle everyday and now i know that i have to adapt to
> oracle environment.
>
> The code works as i aspected, before i posted to this news group, i
> have tested first.
>
> Actually we already have some applications with sqlserver database.
> And my question is what is the right way to us if we want our
> applications can does the scenario like this:
> I have 2 tables, parent and child table, if a user update or delete
> child record, another user will never allow to update the parent
> record that have relation with it. So before a user can update or
> delete child record, the parent record must be locked first.
>
> I know this mechanism is not good, but i can't change the existing
> applications as i want at this time. In my opinion i rather consider
> to redesign the database before migration, after redesigning the
> database we do not need explicit locking anymore but i have to discuss
> this problem with the related staffs.
>
> Regards,
>
> Rudy Susanto
>
> yong321_at_yahoo.com (Yong Huang) wrote in message news:<b3cb12d6.0205242003.13cc4bc8_at_posting.google.com>...
> > Regardless what others day, I think you have a legitimate requirement
> > and the code looks OK (I didn't try). What's your question then? Did
> > you try? Does it work?
> >
> > Yong Huang
> >
> > rsusanto_at_atlas-sp.com (Rudy Susanto) wrote in message news:<1a928d0b.0205232010.45b85f49_at_posting.google.com>...
> > > declare
> > > i number(3,0);
> > > v_ename varchar2(10);
> > >
> > > busy exception;
> > > pragma exception_init(busy,-54);
> > >
> > > begin
> > > i := 1;
> > > loop
> > > begin
> > > select ename into v_ename
> > > from emp
> > > where empno=7900 for update nowait;
> > > dbms_output.put_line('ename: ' || v_ename);
> > >
> > > exception
> > > when busy then
> > > if i < 3 then
> > > dbms_lock.sleep(0.5);
> > > else
> > > dbms_output.put_line('Locking record(s) failed ...');
> > > exit;
> > > end if;
> > > end;
> > >
> > > i := i + 1;
> > > end loop;
> > > end;

I understand what you are trying to do with the parent-child update/delete but why? Assuming you have design intelligent enough that users can't update primary keys ... why would anyone care if a parent record is updated while a child record is updated or deleted? I can't see the point.

Daniel Morgan Received on Mon May 27 2002 - 10:49:09 CDT