I've been running a series of tests to try and stop a connection from waiting on a locked resource, but nothing is working. I have tried both cancel() and setQueryTimeout(int) using the oracle.jdbc.driver.OracleDriver class of classes12.zip, in a Java environment. I've tried this on many versions of Oracle database, including 8.0, 8i and 9i.
What I do is I lock a row with an update statement in SQL PLus then I run my Java program to try updating the same row, which either tries spawning a new thread first to call cancel() in a few seconds, or setting the query timeout for a few seconds. Neither method causes the connection to stop waiting for the lock to be released. I will have to commit the update in SQL Plus before the program ever continues.
A colleague of mine, using the same exact code, has had success with this. I know he is using an 8i database, which I have not had success with. Is there anything else I am missing which could be different?
well, I accidentally left one of my tests running after the cancel() call and it actually did succeed after a whopping 5 minutes! Doing it many more times, I find a consistent delay of 5 minutes before the cancel() actually gets processed by the Oracle server, and since my colleague has his cancel()s complete almost instantaneously, I am led to believe there must be some Oracle setting to adjust this time. Perhaps through an .ORA file I can change the polling time of whatever is responsible for processing cancel()s. Please help