为什么使用 READ UNCOMMITTED 隔离级别? [英] Why use a READ UNCOMMITTED isolation level?

问题描述

简单来说，使用的优缺点是什么

In plain English, what are the disadvantages and advantages of using

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

在 .NET 应用程序和报告服务应用程序的查询中?

in a query for .NET applications and reporting services applications?

推荐答案

此隔离级别允许脏读.一个事务可能会看到其他事务所做的未提交更改.

This isolation level allows dirty reads. One transaction may see uncommitted changes made by some other transaction.

为了保持最高级别的隔离，DBMS 通常会获取数据锁，这可能会导致并发丢失和高锁定开销.此隔离级别放宽了此属性.

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.

您可能想查看 关于READ UNCOMMITTED 的维基百科文章，了解一些示例和进一步阅读.

You may want to check out the Wikipedia article on READ UNCOMMITTED for a few examples and further reading.

您可能还有兴趣查看 Jeff Atwood 的博客文章 关于他和他的团队如何在 Stack Overflow 的早期解决僵局问题.根据杰夫的说法:

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:

但是 nolock 危险吗?你能结束吗?使用 read uncommitted 读取无效数据?是的，理论上.你会发现不乏数据库开始的建筑宇航员将 ACID 科学放在你和所有人身上但拉建筑物火警时你告诉他们你想尝试 nolock.这是真的:这个理论很可怕.但这就是我的想法:理论上有理论和没有区别实践.实际上是有的."

But is nolock dangerous? Could you end up reading invalid data with read uncommitted on? Yes, in theory. You'll find no shortage of database architecture astronauts who start dropping ACID science on you and all but pull the building fire alarm when you tell them you want to try nolock. It's true: the theory is scary. But here's what I think: "In theory there is no difference between theory and practice. In practice there is."

我永远不会推荐使用 nolock作为一般对你的病有好处"任何数据库的蛇油修复您可能遇到的死锁问题.你应该尝试诊断来源先解决问题.

I would never recommend using nolock as a general "good for what ails you" snake oil fix for any database deadlocking problems you may have. You should try to diagnose the source of the problem first.

但在实践中，将 nolock 添加到您绝对知道是简单、直接的只读事务的查询似乎永远不会导致问题... 只要您知道自己在做什么

But in practice adding nolock to queries that you absolutely know are simple, straightforward read-only affairs never seems to lead to problems... As long as you know what you're doing.

您可能需要考虑的 READ UNCOMMITTED 级别的替代方法是 READ COMMITTED SNAPSHOT.再次引用杰夫:

One alternative to the READ UNCOMMITTED level that you may want to consider is the READ COMMITTED SNAPSHOT. Quoting Jeff again:

快照依赖于一种全新的数据更改跟踪方法......不仅仅是轻微的逻辑更改，它需要服务器以不同的物理方式处理数据.启用这种新的数据更改跟踪方法后，它会创建每个数据更改的副本或快照.通过在争用时读取这些快照而不是实时数据，读取时不再需要共享锁，并且整体数据库性能可能会提高.

Snapshots rely on an entirely new data change tracking method ... more than just a slight logical change, it requires the server to handle the data physically differently. Once this new data change tracking method is enabled, it creates a copy, or snapshot of every data change. By reading these snapshots rather than live data at times of contention, Shared Locks are no longer needed on reads, and overall database performance may increase.

这篇关于为什么使用 READ UNCOMMITTED 隔离级别?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！