Link to home
Start Free TrialLog in
Avatar of marathonburke
marathonburke

asked on

Select for update

If I've got a web application that is doing a SELECT FOR UPDATE for one user. When subsequent user comes tries to do a SELECT FOR UPDATE, my application just hangs until the lock is released. I've tried using the "NOWAIT" clause at the end of the statement, but that doesn't help.  Is there a test I can do to figure out if there exists a lock on a record? Is an exception thrown when a lock exists and the application does a SELECT FOR UPDATE?
Avatar of tiendh
tiendh

You can use this SQL to determine which is locked

select l.oracle_username "User Name", o.object_name,
z.status, z.machine, z.program, z.type
FROM v$locked_object l, dba_objects o, v$session z
WHERE l.object_id = o.object_id
and l.session_id = z.sid (+)
ORDER by o.object_name

Hope it help
Using the SQL from tiendh, you can qualify the WHERE clause further by checking for a particular o.object_name.  The SELECT...FOR UPDATE session that is currently waiting will have a status of ACTIVE. The SELECT...FOR UPDATE session that has the object locked will have a status of INACTIVE.

If you use the SELECT...FOR UPDATE NOWAIT, Oracle should throw the error "ORA-00054: resource busy and acquire with NOWAIT specified."
If you do SELECT...FOR UPDATE NOWAIT, then if the record is already locked the following Oracle error is raised:

ORA-00054: resource busy and acquire with NOWAIT specified

You can declare your own exception to catch this:

DECLARE
  e_locked EXCEPTION;
  PRAGMA EXCEPTION_INIT( e_locked, -54 );
BEGIN
...
EXCEPTION
  WHEN e_locked THEN ...
END;

It is up to you what to do then - you can just give an error message, or wait a few seconds then try again.
ASKER CERTIFIED SOLUTION
Avatar of giteshtrivedi
giteshtrivedi

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of marathonburke

ASKER

Thanks for the help!