Unanswered: Cancelling a statement (if it's blocked)
In a Java environment, I have two threads. One performs an update on a table in Oracle (but it will be forced to wait because I purposely updated that table in SQL Plus but never committed, so that it will be blocked), then the other thread gets a reference to the first thread's statement and calls cancel() on it. This should force the blocked thread to stop waiting, so I can use that Connection for other tasks.
This works but, it takes a long time! In one of my Oracle servers(9i) it consistently takes 5 minutes. In another sevrer (8i) it consistantly takes 1 minute. There must be some setting to adjust this time, so that I can call cencel and the connection will be free almost immediately, instead of waiting for so long. Any idea where this setitng is or what its called?
there must be some wayto adjust the time a cancel takes place, because a friend working at another company using their ORacle DB can do a cancel and get a response in a split second, while my ORacle DB makes me wait for an unusualyl consistent time of 5 minutes. We're using the same code, same drivers, same DB versions, so naturually I must conlude there is some setting that can govern this.
Also, running a similar test on SQL server gives me a response in a split second, when I call cancel.
I'm absolutely sure there is some setting to adjust this time, someone's gotta know