No Wait for Update of locked row? 
Author Message
 No Wait for Update of locked row?

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



Sun, 08 Aug 1999 03:00:00 GMT
 No Wait for Update of locked row?

Quote:

> 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


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 .



Mon, 09 Aug 1999 03:00:00 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Waited Too Long For Row Cache Enqueue Lock

2. waited too long for row cache enqueue lock

3. Lock Row for Select and Update

4. row locks and updates causing deadlocks

5. locking a row for update

6. locks, stored procedure, update row

7. Lock a SELECTed row for UPDATE?

8. ADO not releasing row level lock after .Update

9. Implicit row locking during an UPDATE

10. ADO not releasing row level lock after .Update

11. locking a row for update

12. Problem locking a row for updating


 
Powered by phpBB® Forum Software