1

When I cancel my Task using CancellationToken method, It usually get's cancelled around min. 30 seconds. Time depends on how heavy query in Oracle is, it can take even to 1 minute.

Task is called on different thread, using Task.Run (doing some UI loading animations while fetching data) .

If user cancels Task (closes window) while running and starts some new Oracle Task in app, things get's messed up - suddenly a lot of opened sessions appear in Oracle. I want avoid that by speeding up cancellation signal.

Here is my code:

    //global static variable, defined when window opens
    CancellationTokenSource cts= new  CancellationTokenSource(); 
    
    //CancellationToken get's cancelled when user leaves window
    private void OnCloseWindow(object target, ExecutedRoutedEventArgs e)
    {
         cts.Cancel(); 
    }
    
    public async Task<List<EmployeeModel>> Get_Employees(CancellationToken cts)
    {
      var data = new List<EmployeeModel>();
        
      try
      {
        using (OracleConnection con = new OracleConnection(conn_string))
        {
           con.OpenAsync(cts);
        
           OracleCommand cmd = new OacleCommand("Myschema.Procedure_1", con)
           {
             CommandType = CommandType.StoredProcedure
           };
           cmd.Parameters.Add("name_in", OracleDbType.Varchar2).Value = "John";
           cmd.Parameters.Add("surname_in", OracleDbType.Varchar2).Value = "Black";
                       
           using (cts.Register(() => cmd.Cancel()))
           {            
             using (OracleDataReader rdr = cmd.ExecuteReaderAsync(cts))
             {
               while (await rdr.ReadAsync(cts))
               {  
                 data.Add(new EmployeeModel()
                 {
                   NAME = rdr.IsDBNull(0) ? null : rdr.GetString(0),
                   SURNAME = rdr.IsDBNull(1) ? null : rdr.GetString(1)
                 });
               }
             }
           }
         }
      }
      return data;
      }
      catch (OperationCanceledException)
      {
        return null;
      }
      catch (Exception)
      {
        return null;
      }
   }

I'm using OracleManagedDataAccess.dll, version 4.122.19.1. My workaround is to use setting on connection string for Max pool size=3. That - at least during testing - doesn't produce more than 3 sessions at once in Oracle, but I'm not sure if this is the right way.

Is there anything else I can do to fasten cancellation to Oracle? Some kind of mechanism that I'm not aware of?

Theodor Zoulias
  • 34,835
  • 7
  • 69
  • 104
Lucy82
  • 654
  • 2
  • 12
  • 32
  • 1
    The problem is - Oracle not implemented async API properly. In fact it is sync, so this why you see deadlocks/timeouts where it shouldn't happen (all those fancy Async methods accepting cancellationtoken). To mitigate problem - check your cancellation token yourself and throw up at each cycle. Also, you can close datareader in registered action. It will blow up with a bunch of errors, but I see no way around Oracle PM stupidity. https://exceptionnotfound.net/why-does-odp-net-have-async-methods-if-theyre-not-asynchronous/ – eocron Dec 20 '20 at 13:00
  • 3
    Also, on stackoverflow (https://stackoverflow.com/questions/29016698/can-the-oracle-managed-driver-use-async-wait-properly). Apparently they just don't care about async/performance at all. – eocron Dec 20 '20 at 13:08
  • @eocron, thanks for answer. So, this means that I can't achieve proper cancellation neither with unmanaged driver ? – Lucy82 Dec 20 '20 at 13:13
  • Im not sure, you can try, but I doubt it will be any better with all those marshaling clutter. It probably better to migrate to other database, which actually support users instead of abandoning them - ms sql, mysql, etc. – eocron Dec 20 '20 at 14:08
  • @eocron, you won't believe how I agree with you, this is only one of maaaany problems I have with Oracle. But unfortunally It's not up to me, and no one listens to my problems in company. All bosses say that Oracle is the only serious DB in the world :) – Lucy82 Dec 20 '20 at 14:32
  • 1
    Among all developer problems is doing tough decisions AND showing your team pros/cons of your decisions instead of vague "it will be better". So, here is your part of the fault too. You either solve problems or explain why current architecture not allow you to solve it in reasonable time. You can came up with Thread.Abort for sql connection - but how you will mitigate risks of completely broken app environment? I'd say "you either change database, pay Oracle big money to fix this or just migrate to other DB", written down all pros/cons and let them to think about it. – eocron Dec 20 '20 at 15:10

0 Answers0