• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Killing a thread waiting for db query execution

 
Joe Nguyen
Ranch Hand
Posts: 161
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hope some ones can shred a light to solve my thread problem. I want to kill a thread waiting for long query execution. I had used a monitor thread to close the preparedStatement of the query thread, but it did not work. Agressively, I tried to stop the query thread, but it also did not work. In this case, the query thread was death, but the program did not terminate.
 
Jim Yingst
Wanderer
Sheriff
Posts: 18671
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Joey - did you try the cancel() method on the Statement? That's probably best. You can also try close() on the Connection object. If you're desparate there's even Thread's stop() method - yes it's deprecated, but there are occasionally cases where none of the alternatives work; this may be one of them. But if you do stop(), assume that any objects or data that were associated with that thread are possibly corrupt one way or another (unless you can confidently determine why they're not) and should be abandoned. If you need the data later, best to create a whole new Connection and Statement, and reload the data fro scratch.
 
Jim Yingst
Wanderer
Sheriff
Posts: 18671
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Joey said in another thread:

Jim,
I did try them all, but they did not work. The method cancel() did nothing because informix jdbc does not support it. The method close() could not execute. It appeared that the query thread locked the prepapredStatement object and prevented the monitor thread from close() the prepraredStatement. Stopping the query thread was interesting. After stopping the query thread, I checked whether the thread was alive. The thread was indeed died, but the query kept running.
 
Jim Yingst
Wanderer
Sheriff
Posts: 18671
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
David Weitzman wrote (about a different problem):
If you choose to use a boolean flag to indicate when it's time to stop, be sure to declare the flag volatile in case you should find your code running in a VM that keeps thread local copies of fields.

Joay wrote:

David,
while(flag){
...
prepraredStatement.executeQuery("Sql");
...
...
}
volatile flag will not work because the operation preparedStatement.executeQuery() consumes much time (sometimes it takes up to 5 min).
 
Joe Nguyen
Ranch Hand
Posts: 161
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Has anyone previously run into this problem? Any suggestion?
 
Jim Yingst
Wanderer
Sheriff
Posts: 18671
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You might see if it's possible to find a different driver for your Informix DB, one that does support cancel(). Especially if you're currently using a JDBC-ODBC bridge or something like that. Buf if you've tried the most full-featured Informix driver(s) you can find and cancel() still doesn't work, you may be stuck with either (a) switching to another DB, or (b) living with the inability to cancel operations.
I should note though that my knowledge of databases is pretty generic, and I know nothing about Informix specifically. Tell you what, people in [url=http://www.coderanch.com/forums/f-3/JDBCJDBC[/url] may know more about this, so I'm going to transfer the topic there. Good luck...
 
Joe Nguyen
Ranch Hand
Posts: 161
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You might see if it's possible to find a different driver for your Informix DB, one that does support cancel(). Especially if you're currently using a JDBC-ODBC bridge or something like that. Buf if you've tried the most full-featured Informix driver(s) you can find and cancel() still doesn't work, you may be stuck with either (a) switching to another DB, or (b) living with the inability to cancel operations.

Jim,
I did not post the question in here because I know informix jdbc does not support the query timeout/ cancel operation. It's impossible to switch to different database (it's out of my control). I just want to kill the query thread. Could you move this question to java (advance) place where jguru can give me some suggestions? Thanks
 
john guthrie
Ranch Hand
Posts: 124
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
as far as i know, if informix doesn't support cancel, you are out of luck as far as jdbc goes. if you do a blocking call and the thing on the other side doesn't support any interrupt method, not much you can do.
having said that, maybe there is some informix-specific way to do this. have you read the docs for their jdbc driver?
 
Joe Nguyen
Ranch Hand
Posts: 161
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
John,
Informix version 7.3, which is over 7 years old, does not support cancel/query timeout. Unable to solve this problem, my application will eventually run out of db connections. I am quite supprised that a java thread is immutable from getting killed.
 
john guthrie
Ranch Hand
Posts: 124
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
it's not a java problem in my opinion. if informix doesn't supply a way to cancel a query in its jdbc driver, informix is the one doing you a dirty turn here.
one last idea. if informix offers a way to cancel a query through another (e.g C) api, you could write some jni code to utilize that.
btw, not that it helps, but postgresql provides nice support for the cancel method. when you call it you get (immediately) a SQLException that states the query was cancelled. that's what informix should have done for you too.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic