sql-server - 为什么使用 READ UNCOMMITTED 隔离级别？

Question

用通俗的话说，使用的缺点和优点是什么

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

查询 .NET 应用程序和报告服务应用程序？

Answer 1

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

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

您可能想查看Wikipedia article on READ UNCOMMITTED一些示例和进一步阅读。

<小时/>

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

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."

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.

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。再次引用杰夫的话:

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.