Re: Oracle Manual Lock and blocking

Mark D Powell wrote:
> ianal Vista wrote:
> > "Mike" <n00spam_at_comcast.net> wrote in news:1149925909.348382.273640
> > @h76g2000cwa.googlegroups.com:
> >
> > > Can someone please confirm that an Oracle manual lock only blocks
> > > other's updates and inserts? That is, readers use mult-versioning and
> > > therefore are never blocked.
> > >
> > > Thanks
> > > Mike Jr
> > >
> > >
> >
> > you are correct
> > writers never block readers
> > and vice versa
>
> ianal, is correct if the OP is referring to Oracle's default normal
> locking scheme when he said "manual locks". But these are automatic,
> implict, locks taken out by Oracle as part of DML processing. The term
> "manual lock" would seem to have to be either an explicit lock taken
> via LOCK TABLE commands or USER locks taken via dbms_lock.

Mark,
 That is correct. Manual locking can be accomplished via a LOCK TABLE or by a SELECT...FOR UPDATE statement. A 3rd way to manual lock a table is via the DBMS_LOCK package.

>
> Mike, it is important to always try to use official terminology when
> posting so that board readers properly understand the question and do
> not waste time posting the right answer to the wrong question.

Thank you for the caution. Manual Lock is a term used by both Tom Kyte (see DBMS_LOCK package) of "AskTom" fame (http://www.dbazine.com/oracle/or-articles/kyte2/) and also by the Oracle Concepts manual.

The reason that I asked is that in the case that a table (T1) that is only read by a transaction (Tx1) that updates a second manually locked table (T2) can be updated by a second transaction (Tx2) that has a manual lock on T1. In this case, Tx1 will have to use multi-versioning to get a read consistent answer from T1. It is good that Oracle does this.

Thank you Mark and ianal for responding.

--Mike Jr

>
> IMHO -- Mark D Powell --
Received on Sat Jun 10 2006 - 17:09:36 CDT