Re: Detecting SELECT FOR UPDATE WAIT ... failures
- From: Nigel Thomas <nigel@xxxxxxxxxxxxxx>
- To: stuart.clowes@xxxxxxxxx, oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 21 Jun 2006 11:07:27 -0700 (PDT)
Stuart
The row(s) you want could have been locked by a simple UPDATE of the same table
with no prior SELECT FOR UPDATE. Are there any "long running" transactions
updating this table (by long running I mean > 10 seconds from start to
commit...)?
Another one to watch out for is the old foreign key locking chestnut - varies
by release, but a delete or PK update of a master row can lock the child table.
See Jonathan Lewis's explanation:
//www.freelists.org/archives/oracle-l/06-2005/msg01430.html or browse
around http://www.jlcomp.demon.co.uk/faq/ind_faq.html.
Regards Nigel
----- Original Message ----
From: Stuart Clowes
To: oracle-l@xxxxxxxxxxxxx
Sent: Wednesday, June 21, 2006 4:39:31 PM
Subject: Detecting SELECT FOR UPDATE WAIT ... failures
List,
We have a packaged app. One of our processes is issuing 'SELECT X FROM TABLE_Y
WHERE A=:1 FOR UPDATE WAIT 10' (names changed to protect the vendor....). .
This process is periodically having problems because the 'SELECT FOR UPDATE' is
timing out. This happens a few times a day, at unpredictable times. (Yes, I
know the code should cope gracefully with this......... but it doesn't).
I've been asked - what other process is holding the lock that this process is
failing to get?
The vendor is saying that they can't spot the conflicting DML in their code. A
trace file doesn't seems to help. I can't spot 'SELECT FOR UPDATE' in Logminer
output.
Do you have any thoughts on how I can approach this, aside from querying the
lock views all day?
Stuart
--
//www.freelists.org/webpage/oracle-l
Other related posts:
