在NOLOCK上使用ADO.NET TransactionScope和ExecuteCommand的高容量站点，是否直接读取未提交? [英] High volume site using ADO.NET TransactionScope vs ExecuteCommand on NOLOCK, READ UNCOMMITTED directly?

问题描述

只需阅读Omar在他的博客上发表的这篇有趣的文章

贾维德·哈桑(Javed Hasan)建议

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

我非常想知道你们在StatckOverflow这样的高流量站点上针对这个特定问题做了什么，或者Jeff和他们在这方面做了什么?

编辑:在阅读了第一篇文章之后，我想指出Omar文章中的一些内容.

1. 他的方法确实遇到了连接问题，但他解决了，请参阅他的帖子.
2. 更重要的是，他提到他尝试使用ADO.NET事务处理方式，甚至尝试了Scott Hanselman在他的博客上写的内容，但是它不适用于大量站点，这会大大降低性能.奥马尔说:"System.Transactions的开销很大.如果不让CPU占用100％的内存，而Req/sec降低到1/10，我将无法在高容量的网站上使用它.大量网站."

解决方案

首先，请避免进行未提交的读取，因为这会引起很多问题.更好的方法是将数据库设置为快照隔离. 这就是杰夫所做的.

Jeff基本上说:真实，真实，数据库理论家，bla bla，READ UNCOMMITTED对于不需要数据一致性的REAL生产应用程序很有用." Jeff不是DBA，幸运的是，SO上有很多DBA.

Omar的方法存在的问题是，它可能会将具有读取未提交"隔离级别的连接泄漏到您的连接池中，这可能会对您的网站造成严重破坏.意味着随机语句可以在未提交的读取状态下执行.

使用精简的方法会更好，因为在处置时，MS有机会清理连接上的内容.

编辑.如果您在Javed的方法上遇到性能问题，可以考虑滚动自己的交易管理器.

您可能想做的一些事情:

• 保留一堆当前交易记录
• 确认提交事务时您是否位于创建者线程上
• 在处置时将事务隔离重置为其先前的状态
• 如果未提交交易，则回滚处置.
• 支持嵌套回滚.

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.

1. he did run into connection problem with his approach but he solved it, see his post.
2. 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."

解决方案

First of all please avoid uncommitted reads, they can cause lots of issues. A much better approach is just to set the database to snapshot isolation. This is what Jeff did.

Jeff basically said: "bla bla bla, be real, bla bla bla, database theoreticians, bla bla bla, READ UNCOMMITTED can be useful for REAL production apps that don't need data consistency." Jeff is not a DBA, fortunately there are many DBAs out here on SO.

The problem with Omar's approach is that it can leak connections with "read uncommitted" isolation level in to your connections pool which could wreak havoc in your website. Meaning random statement may be executed in read uncommitted.

Javed approach would be much better because on dispose MS have the chance to clean stuff up on the connection.

EDIT If you are having performance issues with Javed's approach you could look at rolling your own transaction manager.

Some things you probably want to do:

• Hold a stack of current transactions
• Confirm you are on the creator thread when a transaction is committed
• Reset the transaction isolation to its previous state on dispose
• Rollback on dispose if the transaction was not committed.
• Support nested rollbacks.

这篇关于在NOLOCK上使用ADO.NET TransactionScope和ExecuteCommand的高容量站点，是否直接读取未提交?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！