Tuesday, June 8, 2010

Execute LINQ queries without locking any tables

At work I'm building an application which queries a SQL Server backend with LINQ to SQL, and I recently found that some of my queries were slow due to table locking. I found this excellent blog post referencing Scott Hanselman's blog post which describes how to execute LINQ queries without locking the source tables:

Deciding to go with option 1, I came up with an elegant solution for easily adding this to any LINQ queries. Let's say you are using a LINQ query like this:

var query = from f in Foo
select new { Foo = f };

foreach (var item in query)
{
//Do something
}

In this case, the query is actually executed in the foreach loop. By implementing an extension method, you can easily change this to use a transaction with the "Read Uncommitted" Isolation Level like so:

var query = from f in Foo
select new { Foo = f };

foreach (var item in query.ToListNoLock())
{
//Do something
}

The extension method is implemented like so:


public static class IEnumerableExtension
{
public static TransactionScope CreateNoLockTransaction()
{
var options = new TransactionOptions
{
IsolationLevel = IsolationLevel.ReadUncommitted
};
return new TransactionScope(TransactionScopeOption.Required, options);
}


public static List<T> ToListNoLock<T>(this IEnumerable<T> query)
{
using (TransactionScope ts = CreateNoLockTransaction())
{
return query.ToList();
}
}
}


And then of course you can implement new versions of various other extension methods on IEnumerable/IQueryable, such as SingleOrDefault, FirstOrDefault, etc.


Note that this is the same as adding (NOLOCK) hints to EVERY table in your query. I do not currently know of a way to add nolock hints to individual tables.

No comments:

Post a Comment