主页 / dba / 问题 / 185897
Accepted
JNK
Asked: 2017-09-14 12:49:55 +0800 CST

从表中返回没有重复的重复记录

  • 772

我有一个存储过程，用于查询用于在我们的系统中分配工作的繁忙队列表。有问题的表在 WorkID 上有一个主键并且没有重复项。

查询的简化版本是：

INSERT INTO #TempWorkIDs (WorkID)
SELECT
        W.WorkID

    FROM
        dbo.WorkTable W

    WHERE
        (@bool_param = 0 AND
        ((W.InProgress = 0
         AND ISNULL(W.UserID, -1) != @userid_param
         AND (@bool_filtered = 0
              OR W.TypeID IN (SELECT TypeID FROM #Types AS t)))
         OR 
         (@bool_param = 1
          AND W.InProgress = 1
          AND W.UserID != @userid_param)
        OR
        (@Auto_Param = 0
         AND W.UserID = @userid_param)))
         OR
         (@bool_param = 1 AND W.UserID = @userid_param)
    OPTION
        (RECOMPILE)

该#Types表已在该过程的前面填充。

正如我所说，WorkTable它很忙，有时在运行此查询时我怀疑其中一条记录正在从中的一组过滤器移动到另一组过滤器WHERE。具体来说，当有人开始处理某个项目，并且W.InProgress从 0 更改为 1 时，就会发生这种情况。发生这种情况时，当我尝试将主键添加到此查询插入的临时表中时，我遇到了重复键冲突。

我已经在错误发生时生成的查询计划中确认没有并行度，隔离级别为READ COMMITTED，并且源表中没有重复记录。您还可以看到这里没有JOINs 或其他方法来获取笛卡尔积。

这是匿名查询计划：

在此处输入图像描述

问题是，是什么导致了重复，我怎样才能让它停止？

我认为READ COMMITTED应该在这里工作，我需要锁定。我几乎可以肯定，当InProgress我查询时记录上的位发生变化时，就会发生欺骗。我知道这一点是因为该表存储了该更改的时间，并且它在我查询并收到错误的几毫秒内。

sql-server sql-server-2008-r2

1 个回答

  1. Best Answer

    有一些棘手的场景可能导致同一行从索引中读取两次，即使在隔离级别下也是READ COMMITTED如此。

    您的查询不符合分配顺序扫描的条件，因此存储引擎将按照聚集键的顺序从表中读取数据。

    对于您的表，您将InProgress作为聚集键的第一列。当您扫描整个表时，您很可能会获得行或页锁。如果您在扫描开始附近读取一行，释放对其的锁定，该行将更新，InProgress从 0 变为 1，然后在不同的页面中再次读取该行，那么您可以WorkID从查询中看到重复值.

    有很多解决方法。您可以插入堆中并简单地删除重复值。您可以DISTINCT在查询中添加一个。您还可以启用行版本控制隔离级别，以提供数据库提交状态的稳定视图，无论是在事务开始时（快照隔离），还是在语句开始时（读取提交快照隔离） ).

    也许添加锁定提示或更改表的结构是合适的。对于一个相当有趣的解决方案（可能不适合生产），您可以尝试向后阅读索引。这可以通过一个多余TOP的和一个ORDER BY. 下面是一个非常简单的演示来说明这一点：

    CREATE TABLE #WorkTable (
    InProgress TINYINT NOT NULL,
    WorkID INT NOT NULL
    , PRIMARY KEY (InProgress, WorkID)
);

INSERT INTO #WorkTable WITH (TABLOCK)
SELECT (RN - 1) / 5000, RN
FROM
(
    SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) t
OPTION (MAXDOP 1);

    以下查询具有 Ordered:false 属性，但它仍会按聚集键顺序读取数据：

    SELECT WorkId
FROM #WorkTable;

    但是，以下查询将以反向聚集顺序读取数据：

    SELECT TOP (9223372036854775807) WorkId
FROM #WorkTable
ORDER BY InProgress DESC, WorkId DESC;

    我们可以通过查看扫描属性来了解这一点：

    向后扫描

    对于您的表，这意味着如果一行更新InProgress后从 0 变为 1，则它出现两次的可能性将大大降低。它可能根本不显示，这可能是另一个问题。

    • 9

相关问题