为什么不能在聚合函数中使用SELECT ... FOR UPDATE语句？

Question

我有一个应用程序，在该应用程序中，我对一组记录的数据库列执行Sum()操作，并在稍后使用该总和进行单独的查询，类似于以下示例（虚构的表，但思想相同）：

SELECT Sum(cost)
INTO v_cost_total
FROM materials
WHERE material_id >=0
AND material_id <= 10; 

[a little bit of interim work]

SELECT material_id, cost/v_cost_total
INTO v_material_id_collection, v_pct_collection
FROM materials
WHERE material_id >=0
AND material_id <= 10
FOR UPDATE; 

然而，从理论上讲，有人可能会在两个查询之间更新材料表的成本列，在这种情况下，计算出的百分比将不准确。

理想情况下，我只想在第一个查询上使用FOR UPDATE子句，但是当我尝试时，我会收到错误提示：

ORA-01786: FOR UPDATE of this query expression is not allowed

现在，解决方法并不是问题——只需在查找Sum()之前执行额外的查询以锁定行即可，但是该查询除了锁定表格外没有任何其他用途。虽然这个特定的例子并不费时，但在某些情况下，额外的查询可能会导致性能损失，并且它不太干净，所以我想避免这样做。

有人知道为什么不允许这样做吗？在我的脑海中，“FOR UPDATE”子句应该只锁定与“WHERE”子句匹配的行——我不明白为什么在处理这些行时要注意什么。

编辑：看起来可以像David Aldridge建议的那样使用SELECT ... FOR UPDATE与分析函数。这是我用来证明这个方法有效的测试脚本。

SET serveroutput ON;

CREATE TABLE materials (
    material_id NUMBER(10,0),
    cost        NUMBER(10,2)
);
ALTER TABLE materials ADD PRIMARY KEY (material_id);
INSERT INTO materials VALUES (1,10);
INSERT INTO materials VALUES (2,30);
INSERT INTO materials VALUES (3,90);

<<LOCAL>>
DECLARE
    l_material_id materials.material_id%TYPE;
    l_cost        materials.cost%TYPE;
    l_total_cost  materials.cost%TYPE;

    CURSOR test IS
        SELECT material_id,
            cost,
            Sum(cost) OVER () total_cost
        FROM   materials
        WHERE  material_id BETWEEN 1 AND 3
        FOR UPDATE OF cost;
BEGIN
    OPEN test;
    FETCH test INTO l_material_id, l_cost, l_total_cost;
    Dbms_Output.put_line(l_material_id||' '||l_cost||' '||l_total_cost);
    FETCH test INTO l_material_id, l_cost, l_total_cost;
    Dbms_Output.put_line(l_material_id||' '||l_cost||' '||l_total_cost);
    FETCH test INTO l_material_id, l_cost, l_total_cost;
    Dbms_Output.put_line(l_material_id||' '||l_cost||' '||l_total_cost);
END LOCAL;
/

这将生成输出：

1 10 130
2 30 130
3 90 130

- BimmerM3

---

能否在使用“select ... for update”时使用分析函数（sum或ratio_to_report）？我目前没有可用的数据库进行测试，所以不确定... - Mike Meyers

Answer 1

select . . . for update 语法会锁定表中的记录以便于更新。当进行聚合操作时，结果集不再引用原始行。

换句话说，数据库中没有要更新的记录，只有一个临时的结果集。

- Gordon Linoff

---

我知道结果集本身是临时的，但结果集的完整性取决于底层数据，因此这似乎是一个有用的功能。我猜Oracle不同意。哦，算了。 - BimmerM3

@BimmerM3 . . . 不行。这是一个比你想象的要难得多的问题。对于 max()，如何指定要锁定的记录呢？是仅仅与 max() 值相同的记录吗？还是整个表，因为可能会添加或更新比最大值更大的新值？ - Gordon Linoff

啊，好观点。我没有考虑到像max()和min()这样的函数。感谢您的回复。 - BimmerM3

@GordonLinoff - 在可串行化隔离级别下的SELECT也存在同样的问题。 - Martin Smith

Answer 2

您可以尝试类似的方法：

您可以尝试类似的方法：

<<LOCAL>>
declare
  material_id materials.material_id%Type;
  cost        materials.cost%Type;
  total_cost  materials.cost%Type;
begin
  select material_id,
         cost,
         sum(cost) over () total_cost
  into   local.material_id,
         local.cost,
         local.total_cost 
  from   materials
  where  material_id between 1 and 3
  for update of cost;

  ...

end local;

第一行提供了总成本，但它选择了所有行，理论上它们可能被锁定。

我不确定这是否被允许，话说，听到是否被允许会很有趣。

- David Aldridge

---

有趣 - 这确实有效！我会把我的测试脚本放在原始帖子中。 - BimmerM3

@BimmerM3……这是一个深刻的解决问题的方法（值得点赞）。然而，它并不是那么“有趣”。结果集是原始行的原始集合。聚合的问题在于结果集不是原始行。 - Gordon Linoff

Answer 3

例如，有一个名为product表格，其中包含id、name和stock，如下所示。

product表格：

id	name	stock
1	Apple	3
2	Orange	5
3	Lemon	8

然后，以下两个查询可以同时运行sum()和SELECT FOR UPDATE：

SELECT sum(stock) FROM (SELECT * FROM product FOR UPDATE) AS result;

WITH result AS (SELECT * FROM product FOR UPDATE) SELECT sum(stock) FROM result;

输出：

 sum
-----
  16
(1 row)

- Kai - Kazuya Ito

---

这些查询会阻塞另一个会话中的表的插入语句吗？或者在当前会话执行UPDATE语句时会引发错误吗？如果不是，可能会导致某种写入偏斜。 - QuestionDriven

这些查询会阻塞另一个会话中的插入语句吗？或者在当前会话中执行更新语句时会引发错误吗？如果不会，可能会导致某种写入偏斜。 - undefined

我不知道我的上面的代码是否会出现写入偏斜。好观点！！ - Kai - Kazuya Ito

Answer 4

为此，您可以使用WITH命令。

例子：

WITH result AS (
  -- your select
) SELECT * FROM result GROUP BY material_id;

- Nando Luz

Answer 5

你的问题是“然而，在理论上，有人可能会在两个查询之间更新材料表上的成本列，这种情况下计算出的百分比将会不准确。”吗？

如果是这样的话，你可以简单地使用内部查询，如下所示：

SELECT material_id, cost/(SELECT Sum(cost)
  FROM materials
  WHERE material_id >=0
  AND material_id <= 10)
INTO v_material_id_collection, v_pct_collection
FROM materials
WHERE material_id >=0
AND material_id <= 10;

为什么要锁定一个表？如果在此期间其他应用程序尝试更新该表，它们可能会失败，对吗？

- A Nice Guy

---

其他应用程序将不得不等待，直到我释放锁定，但如果编程正确，它们不应该失败。 - BimmerM3

那得看其他应用程序是如何编写的。 - A Nice Guy

在我所谈论的实际应用中，我会返回并更新行，因此无论如何，在更新后它们都将被锁定。使用FOR...UPDATE子句锁定可以确保在此期间没有其他人更改数据。在这种情况下，发生冲突的实际可能性非常小，而数据完整性非常重要，因此值得冒使另一个会话失败的风险。 - BimmerM3