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

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 ...
Rao_SAsked:
Who is Participating?
 
mrjoltcolaConnect With a Mentor Commented:
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.
0
 
sdstuberConnect With a Mentor Commented:
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
0
 
Rao_SAuthor Commented:
thanks!
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..
0
 
sdstuberCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.