为什么使用 READ UNCOMMITTED 隔离级别？
简而言之，
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
在 .NET 应用程序和报告服务应用程序的查询中使用它们有哪些缺点和优点？
In plain English, what are the disadvantages and advantages of using
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
in a query for .NET applications and reporting services applications?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（11）
此隔离级别允许脏读。一个事务可能会看到其他事务所做的未提交的更改。
为了保持最高级别的隔离，DBMS 通常会获取数据锁，这可能会导致并发性损失和较高的锁定开销。此隔离级别放宽了此属性。
您可能需要查看 关于
READ UNCOMMITTED的维基百科文章，获取一些示例和进一步阅读。您可能还有兴趣查看 Jeff Atwood 的 博客文章他和他的团队如何在 Stack Overflow 早期解决僵局问题。据杰夫说：
您可能需要考虑的
READ UNCOMMITTED级别的一种替代方案是READ COMMITTED SNAPSHOT。再次引用杰夫的话：This isolation level allows dirty reads. One transaction may see uncommitted changes made by some other transaction.
To maintain the highest level of isolation, a DBMS usually acquires locks on data, which may result in a loss of concurrency and a high locking overhead. This isolation level relaxes this property.
You may want to check out the Wikipedia article on
READ UNCOMMITTEDfor a few examples and further reading.You may also be interested in checking out Jeff Atwood's blog article on how he and his team tackled a deadlock issue in the early days of Stack Overflow. According to Jeff:
One alternative to the
READ UNCOMMITTEDlevel that you may want to consider is theREAD COMMITTED SNAPSHOT. Quoting Jeff again:我最喜欢的
read uncommited用例是调试事务中发生的事情。在调试器下启动软件，当您单步执行代码行时，它会打开一个事务并修改您的数据库。当代码停止时，您可以打开查询分析器，设置读取未提交的隔离级别并进行查询以查看发生了什么。
您还可以使用它来查看长时间运行的程序是否卡住或使用
count(*)查询正确更新数据库。如果您的公司喜欢制作过于复杂的存储过程，那就太好了。
My favorite use case for
read uncommitedis to debug something that is happening inside a transaction.Start your software under a debugger, while you are stepping through the lines of code, it opens a transaction and modifies your database. While the code is stopped, you can open a query analyzer, set on the read uncommited isolation level and make queries to see what is going on.
You also can use it to see if long running procedures are stuck or correctly updating your database using a query with
count(*).It is great if your company loves to make overly complex stored procedures.
这对于查看长插入查询的进度、进行粗略估计（例如
COUNT(*)或粗略SUM(*)）等很有用。换句话说，只要您将脏读查询返回的结果视为估计值并且不根据它们做出任何关键决策，脏读查询返回的结果就很好。
This can be useful to see the progress of long insert queries, make any rough estimates (like
COUNT(*)or roughSUM(*)) etc.In other words, the results the dirty read queries return are fine as long as you treat them as estimates and don't make any critical decisions based upon them.
优点是在某些情况下可以更快。缺点是结果可能是错误的（可能会返回尚未提交的数据）并且不能保证结果是可重复的。
如果您关心准确性，请不要使用此功能。
更多信息请参见 MSDN：
The advantage is that it can be faster in some situations. The disadvantage is the result can be wrong (data which hasn't been committed yet could be returned) and there is no guarantee that the result is repeatable.
If you care about accuracy, don't use this.
More information is on MSDN:
什么时候可以使用
READ UNCOMMITTED？经验法则
好：显示不断变化的总数的大型汇总报告。
有风险：几乎所有其他事情。
好消息是，大多数只读报告都属于好类别。
更多详细信息...
可以使用它：
它存在误差幅度（可能< 0.1%），该误差幅度远低于其他不确定因素，例如输入误差或每分钟准确记录数据的随机性。
这可能涵盖了商业智能部门在 SSRS 中所做的大部分工作。当然，前面带有 $ 符号的任何内容都是例外。许多人对金钱的热情远远高于对服务客户和产生金钱所需的相关核心指标的热情。 （我责怪会计师）。
有风险时
任何深入到细节级别的报告。如果需要该详细信息，通常意味着每一行都与决策相关。事实上，如果您无法在不阻塞的情况下提取一小部分子集，则可能是因为它当前正在编辑。
历史数据。它很少产生实际差异，但尽管用户知道不断变化的数据不可能是完美的，但他们对静态数据却有不同的感觉。脏读在这里不会造成伤害，但双重读取有时会造成伤害。既然无论如何都不应该对静态数据进行阻止，为什么还要冒险呢？
几乎任何为也具有写入功能的应用程序提供数据的东西。
即使是正常的情况也不正常。
NOLOCK进行任何操作。When is it ok to use
READ UNCOMMITTED?Rule of thumb
Good: Big aggregate reports showing constantly changing totals.
Risky: Nearly everything else.
The good news is that the majority of read-only reports fall in that Good category.
More detail...
Ok to use it:
It risks a margin of error (maybe < 0.1%) which is much lower than other uncertainty factors such as inputting error or just the randomness of when exactly data gets recorded minute to minute.
That covers probably the majority of what an Business Intelligence department would do in, say, SSRS. The exception of course, is anything with $ signs in front of it. Many people account for money with much more zeal than applied to the related core metrics required to service the customer and generate that money. (I blame accountants).
When risky
Any report that goes down to the detail level. If that detail is required it usually implies that every row will be relevant to a decision. In fact, if you can't pull a small subset without blocking it might be for the good reason that it's being currently edited.
Historical data. It rarely makes a practical difference but whereas users understand constantly changing data can't be perfect, they don't feel the same about static data. Dirty reads won't hurt here but double reads can occasionally be. Seeing as you shouldn't have blocks on static data anyway, why risk it?
Nearly anything that feeds an application which also has write capabilities.
When even the OK scenario is not OK.
NOLOCKon those tables for anything.在源极不可能更改的情况下使用 READ_UNCOMMITTED。
当您知道源在获取操作期间可能会发生变化时，请勿使用 READ_UNCOMMITTED。
Use READ_UNCOMMITTED in situation where source is highly unlikely to change.
Don't use READ_UNCOMMITTED when you know souce may change during fetch operation.
关于报告，我们在所有报告查询中使用它，以防止查询陷入数据库困境。我们可以做到这一点，因为我们正在提取历史数据，而不是精确到微秒的数据。
Regarding reporting, we use it on all of our reporting queries to prevent a query from bogging down databases. We can do that because we're pulling historical data, not up-to-the-microsecond data.
这会给你脏读，并显示尚未提交的事务。这是最明显的答案。我认为仅仅使用它来加快阅读速度并不是一个好主意。如果您使用良好的数据库设计，还有其他方法可以做到这一点。
注意到没有发生的事情也很有趣。 READ UNCOMMITTED 不仅会忽略其他表锁。它本身也不会导致任何锁定。
假设您正在生成大型报告，或者您正在使用大型且可能复杂的 SELECT 语句将数据从数据库中迁移出来。这将导致共享锁在事务期间可能升级为共享表锁。其他事务可以从表中读取，但更新是不可能的。如果它是生产数据库，这可能是一个坏主意，因为生产可能会完全停止。
如果您使用 READ UNCOMMITTED，则不会在表上设置共享锁。您可能会从某些新事务中获得结果，也可能不取决于数据插入表的位置以及 SELECT 事务读取的时间。如果发生页面拆分（数据将被复制到数据文件中的另一个位置），您也可能会获得两次相同的数据。
因此，如果在执行 SELECT 时插入数据对您来说非常重要，那么 READ UNCOMMITTED 可能是有意义的。您必须考虑到您的报告可能包含一些错误，但如果它基于数百万行，并且在选择结果时仅更新其中的少数行，则这可能“足够好”。您的事务也可能会一起失败，因为可能无法保证行的唯一性。
更好的方法可能是使用快照隔离级别，但您的应用程序可能需要一些调整才能使用它。一个示例是，如果您的应用程序对某一行采用独占锁，以防止其他人读取该行并进入 UI 中的编辑模式。快照隔离级别也会带来相当大的性能损失（尤其是在磁盘上）。但你可以通过使用硬件来解决这个问题。 :)
您还可以考虑恢复数据库的备份以用于报告或将数据加载到数据仓库中。
This will give you dirty reads, and show you transactions that's not committed yet. That is the most obvious answer. I don't think its a good idea to use this just to speed up your reads. There is other ways of doing that if you use a good database design.
Its also interesting to note whats not happening. READ UNCOMMITTED does not only ignore other table locks. It's also not causing any locks in its own.
Consider you are generating a large report, or you are migrating data out of your database using a large and possibly complex SELECT statement. This will cause a shared lock that's may be escalated to a shared table lock for the duration of your transaction. Other transactions may read from the table, but updates are impossible. This may be a bad idea if its a production database since the production may stop completely.
If you are using READ UNCOMMITTED you will not set a shared lock on the table. You may get the result from some new transactions or you may not depending where it the table the data were inserted and how long your SELECT transaction have read. You may also get the same data twice if for example a page split occurs (the data will be copied to another location in the data file).
So, if its very important for you that data can be inserted while doing your SELECT, READ UNCOMMITTED may make sense. You have to consider that your report may contain some errors, but if its based on millions of rows and only a few of them are updated while selecting the result this may be "good enough". Your transaction may also fail all together since the uniqueness of a row may not be guaranteed.
A better way altogether may be to use SNAPSHOT ISOLATION LEVEL but your applications may need some adjustments to use this. One example of this is if your application takes an exclusive lock on a row to prevent others from reading it and go into edit mode in the UI. SNAPSHOT ISOLATION LEVEL does also come with a considerable performance penalty (especially on disk). But you may overcome that by throwing hardware on the problem. :)
You may also consider restoring a backup of the database to use for reporting or loading data into a data warehouse.
READ UNCOMMITTED 隔离级别不维护页、行、表或分区上的锁。因此，在全天运行大量进程的生产环境中，这种隔离级别可能是有利的。如果允许脏读并且需要更快的响应时间，那么使用 READ UNCOMMITTED 隔离级别可以帮助防止死锁并最大限度地减少执行期间的等待时间。
脏读：当您的事务观察到另一个事务所做的未提交更改时，就会发生这种情况。
最高隔离级别：这代表最严格的隔离级别，可提高并发性并减少与锁定机制相关的开销。
在我以前的组织中，我很少遇到任何使用此隔离级别的过程。然而，在我目前规模较大的组织中，几乎所有过程都设置了隔离级别。
The READ UNCOMMITTED isolation level does not maintain locks on pages, rows, tables, or partitions. Therefore, in a production environment where numerous processes are running throughout the day, this isolation level can be advantageous. If dirty reads are permissible and a quicker response time is essential, then using the READ UNCOMMITTED isolation level can help prevent deadlocks and minimize wait times during execution.
Dirty Reads: This occurs when your transaction observes uncommitted changes made by another transaction.
Highest Level of Isolation: This represents the most stringent isolation level, which promotes concurrency and reduces the overhead associated with locking mechanisms.
In my previous organization, I rarely encountered any procedures that utilized this isolation level. However, in my current organization, which is larger, the isolation level is set in nearly all the procedures.
它可以用于简单的表，例如在仅插入审计表中，其中没有对现有行的更新，也没有对其他表的 fk。该插入是简单插入，没有或很少有回滚的机会。
It can be used for a simple table, for example in an insert-only audit table, where there is no update to existing row, and no fk to other table. The insert is a simple insert, which has no or little chance of rollback.
我现在总是使用 READ UNCOMMITTED。速度很快，问题最少。当使用其他隔离时，您几乎总会遇到一些阻塞问题。
只要您使用自动增量字段并多注意插入，那么您就可以告别阻塞问题。
您可能会在 READ UNCOMMITED 中犯错误，但说实话，确保您的插入内容是完整的证据非常容易。使用选择结果的插入/更新是您唯一需要注意的事情。 （这里使用READ COMMITTED，或者确保脏读不会导致问题）
所以去脏读（特别是对于大报告），你的软件会运行得更流畅......
I always use READ UNCOMMITTED now. It's fast with the least issues. When using other isolations you will almost always come across some Blocking issues.
As long as you use Auto Increment fields and pay a little more attention to inserts then your fine, and you can say goodbye to blocking issues.
You can make errors with READ UNCOMMITED but to be honest, it is very easy make sure your inserts are full proof. Inserts/Updates which use the results from a select are only thing you need to watch out for. (Use READ COMMITTED here, or ensure that dirty reads aren't going to cause a problem)
So go the Dirty Reads (Specially for big reports), your software will run smoother...