"Next number" scenarios using Entity Framework

Question

I have been playing with Entity Framework and so far I like using it a lot. But everything I've done so far assumes optimistic locking which works well for me in most cases. However, I have the following scenario:

1. A SQL Server table with only one row that holds application-wide data
2. The row contains a column named "NextAvailableNumber"
3. The Application needs to read the number, increment it by 1, and update it

The above has to guarantee that any competing process has to wait to get a number until the first transaction has completed. Previously we have done this using table locks (since there's only one row), but I would like to know how this should be done using LINQ to Entities?

Thanks,

Jim K.

Answer 1

I think you need to implement this in stored procedure because and use explicit row locking (or table locking as you mentioned). Then you will call that procedure from EF. I don't think that this can be handled from application code unless you are using serializable transaction every time you work with your special table. It can have huge negative impact on application's performance.

We are actually doing something similar but our table contains plenty of rows for different sequences so I'm using stored procedure with row lock and update lock. We first wanted to call this procedure in insert method of our repository but after that I moved it into database and I call my procedure from after insert trigger. The reason for this was to deffer row locking to the time of real insertion to database not to the time of marking the entity for insert in EF context (insert itself and procedure call must be in the same transaction in our case). I modified my EF model so that related property has StoreGeneratedPattern set to computed. After each insert EF will requery DB to get assigned sequence number. The only drawback is that EF will also requery DB after each update of these entities but in our case it already do this because of timestamp. This is still in phase of testing and evaluating so I can still change my mind and reimplement it.

Answer 2

Since Entity framework uses optimistic locking, it will throw an exception when the next number is changed by a second process. The most primitive way to counter this situation will be catch the exception and redo the change until the change is successful. [you can add a timeout to this to ensure that the code execution moves forward in the worst case scenario]

Answer 3

There is another solution that we are using, and you can use to avoid to use Store Procedures, specially if you are using EF Code First like us. Code First doesn't support the use of Store Procedures.

The solution is using SqlQuery Method of EF inside the scope of transaction, you have to write a query that first do an update of the next number counter, and then select It . The code will be:

string query = "UPDATE Registre_Counter SET Counter = Counter + 1 WHERE CounterName = @p0 AND Year = @p1;";
query += "select * from Registre_Counter Where CounterName = @p0 AND Year = @p1";

GenericCounter GenericCounter = CoreDBContext.Instance().GenericCounter.SqlQuery(query, new Object[] { _counterName, _year }).SingleOrDefault(); //Updates the counter and return de NextNumber to Use

The Update will lock the counter and avoid reads from other transactions on it until the transaccion is aborted or commited.