oracle Oracle命令超时

OracleCommand timeout

提问by Praveen

ODP.NET documentation for OracleCommand.CommandTimeoutsays

OracleCommand.CommandTimeout 的ODP.NET文档说

Default is 0 seconds, which enforces no time limit.

When the specified timeout value expires before a command execution finishes, the command attempts to cancel. If cancellation is successful, an exception is thrown with the message of ORA-01013: user requested cancel of current operation. If the command executed in time without any errors, no exceptions are thrown.

In a situation where multiple OracleCommand objects use the same connection, the timeout expiration on one of the OracleCommand objects may terminate any of the executions on the single connection. To make the timeout expiration of a OracleCommand cancel only its own command execution, simply use one OracleCommand for each connection if that OracleCommand sets the CommandTimeout property to a value greater than 0.

默认值为 0 秒，不强制执行时间限制。

当指定的超时值在命令执行完成之前到期时，该命令将尝试取消。如果取消成功，则抛出异常，消息为 ORA-01013：用户请求取消当前操作。如果命令及时执行且没有任何错误，则不会抛出异常。

在多个 OracleCommand 对象使用同一连接的情况下，OracleCommand 对象之一的超时到期可能会终止单个连接上的任何执行。要使 OracleCommand 的超时到期仅取消其自己的命令执行，只需为每个连接使用一个 OracleCommand，如果该 OracleCommand 将 CommandTimeout 属性设置为大于 0 的值。

But a code like this works:

但是这样的代码有效：

static void Main(string[] args)
{
    OracleConnection conn = null;
    try
    {
        string connString =
            "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=myOracleHost)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=myServiceName)));User Id=system;Password=admin;Pooling = False;";
        string cmdString1 = "UPDATE employee SET empname = 'temp1' where id = 1";
        string cmdString2 = "Update employee set empname = 'temp2' where id = 2";
        conn = new OracleConnection(connString);
        var cmd1 = new OracleCommand(cmdString1, conn);
        cmd1.CommandTimeout = 30;
        var cmd2 = new OracleCommand(cmdString2, conn);
        cmd2.CommandTimeout = 30;
        conn.Open();
        try
        {
            //Locked the row with ID 1 with an uncommitted update operation outside this code
            cmd1.ExecuteNonQuery();
        }
        catch (Exception exception)
        {
            //Exception ORA-01013 Thrown as expected
        }
        try
        {
            //As per the documentation, this should not also work since this command object also uses the same connection as above and it timed out in the query above
            cmd2.ExecuteNonQuery();
            //But this still works fine. 
        }
        catch (Exception)
        {
            //no exception
        }
    }
    finally
    {
        conn.Close();
    }
}

I am using the same OracleConnectionobject for both the command objects - cmd1and cmd2, and cmd1already timed out(as expected). But, per the documentation, cmd2also should not run. But it still runs without any exception and updates the other row properly.

我OracleConnection对命令对象 -cmd1和使用相同的对象cmd2，并且cmd1已经超时（如预期）。但是，根据文档，cmd2也不应该运行。但它仍然毫无例外地运行并正确更新另一行。

回答by Vadim K.

You do not have multiple commands running on the connection, you have two commands running sequentially, one after the other. When the first command times out, there is no other command pending on the connection. Your code does not submit the second command for execution until after the first command has either succeeded or has thrown an exception.

您没有在连接上运行多个命令，您有两个命令依次运行，一个接一个。当第一个命令超时时，连接上没有其他命令挂起。在第一个命令成功或抛出异常之前，您的代码不会提交第二个命令以供执行。

The last paragraph from the documentation you quoted should read: In a situation where multiple OracleCommand objects use the same connection simultaneously, ...

您引用的文档中的最后一段应阅读：在多个 OracleCommand 对象同时使用相同连接的情况下，...

static void Main(string[] args)
{
    using (var conn = new OracleConnection("Pooling=False;...")) // why?
    using (var cmd1 = conn.CreateCommand())
    using (var cmd2 = conn.CreateCommand())
    {
        cmd1.CommandText = "UPDATE employee SET empname = 'temp1' WHERE id = 1";
        cmd2.CommandText = "UPDATE employee SET empname = 'temp2' WHERE id = 2";
        cmd1.CommandTimeout = 30;
        cmd2.CommandTimeout = 30;

        conn.Open();

        // there are no commands on conn yet

        try { cmd1.ExecuteNonQuery(); } // cmd1 is the only command on conn
        catch (OracleException) { } // if timeout, no other command affected

        // cmd1 is no longer on conn

        try { cmd2.ExecuteNonQuery(); } // cmd2 is the only command on conn
        catch (OracleException) { } // if timeout, no other command affected

        // cmd2 is no longer on conn
    }
}