主页 / dba / 问题 / 295462
Accepted
IronicMuffin
Asked: 2021-07-10 06:32:24 +0800 CST

使用 SET @Variable = Field= @Variable + 1 在堆上出现意外更新结果，使用聚集索引修复

  • 772

我只是想了解为什么会发生这种情况，而我的谷歌搜索失败了。我们使用的是 SQL Server 2016 SP1。

情况如下：供应商表通过跟踪每个表的当前值来管理 ID。如果您正在执行插入，则可以调用一个函数来返回一个 ID 块。

所以我们通过使用从真实表中选择来设置一个临时表select into（我们正在克隆一组数据以使用不同的属性集重新插入）。

然后我们调用该函数并获取记录数的新 id（它只返回最大 ID，所以我们做一些数学运算来获取下一个 id）。

然后我们像这样更新表：

update #temp set @nextId = Id = @nextId + 1

期望它将为每条记录增加一并设置ID。

相反，为每 4 条记录设置相同的 ID，然后它会增加，接下来的 4 条获得下一个 id，等等。为什么每 4 条记录？什么地方出了错？

结果

更有趣的是，如果我们在表上放置一个聚集索引，一切都会正常工作。

我确定这与表格堆有关......但不知道为什么。

链接到计划。

sql-server update

2 个回答

  1. Best Answer

    该声明的文档UPDATE说（强调添加）：

    在 UPDATE 语句中可以使用变量名来显示受影响的旧值和新值，但只有在 UPDATE 语句影响单个记录时才应使用该变量名。

    有些人试图通过对其使用施加越来越大的限制来使这种“古怪的更新”技术可靠地用于多行。事实仍然是，这依赖于观察到的效果和未记录的行为，所以你不应该期望它一般工作，或者在未来保持“工作”。

    如果没有重现脚本或执行计划，我无法确切地说出您的情况“出了什么问题”，但最终这并不重要。如果被迫猜测，我会说您的更新在 DOP 4 处运行，并且四个线程同时读取相同的变量值。

    建议您改用可靠的解决方案，例如ROW_NUMBER( docs )、IDENTITY function或sequence。

    • 14

  2. 正如@PaulWhite 在他的回答中提到的，“古怪的更新”方法没有记录并且可能不可靠。它需要仔细编码，并检查执行计划以确保一切正常。

    从您现在提供的执行计划中，您可以非常清楚地看到他是对的：有四个线程：

    <QueryPlan DegreeOfParallelism="4"

    这种方法可以非常繁琐地正常工作。执行计划中的微小变化可能会对其造成影响。

    @JeffModen在经过深入研究的文章中提到的许多未记录的规则中的一些

    • MAXDOP 1提示关闭并行性
    • 聚集索引
    • 必须使用锚柱
    • 没有连接

    这就是为什么许多人反对它的原因，并且鉴于现在有很多方法可以实现相同的目标，它似乎毫无意义。

    在您的特定情况下，IDENTITY列可能是最简单的。但这里的ROW_NUMBER方法也可以确保正确的结果。您可以将其放入 CTE，并直接更新 CTE。

    我注意到您的 ID 没有排序，所以我使用ORDER BY (SELECT 1)了 ，如果您愿意，可以使用不同的排序。

    WITH cte AS (
    SELECT *,
        rn = ROW_NUMBER() OVER (ORDER BY (SELECT 1))
    FROM #temp t
)
UPDATE cte
SET Id = rn;
    • 3

相关问题