Re: Detecting SELECT FOR UPDATE WAIT ... failures

  • From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
  • To: stuart.clowes@xxxxxxxxx
  • Date: Wed, 21 Jun 2006 19:26:12 +0200

Stuart Clowes wrote:

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
Stuart,

If it times out then another transaction is holding another conflicting lock. It is most likely to result from another DDL operation against the same table. Can't you use AUDIT and check DDL operations by access against this table? This will induce some overhead but if it happens a few times a day you may well be able to relate your failing SELECT FOR UPDATE to a particular operation.
This is of course under the wildly optimistic assumption that the data collected by AUDIT is enough for you to identify the culprit. Short of that, you still have the trigger option ...

HTH

Stéphane Faroult
--
//www.freelists.org/webpage/oracle-l


Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 06-2006