为什么我不能将 SELECT ... FOR UPDATE 与聚合函数一起使用? [英] Why can't I use SELECT ... FOR UPDATE with aggregate functions?

问题描述

我有一个应用程序，我在其中找到一组记录的数据库列的 sum() ，然后在单独的查询中使用该总和，类似于以下内容(组成表，但想法相同):

I have an application where I find a sum() of a database column for a set of records and later use that sum in a separate query, similar to the following (made up tables, but the idea is the same):

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; 

但是，理论上有人可以在两次查询之间更新材料表上的成本列，在这种情况下，计算出的百分比将会关闭.

However, in theory someone could update the cost column on the materials table between the two queries, in which case the calculated percents will be off.

理想情况下，我只会在第一个查询中使用 FOR UPDATE 子句，但是当我尝试这样做时，出现错误:

Ideally, I would just use a FOR UPDATE clause on the first query, but when I try that, I get an error:

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

现在，解决方法不是问题 - 只需在找到 Sum() 之前执行额外的查询以锁定行，但该查询除了锁定表外没有其他用途.虽然这个特定的例子并不耗时，但额外的查询在某些情况下可能会导致性能下降，而且它不是那么干净，所以我想避免这样做.

Now, the work-around isn't the problem - just do an extra query to lock the rows before finding the Sum(), but that query would serve no other purpose than locking the tables. While this particular example is not time consuming, the extra query could cause a performance hit in certain situations, and it's not as clean, so I'd like to avoid having to do that.

有没有人知道不允许这样做的特定原因?在我看来，FOR UPDATE 子句应该只锁定与 WHERE 子句匹配的行 - 我不明白为什么我们对这些行所做的事情很重要.

Does anyone know of a particular reason why this is not allowed? In my head, the FOR UPDATE clause should just lock the rows that match the WHERE clause - I don't see why it matters what we are doing with those rows.

看起来 SELECT ... FOR UPDATE 可以与分析函数一起使用，如下面的 David Aldridge 所建议的.这是我用来证明它有效的测试脚本.

It looks like SELECT ... FOR UPDATE can be used with analytic functions, as suggested by David Aldridge below. Here's the test script I used to prove this works.

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

推荐答案

语法 select ...for update 锁定表中的记录以准备更新.进行聚合时，结果集不再引用原始行.

The syntax select . . . for update locks records in a table to prepare for an update. When you do an aggregation, the result set no longer refers to the original rows.

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

In other words, there are no records in the database to update. There is just a temporary result set.

这篇关于为什么我不能将 SELECT ... FOR UPDATE 与聚合函数一起使用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！