Reading, incrementing and writing back a counter which is unique across multiple users (in EF 4)

Question

I have a database table and a corresponding entity class (POCO with change tracking Proxy) with a member which acts as a counter:

class MyEntity
{
    public virtual int ID { get; set; }
    public virtual int Counter { get; set; }
}

There is a function in my web application which must fetch this entity by ID, read the Counter (which is then used to be assigned to other objects), increment it and write the entity back to the database, like so:

public int GetNewCounter(int ID)
{
    int newCounter = 0;
    using (MyObjectContext ctx = new MyObjectContext())
    {
        MyEntity ent = ctx.MyEntities.Where(e => e.ID == ID).Single();
        ++ent.Counter;
        newCounter = ent.Counter;
        ctx.SaveChanges();
    }
    return newCounter;
}
// newCounter is now used for other stuff

Two users or more could do this at the same time (with the entity of the same ID) and I have to make sure that they don't read and use the same Counter (so the second user should not be able to acquire a counter before the first user has stored the incremented counter back to the database).

Is it possible to "lock" an Entity before it is read and release the lock after writing back the change of the counter and then check for a lock when another user tries to read the same Entity? Is that a reasonable pattern at all?

What options do I have to implement this requirement with Entity Framework (EF 4)?

I am very unfamiliar with the concurrency features and ideas in SQL Server and Entity Framework and would appreciate to get help into the right direction with this specific problem.

Thank you in advance!

Answer 1

I'm pretty sure that TransactionScope (using a transaction) will lock the table, but you may want to be SURE that's what you want (a table lock). You may also look at ConcurrencyMode="fixed", although I'm not sure that will get you exactly what you are looking for.

Answer 2

I have tried a solution using optimistic concurrency and following the answer of ThatSteveGuy by setting the ConcurrencyMode to fixed for the Counter property in MyEntity in the edmx model file.

I'd be happy to see if someone could review this since I don't know if that's a good solution or if there are an easier way.

public int GetNewCounter(int ID)
{
    int newCounter = 0;
    using (MyObjectContext ctx = new MyObjectContext())
    {
        MyEntity ent = ctx.MyEntities.Where(e => e.ID == ID).Single();
        int iRetries = 0;
        bool success = false;

        do
        {
            try
            {
                ++ent.Counter;
                newCounter = ent.Counter;
                ctx.SaveChanges();
                success = true;
            }
            catch (OptimisticConcurrencyException)
            {
                ctx.Refresh(RefreshMode.StoreWins, ent);
                ++iRetries;
                if (iRetries == 3) // give up after 3 retries
                    throw;
            }
        } while (!success);
    }
    return newCounter;
}