Re: Detecting SELECT FOR UPDATE WAIT ... failures

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

--
http://www.freelists.org/webpage/oracle-l