如何仅在 SQL Server 中选择未提交的行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22245573/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to Select UNCOMMITTED rows only in SQL Server?
提问by BI Dude
I am working on DW project where I need to query live CRM system. The standard isolation level negatively influences performance. I am tempted to use no lock/transaction isolation level read uncommitted. I want to know how many of selected rows are identified by dirty read.
我正在从事 DW 项目，我需要在其中查询实时 CRM 系统。标准隔离级别会对性能产生负面影响。我很想使用未提交的无锁/事务隔离级别读取。我想知道有多少选定的行被脏读识别。
采纳答案by usr
Maybe you can do this:
也许你可以这样做：
SELECT * FROM T WITH (SNAPSHOT)
EXCEPT
SELECT * FROM T WITH (READCOMMITTED, READPAST)
But this is inherently racy.
但这本质上是活泼的。
回答by Sergio
Why do you need to know that?
为什么你需要知道这些？
You use TRANSACTION ISOLATION LEVER READ UNCOMMITTEDjust to indicate that SELECTstatement won't wait till any update/insert/delete transactions are finished on table/page/rows - and will grab even dirtyrecords. And you do it to increase performance. Trying to get information about whichrecords were dirty is like punch blender to your face. It hurts and gives you nothing, but pain. Because they were dirty at some point, and now they aint. Or still dirty? Who knows...
您使用TRANSACTION ISOLATION LEVER READ UNCOMMITTEDjust 表示该SELECT语句不会等到表/页/行上的任何更新/插入/删除事务完成 - 甚至会抓取脏记录。你这样做是为了提高性能。试图获取有关哪些唱片不干净的信息就像打着打孔机打在您的脸上。它让你痛苦，什么也没有给你，但痛苦。因为它们在某些时候很脏，现在它们不是。还是脏了？谁知道...
upd
更新
Now about data quality. Imagine you read dirty record with query like:
现在关于数据质量。想象一下，您使用以下查询读取脏记录：
SELECT *
FROM dbo.MyTable
WITH (NOLOCK)
and for example got record with id = 1and name = 'someValue'. Than you want to update name, set it to 'anotherValue` - so you do following query:
并且例如使用id = 1和获得记录name = 'someValue'。比您想更新名称，将其设置为“anotherValue” - 这样您就可以执行以下查询：
UPDATE dbo.MyTable
SET
Name = 'anotherValue'
WHERE id = 1
So if this record exists you'l get actual value there, if it was deleted (even on dirty read - deleted and not committed yet) - nothing terrible happened, query won't affect any rows. Is it a problem? Of course not. Becase in time between your read and update things could change zillion times. Just check @@ROWCOUNTto make sure query did what it had to, and warn user about results.
因此，如果此记录存在，您将在那里获得实际值，如果它被删除（即使是脏读 - 已删除但尚未提交） - 没有发生任何可怕的事情，查询不会影响任何行。这是个问题吗？当然不是。因为你的阅读和更新之间的时间可能会改变无数次。只需检查@@ROWCOUNT以确保查询完成了它必须做的事情，并就结果向用户发出警告。
Anyway it depends on situation and importance of data. If data MUSTbe actual - don't use dirty reads
无论如何，这取决于数据的情况和重要性。如果数据必须是真实的 - 不要使用脏读
回答by Remus Rusanu
The standard isolation level negatively influences performance
标准隔离级别对性能有负面影响
So why don't you address that? You know dirty reads are inconsistent reads, so you shouldn't use them. The obvious answer is to use snapshot isolation. Read Implementing Snapshot or Read Committed Snapshot Isolation in SQL Server: A Guide.
那你为什么不解决这个问题？你知道脏读是不一致的读，所以你不应该使用它们。显而易见的答案是使用快照隔离。阅读在 SQL Server 中实现快照或读取提交的快照隔离：指南。
But the problem goes deeper actually. Whydo you encounter blocking? Why are reads blocked by writes? A DW workload should not be let loose on the operational transactional data, this is why we have ETL and OLAP products for. Consider cubes, columnstores, powerpivot, all the goodness that allows for incredibly fastDW and analysis. Don't burden the business operational database with your analytically end-to-end scans, you'll have nothing but problems.
但实际上问题更深了。为什么会遇到阻塞？为什么读取会被写入阻塞？DW 工作负载不应该在操作事务数据上松懈，这就是我们拥有 ETL 和 OLAP 产品的原因。考虑多维数据集、列存储、powerpivot，以及所有能够实现难以置信的快速DW 和分析的优点。不要让您的端到端分析扫描给业务运营数据库带来负担，您只会遇到问题。
