ODP.NET托管驱动程序在命令超时的情况下不返回contol

Question

我使用的是ODP.NET托管驱动程序12.1.0.2.1（ODAC 12c Release 3），发现了严重的错误。如果在命令执行期间超时，则会导致线程和连接池中的连接丢失。

此测试总是失败：

[TestMethod]
[ExpectedException(typeof (Oracle.ManagedDataAccess.Client.OracleException))]
[Timeout(70000)]
public void OracleCommandTimeoutTest()
{
    const string connectionString =
        "user id=user;password=pass;data source=192.168.1.1:1521db";
    using (var connection = new Oracle.ManagedDataAccess.Client.OracleConnection(connectionString))
    using (var command = connection.CreateCommand())
    {
        // we need for 60 seconds timeout
        command.CommandTimeout = 60; //default value is 0 (unlimited)
        //here can be any query that takes longer time than specified in CommandTimeout
        command.CommandText = "execute DBMS_LOCK.sleep(70)";
        connection.Open();
        //this statement will never return control
        command.ExecuteNonQuery();
        //BUG:connection and command never be disposed and test will be fail after timeout
    }
}

让我们看看驱动程序内部。查找功能：

internal void ReceiveExecuteResponse

函数处理异常（ORA 03111）并在此处调用Reset（）：

catch (NetworkException ex)
{
    if (ex.ErrorCode != 3111)
        throw;
    else
        this.m_marshallingEngine.m_oracleCommunication.Reset();
}

在将URG数据包发送到Oracle数据库并读取答案后，函数进入从未结束的循环：

while (!flag1)
    {

函数尝试读取响应流，直到卸载AppDomain为止。在修复此错误之前，我们无法使用托管驱动程序。未管理的x64驱动程序成功通过此测试。

Answer 1

很明显，OP意外使用了MS OracleClient。它可以与ODP.NET.配合使用

工作示例：

public static void OracleCommandTimeoutTest()
{
  System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
  try
  {
    const string connectionString = @"user id=scott;password=tiger;data source=inst1";
    using (var connection = new OracleConnection(connectionString))
    using (var command = connection.CreateCommand())
    {
      // we need for 60 seconds timeout
      command.CommandTimeout = 20; //default value is 0 (unlimited)
      //here can be any query that takes longer time than specified in CommandTimeout
      command.CommandText = "BEGIN DBMS_LOCK.sleep(70); END;";
      connection.Open();
      //this statement will never return control
      sw.Start();
      command.ExecuteNonQuery();
      sw.Stop();
      Console.WriteLine("ExecuteNonQuery Completed (Didn't timeout): " + sw.Elapsed + " ms");
      //BUG:connection and command never be disposed and test will be fail after timeout
    }
  }
  catch (Exception ex)
  {
    sw.Stop();
    Console.WriteLine();
    Console.WriteLine("Timed-Out after: " + sw.Elapsed + " ms");
    Console.WriteLine(ex.Message);
  }
}

上面的测试用例给出了以下结果：

超时时间：00:00:21.0581601毫秒ORA-0013：用户请求取消当前操作

Answer 2

您应该将ODP.NET客户端更新到4.121.1.0或更高版本。看起来CommandTimeout在您的ODP.NET版本中不起作用。我刚刚在4.121.1.0进行了克里斯蒂安的测试，结果很有效。我尝试了所有的Execute*操作，CommandTimeout适用于所有操作。