在 EF4 中使用 NOLOCK 提示？
我们正在评估 EF4，我的 DBA 说我们必须在所有 SELECT 语句中使用 NOLOCK 提示。因此，我正在研究如何在使用 EF4 时实现这一点。
我读过关于如何在 EF4 中实现这一点的不同想法，但所有这些似乎都是一种变通方法，并未得到 Microsoft 或 EF4 的认可。对于希望其 SELECT 语句在使用 LINQ-to-SQL / LINQ-to-Entities 和 EF4 时包含 NOLOCK 提示的人，“Microsoft 官方”回应是什么？
顺便说一句，我发现的绝对最好的信息是 就在这里，我鼓励对此主题感兴趣的每个人阅读此帖子。
谢谢。
We're evaluating EF4 and my DBA says we must use the NOLOCK hint in all our SELECT statements. So I'm looking into how to make this happen when using EF4.
I've read the different ideas on how to make this happen in EF4, but all seem like a work around and not sanctioned by Microsoft or EF4. What is the "official Microsoft" response to someone who wants their SELECT statement(s) to include the NOLOCK hint when using LINQ-to-SQL / LINQ-to-Entities and EF4?
By the way, the absolute best information I have found was right here and I encourage everyone interested in this topic to read this thread.
Thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
NOLOCK =“READ UNCOMMITTED”=脏读
我假设MS知道为什么他们选择默认隔离级别为“READ COMMITTED”
NOLOCK，事实上任何提示都应该非常明智地使用：不是默认情况下。
您的 DBA 是一个布偶。看到这个（SO）： 在 SQL Sever 中的每个 SELECT 上使用 (nolock) 会发生什么？。如果您碰巧在银行或我可能拥有账户的任何机构工作，请告诉我，以便我可以关闭该账户。
NOLOCK = "READ UNCOMMITTED" = dirty reads
I'd assume MS knows why they chose the default isolation level as "READ COMMITTED"
NOLOCK, in fact any hint, should be used very judiciously: not by default.
Your DBA is a muppet. See this (SO): What can happen as a result of using (nolock) on every SELECT in SQL Sever?. If you happen to work at a bank, or any institution where I may have an account please let me know so I can close it.
我是 Microsoft SQL 组织工具团队的一名开发人员。我没有被授权发表任何官方声明，而且我确信 SO 上有人比我更了解这些事情。尽管如此，我将提供一个友好的经验法则，沿着“过早优化是万恶之源”的主题：
不要使用 NOLOCK（或任何其他与此相关的查询提示），除非必须这样做。如果您有一个 select 语句，它有一个不错的查询计划，并且当系统上几乎没有其他负载时它运行良好，但当其他查询访问同一个表时它会变慢，请尝试添加一些 NOLOCK 提示。但请始终明白，当您这样做时，您将面临获得不一致数据的风险。如果您正在编写一些用于在线银行或控制飞机的关键任务应用程序，这可能是不可接受的。然而，对于许多应用程序来说，性能加速是值得冒这个风险的。不过，要根据具体情况进行评估。不要随心所欲地到处使用它们。
如果您选择使用 NOLOCK，我在 博客中发布了一个解决方案 在 C# 中使用扩展方法，以便您可以轻松更改 LINQ 查询以使用 NOLOCK 提示。如果您可以将此调整为 EF4，请发布您的调整。
I'm a developer on a tools team in the SQL org at Microsoft. I'm in no way authorized to make any official statement, and I'm sure there are people on SO who know more about these things than I do. Nevertheless, I'll offer a friendly rule of thumb, along the theme of "Premature optimization is the root of all evil":
Don't use NOLOCK (or any other query hint for that matter), until you have to. If you have a select statement which has a decent query plan, and it runs fine when there is very little other load on the system, but then it slows down when other queries are accessing the same table, try adding some NOLOCK hints. But always understand that when you do, you run the risk of getting inconsistent data. If you are writing some mission critical app that does online banking or controls an aircraft, this may be unacceptable. However, for many applications the perf speedup is worth the risk. Evaluate on a case-by-case basis, though. Don't just use them willy nilly all over the place.
If you do choose to use NOLOCK, I have blogged a solution in C# using extension methods, so that you can easily change a LINQ query to use NOLOCK hints. If you can adapt this to EF4, please post your adaptation.
如果 ef4 生成您的所有查询，则 EF4 目前没有内置方法来执行此操作。
有多种方法可以解决此问题，例如使用存储过程或更扩展的内联查询模型，但是，至少可以说，这可能非常耗时。
我相信（并且我不代表 Microsoft 发言）缓存是 Microsoft 旨在减轻 EF4 站点中服务器负载的解决方案。当同时运行 2 个上下文时，将未提交的读取（或 nolock）内置到框架中会给 EF4 的预期行为带来不可预测的问题。这并不意味着您的情况需要那种级别的并发性。
听起来好像有人要求您对所有选择进行 nolock。虽然我同意之前发帖者的观点，即如果您有任何需要进行事务的事务，这可能会很危险，但我不同意这会自动使 DBA 成为傀儡。您可能只是运行一个对于脏读来说非常酷的 CMS。您可以更改整个数据库的隔离级别，这可以达到相同的效果。
DBA 可能建议对仅选择的操作使用 nolock（这很好，特别是当 ORM 被误用并进行一些不可靠的数据转储时）。关于这个木偶评论最有趣的事情是 Stack Overflow 本身以 READ UNCOMMITTED 模式运行 SQL Server。猜猜您需要寻找其他地方来获得问题的答案吗？
与您的 DBA 讨论在数据库级别设置此选项的可能性，或者如果您只在少数地方需要它，请考虑缓存策略。毕竟网络是无状态的，所以除非你直接解决它，否则并发通常可能是一种幻觉。
有关隔离级别的信息
EF4 does not currently have a built in way to do it IF ef4 is generating all your queries.
There are ways around this such as using stored procedures or a more extended inline query model, however, this can be time consuming to say the least.
I believe (and I don't speak for Microsoft on this) that caching is Microsoft's intended solution for lightening the load on the server in EF4 sites. Having read uncommitted (or nolock) built into a framework would create unpredictable issues for the expected behaviour of EF4 when 2 contexts are run at the same time. That doesn't mean your situation needs that level of concurrency.
It sounds like you were asked for nolock on ALL selects. While I agree with earlier poster that this can be dangerous if you have ANY transactions that need to be transactions, I don't agree that automatically makes the DBA a muppet. You might just be running a CMS which is totally cool for dirty reads. You can change the ISOLATION LEVEL on your whole database which can have the same effect.
The DBA may have recommended nolock for operations that were ONLY selects (which is fine, especially if there's an ORM being misuesd and doing some dodgy data dumps). The funniest thing about that muppet comment is that Stack Overflow itself runs SQL server in a READ UNCOMMITTED mode. Guess you need to find somewhere else to get answers for your problems then?
Talk to your DBA about the posibility of setting this on a database level or consider a caching strategy if you only need it in a few places. The web is stateless after all so concurrency can often be an illusion anyway unless you address it direclty.
Info about isolation levels
使用 EF4 已有一年多了，我认为使用存储过程来执行特定任务不是黑客，并且在某些情况下对于性能来说绝对是必要的。
我们的平台通过我们的网站、API 和 ETL 数据源获得大量流量。我们主要在 Web 端使用 EF，但也用于一些后端流程。有时 EF 在查询生成方面做得很好，有时却很糟糕。您需要查看正在生成的查询，将它们加载到查询分析器中，并决定是否最好以其他方式（存储过程等）编写操作。
如果您发现需要通过 EF 提供数据并且需要
NOLOCK，您始终可以创建包含NOLOCK提示的视图，并公开该视图 em> 到 EF 而不是基础表。使用存储过程也可以完成同样的操作。当您使用“代码优先”方法时，这些方法可能会更容易一些。但我认为很多人在使用 EF 时犯的一个错误是认为 EF 对象模型必须直接映射到数据库中的物理（表）模型。事实并非如此，这就是 DBA 发挥作用的地方。让他设计您的物理模型，您一起抽象逻辑数据模型，该模型映射到 EF 中的对象模型。
Having worked with EF4 for over a year now, I will offer that using stored procedures for specific tasks is not a hack and absolutely necessary for performance under certain situations.
Our platform gets a lot of traffic through our web site, APIs and ETL data feeds. We use EF primarily on our web side, but also for some back-end processes. Sometimes EF does a great job with its query generation, sometimes it is terrible. You need to look at the queries being generated, load them into query analyzer, and decide whether you might be better off writing the operation in another way (stored procedure, etc.).
If you find that you need to make data available via EF and need
NOLOCKs, you can always create views with theNOLOCKhints included, and expose the view to EF instead of the underlying table. The same can be done with Stored Procedures. These methods are probably a bit easier when you are using the Code First approach.But I think that one mistake a lot of people make with EF is believing that the EF object model has to map directly to the physical (table) model in the database. It doesn't and this is where your DBA comes into play. Let him design your physical model and you work together to abstract your logical data model which is mapped to your object model in EF.
虽然这将是一个主要的 PITA 要做的事情，但您始终可以将 SQL 放入存储过程中并获得您需要的功能（或被迫使用的功能）。但这绝对是一个黑客！
Although this would be a major PITA to do, you can always drop your SQL in a stored procedure and get the functionality you need (or are forced into). It's definitely a hack though!
我知道这不是你问题的答案，但我只是想把这个放进去。
在我看来，这是（至少部分）DBA 的工作。可以说应用程序应该以某种方式运行，并且您可以而且当然应该尝试按照他想要的方式对其进行编程。
不过，唯一可以确定的方法是 DBA 与您一起处理应用程序并构建他想要呈现给应用程序的数据库表面。如果他希望将关键表作为 READ UNCOMMITTED 进行查询，那么他应该帮助提供一组具有正确访问权限和隔离级别的存储过程。
依靠应用程序代码来正确构建每个即席查询并不是一种可扩展的方法。
I know this isn't an answer to your question, but I just wanted to throw this in.
It seems to me that this is (at least partially) the DBA's job. It's fine to say that an application should behave a certain way, and you can and should certainly attempt to program it the way that he would like.
The only way to be sure though, is for the DBA to work on the application with you and construct the DB surface that he would like to present to the app. If he wants critical tables to be queried as READ UNCOMMITTED, then he should help to provide a set of stored procedures with the correct access and isolation level.
Relying on the application code to construct every ad-hoc query correctly is not a scalable approach.