How to solve problem with ora-00060 on select for update? is there a default wait time?

Posted on 2011-05-12
Last Modified: 2012-05-11
thread 1 is a normal updat but we dont want to commit,
thread 2 ia select for update.. which fails..
thread 3 is a normal update which we commit
should we say select for update wait? what is the default time?
thread1 executes at 14:38.. thread 2 fails at 14:39...
within java ...
Question by:Rao_S
    LVL 73

    Assisted Solution

    deadlocks have nothing to do with time, although "sometimes" people try to avoid them with race conditions just "hoping" one session finishes first before the lock happens

    a dead lock occurs because two sessions are mutually waiting on each other.
    Thus neither will ever move.

    The fix is always to change the logic of the application so mutual waits don't occur
    LVL 40

    Accepted Solution

    Well, you may add NOWAIT to thread 2, and then handle the ORA-00054 correctly. This is acceptable behaviour if it makes sense for your design. SELECT FOR UPDATE .. NOWAIT is reasonable logic when you want to attempt to lock some rows to be updated and ensure you don't get lost updates. You just have to be prepared to retry.

    Author Closing Comment

    the NOWAIT worked correctly and were able to find the issue. the thread 2 was locking a whole set of rows, so changed the code -  select ...for update to select only one row by sequence number..
    LVL 73

    Expert Comment

    glad we could help,

    as stated,  deadlock fix is always a code change

    whether by nowait to simply eliminate one of the waits, or by reducing number of locks held or, as in your case both.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
    Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
    This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
    This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

    745 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now