Tigraine

Daniel Hoelbling-Inzko talks about programming

Locking with Linq to SQL’s deferred execution

Posted by Daniel Hölbling on April 22, 2009

If you have been reading this blog lately you may have noticed that I’m currently working on a project where I chose Linq to Sql as my data-source, inspired by the IQueryable<T> Repository Rob Conery introduced in his MVC Storefront series.

The basic idea of the IQueryable<T> repository is to have the repository return a IQueryable list of C# domain objects that can then be filtered, queried, parsed at higher layers of the application.
So things like paging, filtering etc (all business concerns) can be applied to the query at a later stage instead of having to propagate all of these requirements down to the Repository (Ayende wrote something great on the death of Repository you should check out).

So my business code would call the repository for all objects and then apply logic to it:

var preferredUsers = 
    repository.GetAll()
    .Where(p => p.JoinDate < DateTime.Now.AddYears(-1))
    .Skip(PAGE_SIZE*pageNumber)
    .Take(PAGE_SIZE);

I loved it. Not having to propagate concerns like paging and filtering to the DAL was awesome, and since the interface is so damn simple I very quickly came up with decorators that did error handling, caching and logging at the DAL level.

Since I’ve become a dependency injection nut, I then came up with a injectable datacontext so my repositories don’t have anything to do with the data context creation (thus sparing me the configuration concerns in that class).


My concrete repository implementation then looked like this:

public class UserRepository : IRepository<BlogUser> 
{
    private DataClassesDataContext context;

    public UserRepository(DataClassesDataContext context)     {         this.context = context;     }

    public IQueryable<BlogUser> GetAll()     {         return from u in context.Users                select new BlogUser                           {                               Id = u.Id,                               Username = u.Name,                               Password = u.Password                           };     } }

You see, the context gets injected and besides the query there is nothing in the repository, SRP .. check.

Now, the logical thing to do in my IoC configuration was to have the repositories be singletons, and so every repository has one datacontext attached to it.

And this is where it blew up in my face, having multiple threads access the repository leads to some nasty race conditions for the datacontext, and I found no sane way of dealing with this at the DAL level.
Try this:

public static void main()
{
    var repository = new UserRepository(new DataClassesDataContext());
    new Thread(() => ThreadStart(repository)).Start();
    new Thread(() => ThreadStart(repository)).Start();
}

public static void ThreadStart(IRepository<BlogUser> repository) {     const int PAGE_SIZE = 10;     int pageNumber = 1;     var preferredUsers =         repository.GetAll()         .Where(p => p.JoinDate < DateTime.Now.AddYears(-1))         .Skip(PAGE_SIZE*pageNumber)         .Take(PAGE_SIZE);     preferredUsers.ToList(); }

The same query as before, but two threads that share the same repository, therefore sharing the same datacontext. Once started, the whole thing will blow up with a InvalidOperationException stating that the connection is closed.

I didn’t bother to go into the DataContext source and check out why they are closing the connection, but apparently after the query is executed it takes some time for the context to “recover” and be able to accept a new query.

I immediately tried to solve the problem by adding a lock on the datacontext in the repository class (since the contexts are pooled, it was the only thing that made sense since I don’t need to lock all connections, just the one I’m currently using).

public IQueryable<BlogUser> GetAll()
{
    lock (context)
    {
        return from u in context.Users
               select new BlogUser
                          {
                              Id = u.Id,
                              Username = u.Name,
                              Password = u.Password
                          };
    }
}

I intentionally said I tried, because it didn’t work. The lock gets executed alright, but the query isn’t run inside the lock{} but rather at the calling code, in my business class (the power of deferred execution). So the only way to prevent a race condition for my datacontext would have been to add locking to the business code:

const int PAGE_SIZE = 10;
int pageNumber = 1;
var preferredUsers = 
    repository.GetAll()
    .Where(p => p.JoinDate < DateTime.Now.AddYears(-1))
    .Skip(PAGE_SIZE*pageNumber)
    .Take(PAGE_SIZE);
lock(repository)
{
    preferredUsers.ToList();
}

Omg right? So, besides the fact that I can’t guarantee that two repositories don’t use the same datacontext (and therefore racing against each other), I just opened the Pandora's box of possible errors (give me a month and I’ll forget the locking at least 3 times).
Also, it’s just painful to see an implementation detail of the data access layer leak into the business code for no apparent reason.

And the only way I found on how to solve that problem was to supply a new datacontext to every query, so I get rid of the whole locking. I did so by injecting a datacontext factory into the repository and call the factory every time I execute a query.

This fixed the issue for now, but I don’t feel too good about the solution. Creating new datacontext object for every query somehow feels wrong, and I’d love to hear suggestions from you on how to change that.

Filed under net, programmierung, patterns
comments powered by Disqus

My Photography business

Projects

dynamic css for .NET

Archives

more