Optmistic Concurrency and Timestamps in SQL Server Using LLBLGen Pro O/R Mapper

by David Hayden ( ASP.NET C# SQL Server Developer )

I am still getting over the flu that we have been fighting at home the past week, and in my weakened state, I somehow got distracted :) today on implementing optimistic concurrency in SQL Server using Timestamps and LLBLGen Pro. Looking back on previous posts, I have talked about optmistic concurrency and timestamps a few times:

LLBLGen Pro O/R Mapper and Timestamps for Optmistic Concurrency

While I was playing with LLBLGen Pro on a test database table:

David Hayden - Optimistic Concurrency Using Timestamps.

I noticed that it didn't automatically use the Timestamp column as part of the Update Statement to check for concurrency violations on the row. Without a timestamp, updating the blog name might look like this with no concurrency checking:

UPDATE Blogs SET Name=@Name WHERE BlogId = @BlogId

With a timestamp column, you would tend to see this:

UPDATE Blogs SET Name=@Name WHERE BlogId = @BlogId
    AND Timestamp = @Timestamp

If the timestamp is different, a change must have happened to the underlying record and the blog name would not be updated during this update command.

So, LLBLGen Pro wasn't using the Timestamp automagically as I expected using the following code:

DataAccessAdapter adapter = new DataAccessAdapter(...);

BlogEntity blog = new BlogEntity();
blog.BlogId = 1;

adapter.FetchEntity(blog);

blog.Name = "New Name";

adapter.SaveEntity(blog);

Luckily, LLBLGen Pro's documentation is awesome, and I found the answer pretty easily. And... the answer is pretty cool and flexible in case you want to specify or use other concurrency strategies other than Timestamp.

Factory Class for Specifying Timestamp Optimistic Concurrency

There is an overload to SaveEntity that not only allows you to specify whether to refetch the entity after updating it ( important to get the new timestamp ), but also to specify an update restriction ( Predicate Expression ) for concurrency:

bool SaveEntity(IEntity2 entityToSave, bool refetchAfterSave,
                 IPredicateExpression updateRestriction);

Below looks worse than it actually is, but I used the example in the documentation that creates a separate factory for creating the Predicate Expression that specifies the concurrency strategy:

public class BlogConcurrencyFactory :
    IConcurrencyPredicateFactory
{
    public IPredicateExpression CreatePredicate(
        ConcurrencyPredicateType predicateTypeToCreate,
        object containingEntity)
    {
        IPredicateExpression toReturn = new PredicateExpression();
           BlogEntity blogEntity = (BlogEntity)containingEntity;

        switch (predicateTypeToCreate)
        {
            case ConcurrencyPredicateType.Save:
                // only for updates
                toReturn.Add(BlogFields.Timestamp == blogEntity.
                  Fields[(int)BlogFieldIndex.Timestamp].DbValue);
                break;
        }
        return toReturn;
    }
}

which basically says to make sure you check the Timestamp with its original value before doing the update. Here is now the new client code using the Timestamp:

DataAccessAdapter adapter = new DataAccessAdapter(...);

BlogEntity blog = new BlogEntity();
blog.BlogId = 1;

adapter.FetchEntity(blog);

blog.Name = "New Name";

IPredicateExpression expression = new BlogConcurrencyFactory()
      .CreatePredicate(ConcurrencyPredicateType.Save, blog);

adapter.SaveEntity(blog, true, expression);

With very few changes, we now have LLBLGen Pro updating the changes while checking the Timestamp and refetching the new values ( especially the new Timestamp ) so the entity is in sync with the database.

Switching Optimistic Concurrency Strategies is Painless

Now the beauty here is that not only was this embarassingly easy, but that I can specify a different type of concurrency strategy by just changing BlogConcurrencyFactory. Assuming the Timestamp column wasn't there or we are no longer using it, we can use a concurrency strategy of checking all original values by only changing the BlogConcurrencyFactory Class in about 15 seconds:

public class BlogConcurrencyFilterFactory :
IConcurrencyPredicateFactory
{
    public IPredicateExpression CreatePredicate(
        ConcurrencyPredicateType predicateTypeToCreate,
        object containingEntity)
    {
        IPredicateExpression toReturn = new PredicateExpression();
        BlogEntity blogEntity = (BlogEntity)containingEntity;
    
        switch (predicateTypeToCreate)
        {
            case ConcurrencyPredicateType.Save:
                // only for updates
                toReturn.Add(BlogFields.Name == blogEntity.Fields
                    [(int)BlogFieldIndex.Name].DbValue);
                break;
        }
        return toReturn;
    }
}

By specifying Name instead of Timestamp above, we now have changed the update query to look something like this:

UPDATE Blogs SET Name=@Name WHERE BlogId = @BlogId
                              AND Name = @OriginalName

which is essentially checking all the original values before updating the new name.

Conclusion

I love the flexibility and I love the concept of a factory here as a central location to build a Predicate Expression that specifies the concurrency strategy for this entity.

Even if you don't use LLBLGen Pro, these are sound principles for handling concurrency strategies in your data access layer / application.

Source: David Hayden ( ASP.NET C# SQL Server Developer )

Filed: SQL Server, O/R Mappers

posted on Tuesday, October 24, 2006 1:30 PM