在NOLOCK上使用ADO.NET TransactionScope和ExecuteCommand的高容量站点,是否直接读取未提交? [英] High volume site using ADO.NET TransactionScope vs ExecuteCommand on NOLOCK, READ UNCOMMITTED directly?
问题描述
只需阅读Omar在他的博客上发表的这篇有趣的文章 贾维德·哈桑(Javed Hasan)建议 我非常想知道你们在StatckOverflow这样的高流量站点上针对这个特定问题做了什么,或者Jeff和他们在这方面做了什么? >
编辑:在阅读了第一篇文章之后,我想指出Omar文章中的一些内容.
首先,请避免进行未提交的读取,因为这会引起很多问题.更好的方法是将数据库设置为快照隔离. 这就是杰夫所做的. 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 whereas Javed Hasan suggests 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.
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: 这篇关于在NOLOCK上使用ADO.NET TransactionScope和ExecuteCommand的高容量站点,是否直接读取未提交?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!using (new TransactionScope(TransactionScopeOption.Required,
new TransactionOptions { IsolationLevel = IsolationLevel.ReadUncommitted }))
{
//Your db Operation
}
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();
}
using (new TransactionScope(TransactionScopeOption.Required,
new TransactionOptions { IsolationLevel = IsolationLevel.ReadUncommitted }))
{
//Your db Operation
}