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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

765 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