Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Lock timeout on oracle 8i

Re: Lock timeout on oracle 8i

From: Howard J. Rogers <dba_at_hjrdba.com>
Date: Wed, 29 May 2002 09:42:46 +1000
Message-ID: <ad14og$go2$1@lust.ihug.co.nz>

"Yong Huang" <yong321_at_yahoo.com> wrote in message news:b3cb12d6.0205271404.651aae5a_at_posting.google.com...
> By update the parent record, you mean updating the primary key or
> other columns? Updating the primary key never or rarely happens.

Tell that to an Oracle Forms application. You update the telephone number of a department in the DEPT table, and the forms application will tend to want to update every damn field of the record as part of its save.

Updating primary keys therefore happens far more often than you might expect or seem to be allowing for, even when you are not updating them!!

>If
> you update other columns such as the department name in the sample
> scott/tiger schema, there's no locking, whether you delete an employee
> or update the employee record.
>

Unless, as I say, the app decides to save every field of the record, just in case.

The rule with foreign key relationships ought to be, as a result, slap an index on the child table's foreign key column to avoid painful locking issues. Unless you aren't using Oracle Forms, or unless you can otherwise guarantee that no parent key will ever be updated.

Regards
HJR
> Your original code is good as far as handling ORA-54 is concerned; you
> wait some time and try to lock the row again in the hope that the row
> is no longer locked by other sessions.
>
> Yong Huang
>
> rsusanto_at_atlas-sp.com (Rudy Susanto) wrote in message
news:<1a928d0b.0205262331.77ccae3c_at_posting.google.com>...
> > 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.
Received on Tue May 28 2002 - 18:42:46 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US