BT

Implementing NOLOCK with LINQ to SQL and LINQ to Entities

by Robert Bazinet on Mar 21, 2008 |

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.

 

Hello stranger!

You need to Register an InfoQ account or to post comments. But there's so much more behind being registered.

Get the most out of the InfoQ experience.

Tell us what you think

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

Original Content by E R

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

1 Discuss

Educational Content

General Feedback
Bugs
Advertising
Editorial
InfoQ.com and all content copyright © 2006-2013 C4Media Inc. InfoQ.com hosted at Contegix, the best ISP we've ever worked with.
Privacy policy
BT