Is there a way to use a generic DbCommand to update asynchronously?
When using a shared DbCommand to perform an update, it will hang indefinitely if the row being updated is locked.
The connection used is based on the Devart Oracle provider, Devart.Data.Oracle.OracleConnection
Setting DbCommand.CommandTimeOut has no effect, the update never expires.
DbCommand does not implement BeginExecuteNonQuery, so there seems to be no way to use DbConnection / DbCommand in an asynchronous way.
I can get around this using Devart OracleCommand and BeginExecuteQuery, but that's the case.
Is there a way to do this in a general way?
Simplified code for 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();
}
a source to share
Unfortunately not, ADO.NET does not have an interface or base class with asynchronous operations (for example, BeginExecuteNonQuery / EndExecuteNonQuery). They are present in only a very few ADO.NET implementations. (SqlClient, Devart Oracle).
However, if the time is not set when the CommandTimeOut is set, in my opinion, a bug in the provider.
a source to share
Can you post the LOCK TABLE with the NOWAIT option? This will cause the control to return immediately with an error if the lock fails. For instance:
LOCK TABLE employees
IN EXCLUSIVE MODE
NOWAIT;
There are several ways to lock a table. Here is the developer guide section on blocking. This page is the SQL Reference for the LOCK TABLE command.
Another option is to use the SELECT .. FOR UPDATE NOWAIT statement to lock the rows that you will be updating. Both options require additional commands for Oracle besides your update statement.
a source to share