Почему я не могу использовать SELECT... FOR UPDATE с агрегатными функциями?

У меня есть приложение, в котором я нахожу сумму() столбца базы данных для набора записей, а затем использую эту сумму в отдельном запросе, похожем на следующий (составленные таблицы, но идея такая же):

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 - я не понимаю, почему это важно, что мы делаем с этими строками.

EDIT: похоже, что SELECT... FOR UPDATE может использоваться с аналитическими функциями, как это предлагает Дэвид Олдридж ниже. Здесь тест script я использовал для доказательства этого.

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

Ответ 1

Синтаксис select . . . for update блокирует записи в таблице для подготовки к обновлению. Когда вы выполняете агрегацию, набор результатов больше не относится к исходным строкам.

Другими словами, в базе данных нет записей для обновления. Существует только временный набор результатов.

Ответ 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;

Первая строка дает вам общую стоимость, но она выбирает все строки, и, теоретически, они могут быть заблокированы.

Я не знаю, разрешено ли это, заметьте - интересно узнать, есть ли.

Ответ 3

Является ли ваша проблема "Однако теоретически кто-то может обновить столбец затрат в таблице материалов между двумя запросами, и в этом случае рассчитанные проценты будут отключены".??

В этом случае, возможно, вы можете просто использовать внутренний запрос как:

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;

Почему вы хотите заблокировать таблицу? Другие приложения могут выйти из строя, если они попытаются обновить эту таблицу в течение этого времени?