Re: Detecting SELECT FOR UPDATE WAIT ... failures
- From: Nigel Thomas <nigel@xxxxxxxxxxxxxx>
- To: stuart.clowes@xxxxxxxxx, oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 22 Jun 2006 01:38:55 -0700 (PDT)
Stuart
Have you tried adding an AFTER SERVERERROR trigger? I've not tried it but you
should be able to do something like:
CREATE TRIGGER log_errors AFTER SERVERERROR ON DATABASE
BEGIN
IF (IS_SERVERERROR (0054)) THEN
<special processing of lock timeout error>
-- ie query from v$ views
ELSE
<ignore other errors>
END IF;
END;
(syntax cribbed from 9i docs). This has the advantage of being (reasonably)
non-invasive to the vendor app. I guess the main problems would be:
- knowing - in the trigger - which row (or even table) you had failed to lock
- catching the offending blocker in time
The other approach is brute force and trace files: trace 10046 - for all
application sessions (set this up with a logon trigger) with waits and bind
variables, then analyse the output by timeslice. Look for oracle errors, then
look for bind variable values in other sessions at about the same time.
Obviously this can have an impact on the app's performance...
Regards Nigel
----- Original Message ----
From: Stuart Clowes <stuart.clowes@xxxxxxxxx>
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
Other related posts:
