How to stop a very long procedure
Hello everybody,
I have a question about a problem that I have.
I have a sql procedure that is running. This procedure is very long, sometime one hour.
But in some case, we must stop it in order to run another procedure more important.
For this emergency, I do two threads : main thread that launch subthread which execute sql procedure. And when we must stop the subthread, the main thread cancel the future task that represent the subthread.
This work well in java only (the thread is well cancelled) but the procedure sql is still running. we saw that in oracle trace session.
And I can't close the jdbc connection. It is locked still the procedure is running.
Do you have solution to stop the procedure sql in java?
Thank you very much for your help and sorry for my poor english.
Best regards,
David.
[873 byte] By [
viddaka] at [2007-11-27 11:41:49]
# 1
> Hello everybody,
>
> I have a question about a problem that I have.
> I have a sql procedure that is running. This
> procedure is very long, sometime one hour.
Sure there's not a problem with the procedure code? Indexes are properly defined? You're not doing table scans? How big a data set, how much calculation?
That's what I'd look at first.
> But in some case, we must stop it in order to run
> another procedure more important.
Does the long running procedure roll back?
> For this emergency, I do two threads : main thread
> that launch subthread which execute sql procedure.
> And when we must stop the subthread, the main thread
> cancel the future task that represent the subthread.
>
> This work well in java only (the thread is well
> cancelled) but the procedure sql is still running. we
> saw that in oracle trace session.
> And I can't close the jdbc connection. It is locked
> still the procedure is running.
>
> Do you have solution to stop the procedure sql in
> java?
> Thank you very much for your help and sorry for my
> poor english.
> Best regards,
> David.
No solution from me, but then I'm not an expert.
I don't think you should be stopping that job from Java. This doesn't feel like the right solution to me.
%
# 2
I don't think you can stop the procedure. Also, as duffymo said, that's not the way to handle the problem.
Manuel Leiria
# 3
I think I found a solution :
For launch the stored procedure :
- have a connection
- get the callableStatement from the connection
- run the procedure
For cancel the procedure, it exist a method cancel().
I call the method cancel from the statement and after I call the close method from connection. But it raise the exception ORA-01013: user requested cancel of current operation.
I search on google and find that is not really an error but an information. Is it right?
# 4
What you're trying to do is a pretty bad idea.
# 5
>
> I call the method cancel from the statement and after
> I call the close method from connection. But it raise
> the exception ORA-01013: user requested cancel of
> current operation.
>
> I search on google and find that is not really an
> error but an information. Is it right?
Yes.
You might note that I have worked with databases in the 300 gigabyte range with hundreds of millions of rows and yet I still managed to produce procs that took less than a second to execute.
So it would probably be a really good idea to look at not only the code for the procs that you are using but also the architecture/design. For starters consider dividing it into tasks.
# 6
> What you're trying to do is a pretty bad idea.
Can you explain me why is it so bad? I'm not very experimented and I'm very interested what in that I do is bad and in order to do better.
Thanks you for your help.
# 7
> > What you're trying to do is a pretty bad idea.
>
> Can you explain me why is it so bad?
I can suggest some reasons that it makes me nervous.
1. Why does it take so long?
2. What are the consequences of cancelling it?
3. Why must it be cancelled for the other one to run?
# 8
> > What you're trying to do is a pretty bad idea.
>
> Can you explain me why is it so bad?
I too would like to know what dcminter meant by that comment. If he meant it in the same context as jschell wher you treating the symptom and not the disease I agree. Better to treat the disease, if at all possible.
But it read to me as well like he was saying something else and I am curious as to what.
# 9
> > > What you're trying to do is a pretty bad idea.
> >
> > Can you explain me why is it so bad?
>
> I too would like to know what dcminter meant by that
> comment. If he meant it in the same context as
> jschell wher you treating the symptom and not the
> disease I agree. Better to treat the disease, if at
> all possible.
That's how I took his meaning.
%
