Re: No Wait for Update of locked row?

Gene Plagge (gene_sal_at_pacbell.net) wrote:
> Hi,
 

> One of our developers needs to have the functionality of the NOWAIT
> clause in a SELECT statement when he is doing an UPDATE. He needs to
> have the UPDATE fail (with Ora error message) if the row he is
> attempting to update is locked.
 

> Any ideas on how to approach this.
 

> Oracle tech support had no clue...

> Thanks for any help.

> Gene Plagge
> WorldxChange, Inc.
> 619-452-0041 x8466
> gplagge_at_wxl-cts.com

Hi Gene,

       Since an UPDATE Statement does not have a NOWAIT clause you cannot use it,
       However there is a WorkAround if You need to Raise an Oracle or User Error
       upon Failure to Update a Row.

       Use a PL/SQL Block which handles the ORA-55 Exception by Naming it.( PRAGMA .. )
       Inside the Block execute a SELECT statement with a 'FOR UPDATE OF.. NOWAIT' 
       clause before the Update Statement. If another session has locked the Row then
       the ORA-55 Exception (Resource Busy ..) will be raised and You Can handle it 
       as You Want.
       Of Course if the Exception is not raised, You have obtained the Lock and the
       following Update Statement will execute immediately, You can Commit or Rollback
       if you want to release the Lock.


       I've often used this to handle Row Locking.

Good Luck,

Joe . Received on Thu Feb 20 1997 - 00:00:00 CST