Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    7

    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?

  2. #2
    Join Date
    Jan 2004
    Posts
    370
    Are you saying that when one thread gets blocked, the other thread somehow detects this and issues a cancel() on it?

    As far as I know, there isn't a magic parameter that can be adjusted to set the time that the cancel() takes effect.

    If the thread is trying to perform an update operation, why don't you use

    select ... for update nowait

    That way, if it can't get the locks it needs, it immediately returns an ORA-00054 error
    And you don't need a second thread to cancel the operation.

    Or maybe I've completely misunderstood what you are trying to do.

  3. #3
    Join Date
    Jan 2004
    Posts
    7
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •