Re: RE: full usefullness of CURRENT OF ???

On Friday 11 July 2003 13:19, rgaffuri_at_cox.net wrote:
> So while you are selecting data and before you update, the record could
> change. This means someone else could update it and youc ould update over
> it without knowing it. This is a bad idea in the database world and is
> known as 'disapearing updates'. So you lock the row when you select it. Not
> when the actual update is processed.

No, you don't lock a row when you select it.

There's no need for a read lock of a row in Oracle to maintain consistency. If Oracle can't produce a consistent row, it throws an ORA-1555.

Jared

>
> Updating inside of cursors is generally a bad idea. Its more code and its
> MUCH slower than a straight update statement. Ive seen some publications
> stating that it can before to do this way, but Ive tested every case I can
> think of and not once was it even close.
>
>
> what is the update statement you want to put inside the cursor?
>
> > From: MaryAnn Atkinson <maryann_30_at_yahoo.com>
> > Date: 2003/07/11 Fri PM 04:09:25 EDT
> > To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> > Subject: RE: full usefullness of CURRENT OF ???
> >
> > --- Chris Stephens <ChrisStephens_at_affina.com> wrote:
> > > Also, the CURRENT OF locks the table so that no one can modify (or
> > > even read) it while the transaction is taking place. This guarantees
> > > nothing is changing between retrieving values from the cursor and
> > > updating the table based on those values.
> >
> > OK, fair enough.
> >
> > > The second example has to run the update statement seperately.
> > > CURRENT OF can go directly to the row(s) affected. CURRENT OF
> > > still has to modify each block header in the table to lock which
> > > is a small performance hit.
> >
> > So CURRENT OF has to lock, so its slower, right?
> > Does it have any advantages after all?
> >
> > >To prevent that you could update by rowid
> >
> > How? How can I update by ROWID? I was thinking to update
> > by PRIMARY_KEY...
> >
> > > and avoid the header updates.
> >
> > ... what "header" updates?
> >
> > thx
> > maa
> >
> > __________________________________
> > Do you Yahoo!?
> > SBC Yahoo! DSL - Now only $29.95 per month!
> > http://sbc.yahoo.com
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: MaryAnn Atkinson
> > INET: maryann_30_at_yahoo.com
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting services
> > ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jared Still
  INET: jkstill_at_cybcon.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).