Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Select for update

Posted on 2003-02-24
Medium Priority
Last Modified: 2010-05-18
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?
Question by:marathonburke

Expert Comment

ID: 8013770
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

Expert Comment

ID: 8016839
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."
LVL 15

Expert Comment

ID: 8016850
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:

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

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

Accepted Solution

giteshtrivedi earned 600 total points
ID: 8018141
Dear friend,

If your every statment is contain 'SELECT FOR UPDATE NOWAIT' then what happenning Once resource is locked then other statment for same object and row will get that ora -00054 error message.You can trap that error from your PL/SQL block.

From Oracle9i there is new consept is begin it 'SELECT FOR UPDATE WAIT <SEC>' in sec you can give appropriate sec for waiting.


Gitesh Trivedi.

Author Comment

ID: 8022501
Thanks for the help!

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to recover a database from a user managed backup
Suggested Courses

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question