1

Follow up of this other question.

I'm trying to implement pessimistic locking for a concurrency issue as I described in the question above (please, feel free to add to that one). But it's not working for me.

I do a very simple test: I have two seperate sites running that both increase a counter 500 times. I run them simultaneously. In the end, I expect that a certain column in my table has, you guess it, a value of 1000.

Here is the code. It's no production code of course, but test code or not, it should still work, right?

for (int i = 0; i < 500; i++)
{
  var tx = this.userRepo.Session.BeginTransaction();
  var user = this.userRepo.GetById(42);
  user.Counter++;
  userRepo.Save(user);
  tx.Commit();
}

The GetById method uses LockMode.Upgrade:

public T GetById(int id)
{
  T obj = Session.Get<T>(id, LockMode.Upgrade);
  return obj;
}

Now, using NHProfiler I see the following SQL statement:

SELECT Id FROM 'User' WHERE Id = 42 for update

but the result is a value of around 530, so that's about half of the updates lost due to concurrency. What am I doing wrong? I disabled second level cache in this test. Am I using the wrong lock mode? Should I specify an isoliation level? Anything else? Thanks in advance.

EDIT: FluentNhibernate config:

Fluently.Configure()
.Database(MySQLConfiguration.Standard.ConnectionString(connectionstring))
.Mappings(m => assemblyTypes.Select(t => t.Assembly).ToList().ForEach(a => m.FluentMappings.AddFromAssembly(a)))
.ExposeConfiguration(c => c.Properties.Add("hbm2ddl.keywords", "none"));
Community
  • 1
  • 1
Razzie
  • 30,834
  • 11
  • 63
  • 78

1 Answers1

0

For the LockMode.Upgrade to work, all transactions have to be enclosed in a transaction because what LockMode.Upgrade does is lock it into the current transaction.

Your problem will most likely be due to the statements not being enclosed in a transaction.

Optimistic locking does not apply to a single statement, but to multiple transactions that are separated from each other. An example:

  1. Begin a transaction;

  2. Get record by Id = 42;

  3. End the transaction.

Then, outside of the transaction, increase Counter.

After that:

  1. Begin a transaction;

  2. Get record by Id = 42;

  3. Check whether counter has been unchanged from the value received in the first transaction;

    a. If it hasn't changed, update the counter with the increased value;

    b. If it has changed, handle the changed value.

  4. End the transaction.

Optimistic locking means that you "hope" the Counter hasn't changed between the two transactions and handle the case where it has changed. With pessimistic locking, you ensure that all changes are done within a single transaction with all required records locked.

B.t.w.: the checking mechanism (whether Counter has changed in the mean time) can be automatically handled by NHibernate.

Pieter van Ginkel
  • 29,160
  • 8
  • 71
  • 111
  • Look at my code - I use BeginTransaction() and Commit() in the end. That's what you mean, right? – Razzie Nov 23 '10 at 08:43
  • Apologies, you're right. What's your serialization mode (the connection.isolation SessionFactory setting or the first parameter to `BeginTransaction`). See http://msdn.microsoft.com/en-us/library/system.data.isolationlevel.aspx for more information on their meaning. Try to play with these and see whether that makes a difference. See what happens with `Serializable` because that should guarantee that this works as expected. – Pieter van Ginkel Nov 23 '10 at 11:10
  • hmmm, maybe using the for-loop was also causing problems. It seems with no for-loop but using Thread.Sleep to cause concurrency, I do get good results using IsolationLevel.RepeatableRead and LockMode.Upgrade. Still, if you can clarify how these different mechanisms and those difference types work together, it would be much appreciated. – Razzie Nov 23 '10 at 13:53
  • I will have to refer you to documentation on these isolation levels because it's too much to handle this in a comment :). Note however that the MSDN link itself also has some information, but http://en.wikipedia.org/wiki/Isolation_(database_systems) will probably provide more information. – Pieter van Ginkel Nov 23 '10 at 15:31