Re: Locking problem?

[Quoted] Hi Robert,

It hangs because the default behaviour is to wait until another user unlocks the row.
[Quoted] You can use SELECT FOR UPDATE NOWAIT to explicitly lock the row prior to issuing the UPDATE statement, and then trap that the row is locked by using an exception.
eg.
FUNCTION your_function IS

---
  CURSOR xxx IS
  SELECT 1   /* (or column_name, column_name) */
    FROM table_name
  WHERE yyy
  FOR UPDATE OF table_name
  NOWAIT;

  row_locked    EXCEPTION;   /* ORA-00054 Resource busy and acquire with
NOWAIT. */
BEGIN
  PRAGMA EXCEPTION_INIT(row_locked, -54);
---
  OPEN xxx;
  FETCH xxx
   INTO your_var;
  CLOSE xxx;
  /* the row remains locked until commit or rollback. */

  UPDATE table_name
   etc.
---
  RETURN 0;

EXCEPTION
  WHEN row_locked
  THEN
     RETURN -1;
END;

Then in your calling form, if -1 returned, display an error. If there are
potentially multiple errors then consider loading the error message into an
OUT variable.

An alternative approach is to lock the row in your Form, and then call the
stored database procedure only if successful.
The FOR UPDATE logic and syntax is the same.

Robert Garcia wrote in message <383ACFB6.D73EDAF8_at_gte.net>...


>I have a Developer 2000, Windows NT, Oracle Server version 7,


>application that I execute update procedures out of a Validation

>Trigger. The problem is that the update procedure hangs - instead of

>executing an On Error Trigger - when one of the records to be updated

>is being edited in another instance of my application. A further

>description is as follows:

>When running two instances of my application, the first instance was

>locked onto (editing) a record that I knew that the second instance was

>going to update. When following the breakpoints of the second instance,

>it got to the update procedure (called out of the validation trigger)

>and the screen froze. I have 'on error' triggers set on the item that

>triggered the update procedure, on the block of this item. The 'on

>error' trigger will display an alert which will require a response from

>the user. None of these alerts was executed.

>

>What is happening is that my update (implicit) query is hanging - On

>Error trigger is not executed - because the record I am trying to

>update is locked by another user. Only when the first instance of my

>application disconnects will the update query run to completion.

>  My gut feeling is that Oracle never expected Update Queries to be

>executed out of validation triggers.   Has anyone been able to do this?