[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 768
  • Last Modified:

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 ...
  • 2
2 Solutions
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
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.
Rao_SAuthor Commented:
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..
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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now