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?
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 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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the help!
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