Force closing an oracle connection in C#

8
votes

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:

  • Start a thread running a query
  • Stash the connection object somewhere

  • 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.

3
c#oracle
Which ADO.NET Oracle provider are you using ? (The Microsoft provided one doesn't support asynchronous processing, so if it's that one, you might need to write code to connect in another connection and kill the offending process). – driis
It's the Oracle.DataAccess provider that comes with 11.2.0. – jonnii
Not a well asked question. Obviously you'll have to abort the query first. You won't get an answer until you explain how you set this train wreck in motion, "report window" is meaningless. Post code. – Hans Passant
Thanks for the helpful feedback. While posting my code would be useful, it would blind anyone who looked at it. That I'm running my oracle procedure from a different thread is incidental, but I'll try to demonstrate my code. – jonnii
@Hans Passant, obviously I'll have to abort the query. How would I obviously abort the query? – jonnii

3 Answers

3
votes

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 ;)

1
votes

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;
0
votes

As with any provider in .NET you can call Dispose

using(var conn = /* your connection */) {
    // do your stuff

    conn.Close();
} // this will automatically call .Dispose()

So that is all you need to do.