Hello,
I have a very simple and frequently occurring problem but I don't seem to find a solution. Or the canned solutions don't seem to work.
I have a simple operation table. The records in this tables have one column which states whether the operation has "not started" or is "in progress" or has been "completed".
Concurrent processes/machines, look for any available ("not started") operation and update the record so that it shows "in progress". This select and update should be atomic. And it also need to lock the row for the duration between the select and the update.
I have been reading that ReadCommitted isolation level with LockMode.Upgrade on the fetch query should do the trick. But when I regression test it with 5 threads against a MySQL server, I see more than 20% incorrect behavior where a record has been accessed by more than one thread (I see two and sometimes three threads accessing the same record). It's as if there is no locking.
Here is a code snippet:
Code:
ISession session = factory.OpenSession(); ;
ITransaction transaction = session.BeginTransaction();
try
{
// try to get one item
q = session.CreateQuery("from operation in class OperationObject " +
"where operation.StatusValue=:init");
q.SetParameter("init", StatusEnum.init, new GenericEnumMapper<StatusEnum>());
q.SetMaxResults(1);
q.SetLockMode("operation", LockMode.Upgrade);
OperationObject operation = q.UniqueResult<OperationObject>();
if (operation == null)
{
log.Debug("No operation available");
return (null);
}
operation.StartDate = START_DATE;
operation.StatusValue = StatusEnum.progress;
operation.Server = SERVER;
session.Update(operation);
transaction.Commit();
return (operation);
}
catch (Exception e)
{
transaction.Rollback();
throw e; // or display error message
}
finally
{
session.Close();
}
I'd appreciate if someone could shed light on the problem and point me to my mistake.
Ramin
Login
FAQ
Search