I have a report window which shows the results returned from a potentially long running oracle stored procedure. My problem is when the user closes the window the connection to oracle remains open and the potentially long running report doesn't get cancelled.
The only way to close the open connection is for either the DBAs to kill them manually or for the user to exit the entire application.
I've tried calling Close
on the connection from a different thread, but this seems to continuously block. I also tried rolling back a transaction, but this exhibits the same problem.
I'm worried that the only solution will be to run the query in a different process (or maybe app domain?).
It's likely that I'm missing something obvious, any help would be greatly appreciated.
PLEASE READ
This question is not about wrapping my connection in a using
statement. It is about how to force an oracle connection that is executing a query to close.
Example:
Call close on the connection object
public void Go()
{
OracleConnection connection;
var queryThread = new Thread(
() =>
{
using (connection = OpenOracleConnection())
{
// execute stored proc that takes 45 mins
// raise an event with the data set we load
}
});
Thread.Sleep(3000); // give it time to be useless
var closeThread = new Thread(
() =>
{
connection.Close();
});
closeThread.Start();
}
The problem is that this doesn't close the connection, instead the call to connection.Close() blocks waiting for the procedure to execute.
Hm, I can't see anything in the API to abort / cancel an ongoing query . Technically it should be possible, with a second session with full privileges, to identify the session you want to abort and issue the kill session command on that session. I would expect your original session to bail out with some kind of exception, but I've never tried it out.
Here it is explained how to kill a session.
Here it is answered how to get the session id. You could find that one out before starting the long running query, then it should be pretty easy to kill exactly that session from a second connection.
Let us know if it works ;)
To see what/who is blocking who:
select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With