OracleCommand timeout

Question

ODP.NET documentation for OracleCommand.CommandTimeout says

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.

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 OracleConnection object for both the command objects - cmd1 and cmd2, and cmd1 already timed out(as expected). But, per the documentation, cmd2 also should not run. But it still runs without any exception and updates the other row properly.

Answer 1

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

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
    }
}