如何仅在SQL Server中选择UNCOMMITTED行？

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系统。标准隔离级别会对性能产生负面影响。我很想使用没有锁定/事务隔离级别读取未提交。我想知道脏读的标识有多少选定的行。

3 个解决方案

#1

Maybe you can do this:

也许你可以这样做：

SELECT * FROM T WITH (SNAPSHOT)
EXCEPT
SELECT * FROM T WITH (READCOMMITTED, READPAST)

But this is inherently racy.

但这本质上是活泼的。

#2

Why do you need to know that?

你为什么要知道这个？

You use TRANSACTION ISOLATION LEVER READ UNCOMMITTED just to indicate that SELECT statement won't wait till any update/insert/delete transactions are finished on table/page/rows - and will grab even dirty records. And you do it to increase performance. Trying to get information about which records 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 UNCOMMITTED来指示SELECT语句不会等到表/页/行上的任何更新/插入/删除事务完成 - 并且甚至会抓取脏记录。而你这样做是为了提高性能。试图获取有关哪些记录变脏的信息就像打孔搅拌器一样。它伤害并且没有给你什么，但痛苦。因为他们在某些时候很脏，现在他们不是。还是还脏？谁知道...

upd

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 = 1 and 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 @@ROWCOUNT to 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 MUST be actual - don't use dirty reads

无论如何，这取决于数据的情况和重要性。如果数据必须是实际的 - 不要使用脏读

#3

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. Why do 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 fast DW 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和分析。不要通过分析端到端扫描给业务运营数据库带来负担，除了问题，你什么也没有。

#1