• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

killing an oracle jdbc session and rolling back?

 
Ryan G Johnson
Ranch Hand
Posts: 33
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
We have a background process that runs every few minutes. It opens up several sessions and each performs works. When we take the system down for maintenance we have a korn shell script that kill -15 the java threads.

Problem is that even though autocommit is off insert,update,delete that have been made are being committed when the session shuts down and we would like it to roll back.

Writing code to handle a graceful shutdown and have it roll back would be a significant effort. Is there a jdbc flag that will tell oracle 'if this session dies on you rollback and don't commit unless we specifically say so'.
 
Ryan G Johnson
Ranch Hand
Posts: 33
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
top anyone?
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ryan,
I don't know of a way to do that. Can you lock the Oracle tables themselves during the maintenance window?
 
R. M. Menon
Author
Ranch Hand
Posts: 50
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Ryan Johnson:
We have a background process that runs every few minutes. It opens up several sessions and each performs works. When we take the system down for maintenance we have a korn shell script that kill -15 the java threads.

Problem is that even though autocommit is off insert,update,delete that have been made are being committed when the session shuts down and we would like it to roll back.

Writing code to handle a graceful shutdown and have it roll back would be a significant effort. Is there a jdbc flag that will tell oracle 'if this session dies on you rollback and don't commit unless we specifically say so'.


When you take the system down for maintenance, why don't you wait for the threads to finish the jobs? When you shutdown Oracle normally, it should do that. If you want to "force kill" then you should get an SQLException which you should be catching and doing a rollback in your JDBC client code anyways? Also, do you have "intermittent" commits in your transaction that may be causing this issue?
 
Ryan G Johnson
Ranch Hand
Posts: 33
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
no intermittant commits.

I can have it wait until they are done, but there may be a need to kill immediately.

If I do a kill command from unix, i can't force a sql exception, it kills the thread? I don't get it. This is are background processes. I would have to re-write the framework to message the background processes to exit gently. Not practical. I was hoping for an API.

Oracle's sqlplus utility has an 'autocommit' command, when it is off, if you kill the sqlplus session it does NOT commit. So I hoped there would be something similiar for jdbc.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic