Posted by Robert Bazinet  From/InfoQ

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.

感谢原创者的辛勤劳动，希望对您有所帮助，转载请注明原出处。

您可能对 [Linq] 的这些文章也感兴趣： Jimmy Nilsson谈LINQ to SQL 深入浅出学Linq：Linq to SQL感性认识篇 Linq入门与提高 LINQ to XSD is Back .Net Compact Framework 3.5对Linq的支持 使用 Linq 在不同类型之间转换 LINQ体验(12)——LINQ to SQL语句之对象标识和对象加载 LINQ查询关键字使用之from,group,select,into,where(C#) Linq to sql--用存储过程检索数据 Creating custom LINQ provider using LinqExtender

在使用LINQ to SQL和LINQ to Entities时实现NOLOCK

译/赵劼

Scott Hanselman最近发表了一篇非常有用的文章，谈到了如何在使用LINQ to SQL和LINQ to Entities时利用NOLOCK选项。这个问题实际是在找出一个办法，使LINQ查询生成的SQL语句能够像SQL开发人员常用的做法那样加上NOLOCK选项。

既然LINQ to SQL会动态生成SQL查询，因此向开发人员提供控制查询语句的能力还是有一定必要的。Scott指出，并非所有的情况都应该使用NOLOCK选项，它只是最后一个可以依靠的手段：

然而，使用NOLOCK（即使“每个人”多多少少都用过这个方法）通常是最后一个可以依靠的手段。使用了NOLOCK的查询并不保证能返回正确地结果，而且从技术上讲，它可能会返回任意结果。
SQL 2005有一个基于快照的隔离级别 ，它能在不允许“脏读”的情况下避免数据读取阻塞数据写入，或者数据写入阻塞数据读取。

对于我个人来说，目前的NOLOCK在旧有的具有一定规模的系统中，工作得很好，但是我也听闻有人认为应该避免使用NOLOCK。对于这一点，完全就要看此人是如何定义“正确结果”这个概念了。;)

对于在查询中添加NOLOCK选项，Scott提出了三种方法：

推荐的做法是使用TransactionScope来控制LINQ to SQL和LINQ to Entities执行命令时的事务选项（译者注：如果在一个TransactionScope的作用范围内开启多个数据库连接就会引发基于MSDTC的分 布式事务，从而降低性能，在使用这种做法时要注意这一点）：

LINQ to SQL同样支持显式设置上下文的事务，所以您可以获取上下文的数据库连接，然后打开它，开启一个事务，并在上下文中设置。如果您觉得SQL 2005提升事务级别过于频繁，不妨试试这个做法。不过，最佳选择则是使用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();
}

第二种做法已经被证明是卓有成效的，那就是使用存储过程：

至于第二种做法，您依旧可以创建带有NOLOCK的存储过程，并且使用LINQ to SQL来访问它们。不过对于LINQ to SQL和LINQ to Entities动态生成的SQL语句来说，较好的选择依旧是使用TransactionScope来避免查询对于它所读取的表的锁定。

第三种方法是在DataContext级别中进行设置：

至于第三种做法，您可以在DataContext级别进行设置（很明显，这会影响每个生成的执行在上下文之上的LINQ to SQL查询语句），使它执行如下命令：
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

对于Scott提出的每种方法，赞成者有之，同时也不乏反对者。也有一些争论是关于NOLOCK选项本身，以及是否应该在部署时使用NOLOCK选 项。例如，如果按照第一种方式来使用NOLOCK选项，那么当NOLOCK不再需要时，就需要重新进行部署。而如果NOLOCK是在存储过程中使用的，那 么只需要在数据库级别进行修改即可。

显然，NOLOCK只是如今SQL中许多选项中的一种。而对于上面提到的技术，也没有证据说明不能用同样的方法使用其它SQL选项。

若要了解更多有关LINQ to SQL和LINQ to Entities的信息，敬请访问MSDN网站。您也可以在Computerzen.com中访问到Scott Hanselman著名的博客。