NHibernate: Select MAX value concurrently

Question

Suppose I need to select max value as order number. Thus I'll select MAX(number), assign number to order, and save changes to database. However, how do I prevent others from messing with the number? Will transactions do? Something like:

     ordersRepository.StartTransaction();
     order.Number = ordersRepository.GetMaxNumber() + 1;
     ordersRepository.Commit();

Will the code above "lock" changes so that order numbers are read/write only by one DB client? Given that transactions are plain NHibernate ones, and GetMaxNumber just does SELECT MAX(Number) FROM Orders.

Answer 1

bada bing! http://ayende.com/Blog/archive/2009/04/15/nhibernate-mapping-concurrency.aspx

Answer 2

Using an ITransaction with IsolationLevel.Serializable should do the job. Be careful of table contention, though. If you've got high frequency updates on the table, things could slow down big time. You might want to profile the hit on the db when using GetMaxNumber().

I had to do something similar to generate custom IDs for high concurrency usage. My solution moved the ID generation into the database, and used a separate Counter table to hold the max values.

Using a separate Counter table has a couple of plus points:

• It removes the contention on the Order table
• It's usually faster
• If it's small enough, It can be pinned into memory

I also used a stored proc to return the next available ID:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
  UPDATE [COUNTER] SET Value = Value + 1 WHERE COUNTER_ID = @counterId
COMMIT TRAN

RETURN [NEW_VALUE]

Hope that helps.