News

Implementing NOLOCK with LINQ to SQL and LINQ to Entities

Posted by Robert Bazinet on Mar 21, 2008

Community: .NET
Topics: Data Access
Tags: SQL Server 2005 ,; LINQ ,; Microsoft

Scott Hanselman recently posted a really useful article on his blog covering the NOLOCK hint when using LINQ to SQL and LINQ to Entities. The problem is actually how to get the SQL generated by LINQ queries to use the NOLOCK hint as SQL developers are accustomed.

Since LINQ to SQL creates SQL queries dynamically, it is not trivial to be able to have an effect on what the query looks like. Scott points out using NOLOCK should not be used in all cases and should only be used as a last resort:

However, with NOLOCK (even though "everyone" has used it at some point or another) is generally considered a last resort. Queries that use NOLOCK aren't guaranteed to return correct results or technically, return any results at all.

SQL 2005 has snapshot-based isolation levels that prevent readers from blocking writers or writers from blocking readers without allowing dirty reads.

Now, I have said that NOLOCK has served me personally very well in the past on systems of some size, but I hear what folks who say no to NOLOCK are saying. It certainly depends on one's definition of "correct results." ;)

Scott points out there are three ways to accomplish the task of adding the NOLOCK hint:

The recommended way is using TransactionScope as a way to affect the transaction options of the commands generated by either LINQ to SQL or LINQ to Entities.

LINQ to SQL also supports explicitly setting the transaction on the context, so you could get the connection from the context, open it, start a transaction, and set it on the context. This can be desirable if you think SQL 2005 is promoting transactions too often, but the preferred method is TransactionScope.
ProductsNewViewData viewData = new ProductsNewViewData();
using (var t = new TransactionScope(TransactionScopeOption.Required,
    new TransactionOptions { 
        IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted 
    }))
{
   viewData.Suppliers = northwind.Suppliers.ToList();
   viewData.Categories = northwind.Categories.ToList();
 }

The second way is the tried and true, stored procedures:

A second way is that you can still create and call Stored Procedures (sprocs) from LINQ to SQL and those sprocs could include NOLOCK, TransactionScope is a better choice for LINQ to SQL or LINQ to Entity generated SQL if you feel that your query doesn't need to lock down the table(s) it's reading from.

The third way is setting it at the DataContext level:

Another third way you could set it at a DataContext level (which, to be clear, would affect every generated LINQ to SQL query executed on that context) would be to execute the command:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

There are certainly pros and cons to each technique mentioned by Scott. There are also arguments about using NOLOCK at all and where the NOLOCK should live when considering deployment. For example, if the NOLOCK setting is done using the recommended way, option #1, then when NOLOCK is no longer needed, a complete binary deployment is required, but if NOLOCK is used in the stored procedure method then the only change is done at the database level.

Of course NOLOCK is only one of many hints used in SQL today and using the techniques above, there is no reason other hints cannot be used the same way.

For more information about LINQ to SQL or LINQ to Entities, please visit the MSDN web site. Scott Hanselman can be found at Computerzen.com where he writes his popular blog. You can read the full text of Scott's original blog post at his blog.