Is there a way to use a generic DbCommand to do an Asynchronous update?

Question

When using a generic DbCommand to perform an update, it will hang indefinately if the row being updated is locked.

The underlying connection used is is Devart's Oracle provider, Devart.Data.Oracle.OracleConnection

Setting the DbCommand.CommandTimeOut has no effect at all, the update never times out.

DbCommand does not implement BeginExecuteNonQuery, so there seems to be no way to use DbConnection/DbCommand in an asynchronous manner.

I am able to get around this by using Devart's OracleCommand and BeginExecuteQuery, but it does .

Is there a way to do this in a generic way?

Simplified code for the oracle specific logic:

public bool TestAsyncUpdateRowOracle(string key, OracleConnection con, string sql)
{
    const int timoutIterations=10;
    bool updateOk=false;
    OracleCommand cmd = new OracleCommand(sql, con);
    cmd.Parameters.Add(Util.CreateParameter(dbSrcFactory, DbType.String, 16, "key"));
    cmd.CommandType = CommandType.Text;
    cmd.Parameters[0].Value = key.ToString();

    IAsyncResult result = cmd.BeginExecuteNonQuery();
    int asyncCount = 0;
    while (!result.IsCompleted)
    {
        asyncCount++;
        if (asyncCount > timeoutIterations)
        {
            break;
        }
        System.Threading.Thread.Sleep(10);
    }

    if (result.IsCompleted)
    {
        int rowsAffected = cmd.EndExecuteNonQuery(result);
        Console.WriteLine("Done. Rows affected: " + rowsAffected.ToString());
    }
    else
    {
        try
        {
            cmd.Cancel();
            Console.WriteLine("Update timed out, row is locked");

        }
        catch (Exception e)
        {
            Console.WriteLine(e.ToString());
            Console.WriteLine("Unable to cancel update");
        }
    }
    cmd.Dispose();
}

Answer 1

Can you issue a LOCK TABLE with the NOWAIT option? This will return control to you immediately with an error if the lock fails. For example:

LOCK TABLE employees
   IN EXCLUSIVE MODE 
   NOWAIT;

There are several ways to lock the table. Here is the developer's guide section on locking. This is the SQL Reference page for the LOCK TABLE command.

Another option is to use the SELECT .. FOR UPDATE NOWAIT statement to lock the rows you will be updating. Both options require additional commands to be issued to Oracle besides your update statement.

Answer 2

Sadly, no, there is no interface or base class in ADO.NET that has async operations (e.g. BeginExecuteNonQuery / EndExecuteNonQuery). They're only present in very few ADO.NET provider implementations. (SqlClient, Devart Oracle).

That said, if it doesn't time out when a CommandTimeOut is set, in my opinion that's a bug in the provider.