Re: Detecting SELECT FOR UPDATE WAIT ... failures

From: Nigel Thomas <nigel_at_preferisco.com>
Date: Thu, 22 Jun 2006 01:38:55 -0700 (PDT)
Message-ID: <20060622083855.61101.qmail@web54708.mail.yahoo.com>

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_at_gmail.com> To: oracle-l_at_freelists.org 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            

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

Received on Thu Jun 22 2006 - 03:38:55 CDT