killing an oracle jdbc session and rolling back?

[Ryan G 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'.

[Ryan G Johnson]

top anyone?

[Jeanne Boyarsky]

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]

 

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]

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.