使用 ADO.NET TransactionScope 与 NOLOCK 上的 ExecuteCommand 的高容量站点，直接读取未提交的内容？

发布于 2024-07-15 05:14:27 字数 1546 浏览 10 评论 0原文

只需阅读 Omar 在他的博客上发表的这篇有趣的文章 Linq to SQL 使用未提交的读取解决事务死锁和查询超时问题并在最后 Javed Hasan 开始与他争论如何解决高流量站点上的 nolock 情况。

这里，要解决的问题是，从sql意义上讲，我们需要使用带NOLOCK的Select语句或使用SET TRANSACTION LEVEL READ UNCOMMITTED，否则在数据库中的高容量行将被锁定并导致错误。 Omar 使用的技术是 Linq2Sql，所以问题是我们如何在 C# 数据访问代码中实现这一目标，这样上述情况就不会发生？

基本上在这篇文章中，Omar 通过在现实世界的网站上工作和测试以及使用 SqlProfiler 等工具来得出他的解决方案，而 Javed Hasan 通过 MSDN 文档和 Scott Hanselman 的博客文章等得出他的解决方案。Omar

使用以下内容

using (var db = new DropthingsDataContext2())
{
  db.Connection.Open();
  db.ExecuteCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;");

  var user = db.aspnet_Users.First();
  var pages = user.Pages.ToList();
}

建议使用以下内容，而 Javed Hasan 建议

using (new TransactionScope(TransactionScopeOption.Required, 
  new TransactionOptions { IsolationLevel = IsolationLevel.ReadUncommitted }))
{
 //Your db Operation
}

我很想知道你们在像 StatckOverflow 这样的高容量网站上针对这个特定问题做了什么，或者 Jeff 和他们的人在这方面做了什么？

编辑< /strong>：读完第一篇文章后，我想指出 Omar 文章中的一些内容。

他的方法确实遇到了连接问题，但他解决了，请参阅他的帖子。
更重要的是，他提到他尝试过使用 ADO.NET 事务方式，甚至尝试了 Scott Hanselman 在他的博客上写的内容，但它不适用于高容量网站，它会大大降低性能。 Omar 表示，“System.Transactions 的开销很大。我从来没有能够在不使 CPU 达到 100% 且请求/秒下降到 1/10 的情况下在大容量网站上使用它。它是为企业应用程序而设计的，而不是为高性能应用程序而设计的。”卷网站。”

原文

Just read this interesting article by Omar on his blog Linq to SQL solve Transaction deadlock and Query timeout problem using uncommitted reads and at the end
Javed Hasan started arguing with him about his solution to the nolock situation on a high volume site.

Here, the problem trying to solve is, from the sql sense we need to use Select statements with NOLOCK or use SET TRANSACTION LEVEL READ UNCOMMITTED, otherwise at high volume rows in DB will be locked and cause errors. The technology Omar used is Linq2Sql, so the question is how do we get this achieved in your C# data access code so the above does not happen?

Basically in the post, Omar comes to his solution by working and testing on real world site and with tools like SqlProfiler, whereas Javed Hasan comes to his solution with MSDN documents and Scott Hanselman's blog post etc.

Omar suggests using the following

using (var db = new DropthingsDataContext2())
{
  db.Connection.Open();
  db.ExecuteCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;");

  var user = db.aspnet_Users.First();
  var pages = user.Pages.ToList();
}

whereas Javed Hasan suggests

using (new TransactionScope(TransactionScopeOption.Required, 
  new TransactionOptions { IsolationLevel = IsolationLevel.ReadUncommitted }))
{
 //Your db Operation
}

I'm very interested to know what you guys out there do on this particular issue on a high volume site like the StatckOverflow, or what did Jeff and their guys do in this regard?

Edit: After reading the first post, I want to point out a few things in Omar's post.

he did run into connection problem with his approach but he solved it, see his post.
more importantly he mentioned he tried the using ADO.NET Transaction way and even tried what Scott Hanselman wrote on his blog, but it is not working for high volume site, it degrades the performance quite a bit. Omar said this "System.Transactions has significant overhead. I have never been able to use it on a high volume website without making CPU go 100% and Req/sec come down to 1/10th. It's made for enterprise applications, not for high volume websites."

分享到QQ

分享到微博