OracleCommand does not respond to cancellation

[madelson]

I am trying to cancel an oracle command which is calling sys.dbms_session.sleep but the cancellation signal doesn't seem to be working. Here's the code:

var conn = new OracleConnection(connectionString);
conn.Open();

var command = conn.CreateCommand();
command.CommandText = @"
    begin
        sys.dbms_session.sleep(5);
    end;";
var sw = Stopwatch.StartNew();

var cts = new CancellationTokenSource(delay: TimeSpan.FromSeconds(1));
cts.Token.Register(() => Console.WriteLine("canceled"));
var task = command.ExecuteNonQueryAsync(cts.Token);
try { await task; }
catch (Exception ex) { Console.WriteLine(ex.Message); } // ORA-01013: user requested cancel of current operation

Console.WriteLine(sw.Elapsed); // ~5s

The command does end up in a canceled error state, but it only transitions to that state AFTER the sleep has completed, which negates the benefit of canceling. I'm running into the same issue trying to cancel calls to DBMS_LOCK.REQUEST.

[alexkeh]

ODP.NET doesn't support async APIs yet. See #144

[madelson]

@alexkeh thanks for following up! Good to know that there isn't real async support yet. However, I find that this same bug reproduces with the sync API using DbCommand.Cancel() (the old API for synchronous cancellation which the docs seem to suggest works):

var conn = new OracleConnection(connectionString);
conn.Open();

var command = conn.CreateCommand();
command.CommandText = @"
    begin
        sys.dbms_session.sleep(5);
    end;";
var sw = Stopwatch.StartNew();

Task.Run(() => {
    Thread.Sleep(TimeSpan.FromSeconds(1));
    command.Cancel();
    Console.WriteLine("canceled");
});

try { command.ExecuteNonQuery();  }
catch (Exception ex) { Console.WriteLine(ex.Message); } // ORA-01013: user requested cancel of current operation

Console.WriteLine(sw.Elapsed); // ~5s

Would you expect this to work?

[alexkeh]

I'm not sure. I've not personally seen Cancel() used with Sleep() before. The typical command cancellation scenario is for a SQL or stored procedure executing SQL.

[madelson]

@alexkeh aside from sleep(), I'm also trying and failing to cancel a long-running request to DMBS_LOCK.REQUEST. Doesn't that seem like something that should work? I'm new to Oracle but FWIW the .NET providers for SqlServer, MySQL, and Postgres can all cancel the equivalent to sleep and lock aquire calls for those databases.

[alexkeh]

@madelson I talked with the ODP.NET dev team. This situation is one they have seen before. ODP.NET sends the cancellation call to the DB server fairly quickly. However, the PL/SQL engine itself seldom checks for these client cancellation messages, which is different from how SQL cancellations behave.

While I some on the client side would consider this a bug, the choice the PL/SQL team made means this behavior is by design. I plan to update the ODP.NET doc so that this is more clearly spelled out.

[madelson]

Thanks @alexkeh . I guess consider this a feature request for the PL/SQL team to implement proper cancellation :-)

[madelson]

For anyone else who comes across this, it seems to be fixed in newer versions of Oracle itself (e. g. as of 19.0.0.0.0 it works as expected).