Re: full usefullness of CURRENT OF ???

Hi!

> Actually, it is the FOR UPDATE that locks the table.....

No, for update still locks *only the rows* selected with for update clause. All other parts of table are available for both reading AND writing (of course if other transactions don't have locks on some rows). And you can read the whole table, despite any insert, delete, update or for update clauses.

So, don't give false information here.

There's an exception (as usual), if you have set ROW_LOCKING init parameter to INTENT, then the whole table is locked during update clauses. Also, you can use lock table command to lock tables if needed, but this goes beyond scope of this posting...

Tanel.

>
> >How? How can I update by ROWID? I was thinking to update
> >by PRIMARY_KEY...
>
> DECLARE
>
> CURSOR EmpCursor IS
> SELECT rowid, ....
> FROM Emp;
> BEGIN
> FOR EmpRec IN EmpCursor LOOP
> UPDATE EMP
> SET SALARY = SALARY * 1.08
> WHERE rowid = EmpRec.rowid;
> END LOOP;
> END;
>
> Kevin
> -----Original Message-----
> Sent: Friday, July 11, 2003 4:09 PM
> To: Multiple recipients of list ORACLE-L
>
>
> --- 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: Kevin Toepke
> INET: ktoepke_at_rlcarriers.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: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

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).