Calling Dibs on a table row

Question

The problem I have is this:

I have a table (just an example) with the following fields:

ID int
Value int

I have a method called IncreaseByFive() which does the following:

method IncreaseByFive(int ID)
{    
    int value = GetValueFromDB(ID);
    value = value + 5;
    SaveValueToDB(value, ID);    
}

What I want to avoid is the following situation:

1. User A calls method and gets the value (currently 5)
2. User B calls method and gets the value (currently 5)
3. User A increases value by 5 (now 10)
4. User B increases value by 5 (now 10)
5. User A saves value (10)
6. User B saves value (10)

Now the record has a value of 10 when it should have been 15.

What I want to force is the following:

1. User A calls method and gets the value (currently 5)
2. User B calls method but has to wait because A already called it. (Dibs!)
3. User A increases the value (now 10)
4. User B is still waiting
5. User A saves the value (record has value of 10)
6. User B can now read the value (10)
7. User B increases the value (15)
8. User B saves the value

Now the record has a value of 15, which is the outcome I'm looking for.

I've solved this problem in the past using a static class and putting a lock on a static object created within the constructor of that class, thus funneling all the work through one static method, which forces other calls to wait in line. I'm afraid however that this isn't scalable.

I also think that the the highest isolation level (serializable) for a transaction won't do the trick either because it will allow the read in step 2 of the unwanted example above.

I suppose another solution would be to create my own lock table and record the locks there, but that seems like it shouldn't be neccessary.

I'm developing this project with C# (3.5) and SQL server 2008.

What does the hive mind think?

Answer 1

Maybe I'm overunderthinking this, but wouldn't you just wrap the entire logic block..

• read value
• write value

in a database transaction of the appropriate level?

SET TRANSACTION ISOLATION LEVEL
    { READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SNAPSHOT
    | SERIALIZABLE
    }
[ ; ]